Identity: How would I change the primary keys in the identity tables to int instead of string?

[rowanmiller]

Porting a question from the EF repo (https://github.com/aspnet/EntityFramework/issues/4879) asked by @TDK1964...

How would I change the primary keys in the identity tables to int instead of string?

I basically want to change all the string Guids PK's to int so this would include IdentityUserRole,IdentityUserLogin,IdentityUserClaim,IdentityRole,ApplicationUser (or should that be IdentityUser?)

I also want to change the table names to

builder.Entity<ApplicationUser>().ForSqlServerToTable().Table("Users");
builder.Entity<IdentityUserRole<string>>().ForSqlServerToTable("UserRoles");
builder.Entity<IdentityUserLogin<string>>().ForSqlServerToTable("UserLogins");
builder.Entity<IdentityUserClaim<string>>().ForSqlServerToTable("UserClaims");
builder.Entity<IdentityRole>().ForSqlServerToTable("Roles");

I have tried .ToTable("...") but this doesn't work either.

[gdoron]

It's doable but requires too much hassle that just decided to go with the default string ID.
(You can not use IdentityDbContext, all the templates need to be changed)

This is a thing I really wish to be fixed soon too.

[TDK1964]

Any thoughts?

[ghost]

Instructions for this would be quite important for us since we are using a large pre-existing database that uses INT as the primary user key.

[gdoron]

@rowanmiller who should we ping to get a response?
It's sad that EF and asp.net core that give so much flexibility enforces such a thing that I believe most of the users wouldn't want.
How many .Net applications are there that int isn't their pk type for users?
You need an enormous amount of users to actually need to use GUID as the PK.

[divega]

@HaoK can you please respond to this question with details?

[HaoK]

Can you try adding classes for each like this:

    public class IntUser : IdentityUser<int>
    public class IntRole : IdentityRole<int>
    public class TestDbContext : IdentityDbContext<IntUser, IntRole, int>

[gdoron]

@HaoK It's not just the DbContext, it's Identity helper classes (e.g. UserManager) as well (and obviously the asp.net templates).
Isn't it?

[HaoK]

There should be no change in UserManager nor in the templates, Identity itself always treats the key as a string. Its only the stores that store the keys whatever type you want (and to do the TKey => string translation)

[TDK1964]

@HaoK Your solution worked for me. These are the changes I made and includes renaming the tables.

public class ApplicationUser : IdentityUser<int>
{
...
}
public class ApplicationRole : IdentityRole<int>
{
...
}
public class ApplicationDbContext : IdentityDbContext<ApplicationUser, ApplicationRole, int>
{
...
protected override void OnModelCreating(ModelBuilder builder)
{
    builder.Entity<ApplicationUser>(i => {
        i.ToTable("Users");
        i.HasKey(x => x.Id);
    });
    builder.Entity<ApplicationRole>(i => {
        i.ToTable("Role");
        i.HasKey(x => x.Id);
    });
    builder.Entity<IdentityUserRole<int>>(i => {
        i.ToTable("UserRole");
        i.HasKey(x => new { x.RoleId, x.UserId });
    });
    builder.Entity<IdentityUserLogin<int>>(i => {
        i.ToTable("UserLogin");
        i.HasKey(x => new { x.ProviderKey, x.LoginProvider });
    });
    builder.Entity<IdentityRoleClaim<int>>(i => {
        i.ToTable("RoleClaims");
        i.HasKey(x => x.Id);
    });
    builder.Entity<IdentityUserClaim<int>>(i => {
        i.ToTable("UserClaims");
        i.HasKey(x => x.Id);
    });
    }
}

[SteveSandersonMS]

I'm doing some verification for the ASP.NET Identity features and have not been successful in getting the Identity DB to change the user ID type from guid to int. If this is going to be a common scenario, we may need better docs.

I tried doing what's described above by @TDK1964, but it ends up producing the error:

System.TypeLoadException
GenericArguments[0], 'WebApplication1.Data.ApplicationUser', on 'Microsoft.AspNetCore.Identity.EntityFrameworkCore.UserStore`8[TUser,TRole,TContext,TKey,TUserClaim,TUserRole,TUserLogin,TUserToken]' violates the constraint of type parameter 'TUser'.

at System.RuntimeTypeHandle.Instantiate(RuntimeTypeHandle handle, IntPtr* pInst, int numGenericArgs, ObjectHandleOnStack type)

at System.RuntimeTypeHandle.Instantiate(Type[] inst)

at System.RuntimeType.MakeGenericType(Type[] instantiation)

I don't know why this type error occurs at runtime, given that there was no build error. I'm using the 21343 (6/11) build.

So, is this a scenario we need to support, and if so, can you provide instructions for doing it? Have I missed where this is documented, or should I file an issue for it to be documented? Thanks!

[blowdart]

@HaoK @divega

[gdoron]

I'm on my mobile phone, but you should add to the startup class some code that says the PK is an int.
I'll upload it soon.
It works fine for me (using RC 2)

[SteveSandersonMS]

I did also try changing the AddIdentity call in Startup.cs to specify the custom generic types:

services.AddIdentity<ApplicationUser, ApplicationRole>(...

... but that didn't change the outcome. Is there some other code I need to add?

[gdoron]

@SteveSandersonMS

        services.AddIdentity<ApplicationUser, Role>()
            .AddEntityFrameworkStores<ApplicationDbContext, int>()

This does the trick for me.

[HaoK]

Yeah that is an easy step to miss. We should make sure the docs are updated to make it clear.

[SteveSandersonMS]

Thanks! Yes, that works :)

[gdoron]

@HaoK, BTW, why is it even needed?
Why isn't inheriting from IdentityDbContext with the generic parameters enough?
It looks redundant.

[HaoK]

Because .AddEntityFrameworkStores<ApplicationDbContext>() is really .AddEntityFrameworkStores<ApplicationDbContext, string>()

At the end of the day, the primary key type is now a store specific thing, core identity always uses string for the keys.

[gdoron]

@HaoK I meant to say, that since you pass in ApplicationDbContext, you can infer the PK type from the IdentityDbContext generic parameter.

[HaoK]

Our constraint is that its a DbContext not necessarily one derived from IdentityDbContext

[gdoron]

@HaoK Since most do, can't you check is IdentityDbContext?
That sounds like a lot better UX even if not the best OO.

[HaoK]

Sure filed a issue to consider in the future

[gdoron]

@HaoK Out of curiosity, in which timezone are you...? :+1:

[gdoron]

Changing the user PK to int caused me problems with external login providers (OAuth).
Be aware.

885

[raffaeu]

Hi guys, using EF 1.1.0 and Identity Server 4 I got this working using the following steps:

Change the ApplicationDbContext or whatever you used to reflect this one:

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);

            // Customizations
            builder.Entity<ApplicationUser>(i =>
            {
                i.ToTable("Users");
                i.HasKey(x => x.Id);
            });
            builder.Entity<ApplicationRole>(i =>
            {
                i.ToTable("Roles");
                i.HasKey(x => x.Id);
            });
            builder.Entity<IdentityUserRole<Guid>>(i =>
            {
                i.ToTable("UserRoles");
                i.HasKey(x => new { x.RoleId, x.UserId });
            });
            builder.Entity<IdentityUserLogin<Guid>>(i =>
            {
                i.ToTable("UserLogins");
                i.HasKey(x => new { x.ProviderKey, x.LoginProvider });
            });
            builder.Entity<IdentityRoleClaim<Guid>>(i =>
            {
                i.ToTable("RoleClaims");
                i.HasKey(x => x.Id);
            });
            builder.Entity<IdentityUserClaim<Guid>>(i =>
            {
                i.ToTable("UserClaims");
                i.HasKey(x => x.Id);
            });
            builder.Entity<IdentityUserToken<Guid>>(i =>
            {
                i.ToTable("UserTokens");
                i.HasKey(x => x.UserId);
            });
        }

Create a Custom ApplicationUser class

    public class ApplicationUser : IdentityUser<Guid>
    {
    }

Create a Custom ApplicationRole class

    public class ApplicationRole : IdentityRole<Guid>
    {
    }

Change your ASP.NET Core Startup according:

// entity framework for asp.net identity
services
    .AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(Configuration.GetConnectionString("xxx")));

// identity models
services
    .AddIdentity<ApplicationUser, ApplicationRole>()
    .AddEntityFrameworkStores<ApplicationDbContext, Guid>()
    .AddDefaultTokenProviders();

Drop the existing DB because the schema can't be migrated from PK String to PK Guid
dotnet ef database drop -c ApplicationDbContext

Create the initial migration
dotnet ef migrations add InitialCreate -c ApplicationDbContext

Create the database
dotnet ef database update

[Kurtbaby]

raffaeu, don't you also have to change the ApplicationDbContext class signature to something like:

public class ApplicationDbContext : IdentityDbContext<ApplicationUser, ApplicationRole, Guid>

[raffaeu]

Yes, I forgot to mention @Kurtbaby
You also have to change the inheritance signature of ApplicationContext in this way:

    public class ApplicationDbContext : IdentityDbContext<ApplicationUser, ApplicationRole, Guid>
    {