How can I change ASP.Net Identity 2 on SQL Server to create a newSequentialId Primary Key?

Question

I have an ASP.NET Identity 2 implementation (no user data yet just base tables) that I have with a userId of type UNIQUEIDENTIFIER.

The application is a code first and I am using EF6.

Here's the DDL:

CREATE TABLE [dbo].[AspNetUsers] (
    [Id]                   UNIQUEIDENTIFIER NOT NULL,
    [FirstName]            NVARCHAR (MAX) NULL,
    [LastName]             NVARCHAR (MAX) NULL,
    [Email]                NVARCHAR (256) NULL,
    [EmailConfirmed]       BIT            NOT NULL,
    [PasswordHash]         NVARCHAR (MAX) NULL,
    [SecurityStamp]        NVARCHAR (MAX) NULL,
    [PhoneNumber]          NVARCHAR (MAX) NULL,
    [PhoneNumberConfirmed] BIT            NOT NULL,
    [TwoFactorEnabled]     BIT            NOT NULL,
    [LockoutEndDateUtc]    DATETIME       NULL,
    [LockoutEnabled]       BIT            NOT NULL,
    [AccessFailedCount]    INT            NOT NULL,
    [UserName]             NVARCHAR (256) NOT NULL,
    [SubjectId]            INT            DEFAULT ((0)) NOT NULL,
    [SubjectIds]           VARCHAR (50)   NULL,
    [OrganizationId]       INT            DEFAULT ((0)) NOT NULL,
    [OrganizationIds]      VARCHAR (50)   NULL,
    [RoleId]               INT            DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
CREATE UNIQUE NONCLUSTERED INDEX [UserNameIndex]
    ON [dbo].[AspNetUsers]([UserName] ASC);

I understand that normal the GUID create is a normal GUID.

Can someone tell me how I can make this create a newSequential GUID?

Please note

I am looking for the correct way to do this specifically with ASP.Net Identity 2. In particular I would like to know if any changes are needed to the Identity 2 UserManager etc.

Answer 1

I was finally able to build the project and run it. A newsequentialid() is assigned to the ID field after creation using Fluent API:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<ApplicationUser>().Property(t => t.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        modelBuilder.Entity<CustomUserRole>().HasKey(x => new
        {
            x.RoleId,
            x.UserId
        });

        modelBuilder.Entity<CustomUserLogin>().HasKey(x => new
        {
            x.UserId,
            x.ProviderKey,
            x.LoginProvider
        });
    }

The result was SQL table that scripted as:

/****** Object:  Table [dbo].[AspNetUsers]    Script Date: 4/11/2015 3:40:51 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AspNetUsers](
    [Id] [uniqueidentifier] NOT NULL,
    [Email] [nvarchar](256) NULL,
    [EmailConfirmed] [bit] NOT NULL,
    [PasswordHash] [nvarchar](max) NULL,
    [SecurityStamp] [nvarchar](max) NULL,
    [PhoneNumber] [nvarchar](max) NULL,
    [PhoneNumberConfirmed] [bit] NOT NULL,
    [TwoFactorEnabled] [bit] NOT NULL,
    [LockoutEndDateUtc] [datetime] NULL,
    [LockoutEnabled] [bit] NOT NULL,
    [AccessFailedCount] [int] NOT NULL,
    [UserName] [nvarchar](256) NOT NULL,
 CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[AspNetUsers] ADD  DEFAULT (newsequentialid()) FOR [Id]
GO

Had to change the other Entity Types:

public class ApplicationUser : IdentityUser<Guid, CustomUserLogin, CustomUserRole,
    CustomUserClaim>
{


    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public override Guid Id { get; set; }

    public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<ApplicationUser, Guid> manager)
    {
        // Note the authenticationType must match the one defined in CookieAuthenticationOptions.AuthenticationType
        var userIdentity = await manager.CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);
        // Add custom user claims here
        return userIdentity;
    }
}

public class CustomUserRole : IdentityUserRole<Guid> { }
public class CustomUserClaim : IdentityUserClaim<Guid> { }
public class CustomUserLogin : IdentityUserLogin<Guid> { }

public class CustomRole : IdentityRole<Guid, CustomUserRole>
{
    public CustomRole() { }
    public CustomRole(string name) { Name = name; }
}

public class CustomUserStore : UserStore<ApplicationUser, CustomRole, Guid,
    CustomUserLogin, CustomUserRole, CustomUserClaim>
{
    public CustomUserStore(ApplicationDbContext context)
        : base(context)
    {
    }
}

public class CustomRoleStore : RoleStore<CustomRole, Guid, CustomUserRole>
{
    public CustomRoleStore(ApplicationDbContext context)
        : base(context)
    {
    }
}

public class ApplicationDbContext : IdentityDbContext<ApplicationUser, CustomRole,
    Guid, CustomUserLogin, CustomUserRole, CustomUserClaim>
{
    public ApplicationDbContext()
        : base("DefaultConnection")
    {
    }

In the Startup.Auth.cs, I changed

        app.UseCookieAuthentication(new CookieAuthenticationOptions
        {
            AuthenticationType = DefaultAuthenticationTypes.ApplicationCookie,
            LoginPath = new PathString("/Account/Login"),
            Provider = new CookieAuthenticationProvider
            {
                // Enables the application to validate the security stamp when the user logs in.
                // This is a security feature which is used when you change a password or add an external login to your account.  
                OnValidateIdentity = SecurityStampValidator
                    .OnValidateIdentity<ApplicationUserManager, ApplicationUser, Guid>(
                        validateInterval: TimeSpan.FromMinutes(30),
                        regenerateIdentityCallback: (manager, user) =>
                            user.GenerateUserIdentityAsync(manager),
                        getUserIdCallback: (id) => new Guid(id.GetUserId()))
            }
        });            
        app.UseExternalSignInCookie(DefaultAuthenticationTypes.ExternalCookie);

In the IdentityConfig.cs, I changed altered the ApplicationUserManager

Here:

public class ApplicationUserManager : UserManager<ApplicationUser, Guid>
{
    public ApplicationUserManager(IUserStore<ApplicationUser, Guid> store)
        : base(store)
    {
    }

    public static ApplicationUserManager Create(IdentityFactoryOptions<ApplicationUserManager> options, IOwinContext context) 
    {
        var manager = new ApplicationUserManager(
            new CustomUserStore(context.Get<ApplicationDbContext>()));
        // Configure validation logic for usernames             manager.UserValidator = new UserValidator<ApplicationUser>(manager)

        manager.UserValidator = new UserValidator<ApplicationUser, Guid>(manager)
        {
            AllowOnlyAlphanumericUserNames = false,
            RequireUniqueEmail = true
        };

And

        manager.RegisterTwoFactorProvider("Phone Code", new PhoneNumberTokenProvider<ApplicationUser, Guid>
        {
            MessageFormat = "Your security code is {0}"
        });
        manager.RegisterTwoFactorProvider("Email Code", new EmailTokenProvider<ApplicationUser, Guid>
        {
            Subject = "Security Code",
            BodyFormat = "Your security code is {0}"
        });
        manager.EmailService = new EmailService();
        manager.SmsService = new SmsService();
        var dataProtectionProvider = options.DataProtectionProvider;
        if (dataProtectionProvider != null)
        {
            manager.UserTokenProvider =
                new DataProtectorTokenProvider<ApplicationUser, Guid>(dataProtectionProvider.Create("ASP.NET Identity"));
        }
        return manager;
    }
}

// Configure the application sign-in manager which is used in this application.
public class ApplicationSignInManager : SignInManager<ApplicationUser, Guid>

In ManageController.cs, I added

public class ManageController : Controller
{
    private ApplicationSignInManager _signInManager;
    private ApplicationUserManager _userManager;
    private Guid userGuidId;

    public ManageController()
    {
        userGuidId= new Guid(User.Identity.GetUserId());
    }

Replacing userGuidId instead everywhere that I saw userId

I had to use a ToString() here:

BrowserRemembered = await AuthenticationManager.TwoFactorBrowserRememberedAsync(userGuidId.ToString())

In Account Controller, I seem to have only changed

    [AllowAnonymous]
    public async Task<ActionResult> ConfirmEmail(string userId, string code)
    {
        Guid GuidUserId = new Guid(userId);
        if (userId == null || code == null)
        {
            return View("Error");
        }
        var result = await UserManager.ConfirmEmailAsync(GuidUserId, code);
        return View(result.Succeeded ? "ConfirmEmail" : "Error");
    }

Answer 2

First create non-generic version of the "IdentityUser" based classes...

public class AppUserClaim : IdentityUserClaim<Guid> { }
public class AppUserLogin : IdentityUserLogin<Guid> { }
public class AppUserRole : IdentityUserRole<Guid> { }

...then the same for IdentityRole and UserStore and `UserManager...

public class AppRole : IdentityRole<Guid, AppUserRole> 
{ 
}

public class AppUserStore : UserStore<AppUser, AppRole, Guid, AppUserLogin, AppUserRole, AppUserClaim>
{
    public AppUserStore(DbContext context)
        : base(context)
    {
    }
}

public class AppUserManager : UserManager<AppUser, Guid>
{
    public AppUserManager(IUserStore<AppUser, Guid> store)
        : base(store)
    {
    }
}

... and finally the IdentityDbContext...

public class AppIdentityContext : IdentityDbContext<AppUser, AppRole, Guid, AppUserLogin, AppUserRole, AppUserClaim>
{
    public AppIdentityContext()
        : base("name=AspNetIdentity")
    {
    }
}

Throughout all these new classes you will notice that the base classes use the generic version of the Identity classes and we are using the AppUserClaim, AppUserLogin, AppUserRole and AppRole in place of the Identity counterparts.

For the user we create a class named AppUser that will derive from IdentityUser:

public class AppUser : IdentityUser<Guid, AppUserLogin, AppUserRole, AppUserClaim>
{
    [DllImport("rpcrt4.dll", SetLastError = true)]
    private static extern int UuidCreateSequential(out Guid guid);

    private Guid _id;

    public AppUser()
    {
        UuidCreateSequential(out _id);
    }        

    /// <summary>
    /// User ID (Primary Key)
    /// </summary>
    public override Guid Id
    {
        get { return _id; }
        set { _id = value; }
    }
}

In the constructor we use the UuidCreateSequential function to create a new ID and return that through the Id property. I wanted to setup the Id column in the database to use newsequentialid() as a default value and use that instead of a DllImport, but I've not worked that out yet.

To use in an controller action:

public async Task<ActionResult> ActionName()
{
    AppIdentityContext dbContext = new AppIdentityContext();
    AppUserStore store = new AppUserStore(dbContext);
    AppUserManager manager = new AppUserManager(store);
    AppUser user = new AppUser { UserName = "<name>", Email = "<email>" };

    await manager.CreateAsync(user);

    return this.View();
}

A few things to note:

1. If you are using an existing database, i.e. one created with an SQL script and where the Id column in AspNetUsers is nvarchar, then you will need to change the following columns to a uniqueidentifier:

   • AspNetUsers.Id
   • AspNetRoles.Id
   • AspNetUserRoles.UserId
   • AspNetUserRoles.RoleId

2. Using the GetUserId extension method on the IIdentity interface within you ASP.NET MVC controllers, i.e. this.User.Identity.GetUserId(), will return a string so you will have to use the following when converting the return value to a string:

   new Guid(this.User.Identity.GetUserId())

   There is a generic version of this method, but underneath it uses Convert.ChangeType and that requires the value being passed in implements IConvertable and Guid does not.

I have not been able to fully test this, but hopefully it will provide a useful base if it doesn't fully meet your needs.

UPDATE #1: These are the steps I went through:

1. Create a new ASP.NET MVC application with No Authentication

2. Add the following NuGet packages

   • EntityFramework
   • Microsoft.AspNet.Identity.Core
   • Microsoft.AspNet.Identity.EntityFramework

3. Add all the code samples to a file named Identity.cs in the App_Start folder

   NOTE: Exclude the controller action sample..this will be done in step #6

4. Remove all the Entity Framework parts from the web.config

5. Add a new connection string to web.config named AspNetIdentity
6. Add the controller action sample to the Index action on the HomeController and replace the <name> and <email> parts
7. Add a new, empty, database named AspNetIdentity to your SQL Server
8. Run the application

If you use the ASP.NET MVC template that has the Individual User Accounts authentication option selected, then there will be a few errors that will have to be fixed. These are mostly centred around changing references to the IdentityUser* classes to the new AppUser* based classes and replace calls to User.Identity.GetUserId() to use the code sample provided in step #2 in my original answer.

Answer 3

This is what worked for me to make Roles and Users Id fields of type Guid have newsequentialid() in Default Value or Binding:

1. Delete *.cs files in Migrations folder in Visual Studio
2. Drop table __MigrationHistory and all AspNet* tables in database

3. Add the following code to ApplicationDbContext class:

   protected override void OnModelCreating(DbModelBuilder modelBuilder)
   {
       base.OnModelCreating(modelBuilder);
       modelBuilder.Entity<ApplicationUser>().Property(t => t.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
       modelBuilder.Entity<ApplicationRole>().Property(t => t.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
   }
   

4. Run Add-Migration Initial in Visual Studio Package Manager Console
5. Run Update-Database in Visual Studio Package Manager Console

Warning: this will delete all users in roles from your database