How can I change the table names when using ASP.NET Identity?

Question

I am using the release version (RTM, not RC) of Visual Studio 2013 (downloaded from MSDN 2013-10-18) and therefore the latest (RTM) version of AspNet.Identity. When I create a new web project, I select "Individual User Accounts" for authentication. This creates the following tables:

1. AspNetRoles
2. AspNetUserClaims
3. AspNetUserLogins
4. AspNetUserRoles
5. AspNetUsers

When I register a new user (using the default template), these tables (listed above) are created and the AspNetUsers table has a record inserted which contains:

1. Id
2. UserName
3. PasswordHash
4. SecurityStamp
5. Discriminator

Additionally, by adding public properties to the class "ApplicationUser" I have successfully added additional fields to the AspNetUsers table, such as "FirstName", "LastName", "PhoneNumber", etc.

Here's my question. Is there a way to change the names of the above tables (when they are first created) or will they always be named with the AspNet prefix as I listed above? If the table names can be named differently, please explain how.

-- UPDATE --

I implemented @Hao Kung's solution. It does create a new table (for example I called it MyUsers), but it also still creates the AspNetUsers table. The goal is to replace the "AspNetUsers" table with the "MyUsers" table. See code below and database image of tables created.

I would actually like to replace each AspNet table with my own name... For fxample, MyRoles, MyUserClaims, MyUserLogins, MyUserRoles, and MyUsers.

How do I accomplish this and end up with only one set of tables?

public class ApplicationUser : IdentityUser
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Address1 { get; set; }
    public string Address2 { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string PostalCode { get; set; }
    public string PhonePrimary { get; set; }
    public string PhoneSecondary { get; set; }
}

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext(): base("DefaultConnection")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<IdentityUser>().ToTable("MyUsers");
    }
}

-- UPDATE ANSWER --

Thanks to both Hao Kung and Peter Stulinski. This solved my problem...

    protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<IdentityUser>().ToTable("MyUsers").Property(p => p.Id).HasColumnName("UserId");
        modelBuilder.Entity<ApplicationUser>().ToTable("MyUsers").Property(p => p.Id).HasColumnName("UserId");
        modelBuilder.Entity<IdentityUserRole>().ToTable("MyUserRoles");
        modelBuilder.Entity<IdentityUserLogin>().ToTable("MyUserLogins");
        modelBuilder.Entity<IdentityUserClaim>().ToTable("MyUserClaims");
        modelBuilder.Entity<IdentityRole>().ToTable("MyRoles");
    }

Bodega

Are you sure? Please delete all your tables, remove your _migration table and then try. The code i have posted below which is very similar to yours does not create the AspNetUsers table. – Histochemistry

To make sure, I deleted the entire database, even created a new c# web project in Visual Studio. It creates all the AspNet tables and also the MyUsers table (as pictured above). It creates the _MigrationHistory table and there is one record inserted with a MigrationId: "201310292106426_InitialCreate" – Bodega

The only difference between your code and mine is that i renamed ApplicationUser to "User". My behavior is quite different. On first create it creates the tables as needed and with the names i specify.... Maybe just for "experimentation" sake try change ApplicationUser to User and then add the lines base.OnModelCreating(modelBuilder); modelBuilder.Entity<IdentityUser>() .ToTable("Users", "dbo"); modelBuilder.Entity<ApplicationUser>() .ToTable("Users", "dbo"); – Histochemistry

Updated solution above... – Bodega

do you know how to remove the discriminator columns? – Octroi

@Daskul Remove modelBuilder.Entity<IdentityUser>().ToTable("MyUsers").Property(p => p.Id).HasColumnName("UserId"); and in that case discriminator column will not be added to MyUsers table. See this error for more info: #22054668 – Chaunce

see here to save you some googling around. this question + that one made it click – Iverson

@Bodega - You should update your answer to remove the line containing modelBuilder.Entity<IdentityUser>().ToTable("MyUsers").Property(p => p.Id).HasColumnName("UserId"); as mentioned by @Sergey. Otherwise, the newly named MyUsers table has a discriminator column as @Daskul pointed out. Also, your MyUserClaims table structure will be wrong as @Matt Overall pointed out. I think the idea to add that came from a comment to @Giang in a msdn blog, but its wrong! – Corrasion

For .NET 6, check the first part of this answer: https://mcmap.net/q/119408/-how-to-migrate-identity-users-from-a-mvc5-app-to-a-asp-net-core-2-2-app – Rockwell

Answer 1

You can do this easily by modifying the IdentityModel.cs as per the below:

Override OnModelCreating in your DbContext then add the following, this will change AspNetUser table to "Users" you can also change the field names the default Id column will become User_Id.

modelBuilder.Entity<IdentityUser>()
                    .ToTable("Users", "dbo").Property(p => p.Id).HasColumnName("User_Id");

or simply the below if you want to keep all the standard column names:

modelBuilder.Entity<IdentityUser>()
                        .ToTable("Users", "dbo")

Full example below (this should be in your IdentityModel.cs file) i changed my ApplicationUser class to be called User.

public class User : IdentityUser
    {
        public string PasswordOld { get; set; }
        public DateTime DateCreated { get; set; }

        public bool Activated { get; set; }

        public bool UserRole { get; set; }

    }

public class ApplicationDbContext : IdentityDbContext<User>
    {
        public ApplicationDbContext()
            : base("DefaultConnection")
        {
        }

        protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<IdentityUser>()
                .ToTable("Users", "dbo").Property(p => p.Id).HasColumnName("User_Id");
            modelBuilder.Entity<User>()
                .ToTable("Users", "dbo").Property(p => p.Id).HasColumnName("User_Id");
        }
    }

Please note i have not managed to get this working if the current table exists. Also note whatever columns you do not map the default ones will be created.

Histochemistry

To get these changes to come into an effect you need to use migrations to push the changes to the existing database. – Buy

If you do this, your foreign keys will be a little odd, I don't think you are required to change the table name on IdentityUser. See this SO post – Connotative

Just as an extra check as this caught me out previously. Make sure the call to base.OnModelCreating is the first line of code in the override otherwise the rest of the lines are overwritten by the base Identity class. – Neurovascular

@Neurovascular Thank you indeed – Deina

Good solution. But it's not necessary to overwrite the table name for IdentityUser. See this solution #28017184 – Plier

I think this was version 1.0 (initial release) – Histochemistry

Note that if you have a derived class (custom role) from IdentityRole, for example, you must indicate that you use the same table for both entities: base class role, and the derived role.modelBuilder.Entity<IdentityRole>().ToTable("UserRoles"); and modelBuilder.Entity<DerivedRole>().ToTable("UserRoles"); – Eyetooth

Answer 2

Below is my working solution:

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext()
        : base("DefaultConnection", throwIfV1Schema: false)
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder); // This needs to go before the other rules!

        modelBuilder.Entity<ApplicationUser>().ToTable("User");
        modelBuilder.Entity<IdentityRole>().ToTable("Role");
        modelBuilder.Entity<IdentityUserRole>().ToTable("UserRole");
        modelBuilder.Entity<IdentityUserClaim>().ToTable("UserClaim");
        modelBuilder.Entity<IdentityUserLogin>().ToTable("UserLogin");
    }

    public static ApplicationDbContext Create()
    {
        return new ApplicationDbContext();
    }
}

See this for more detail

Disuse

Can you do that by specifying attributes instead of the (ugly) fluent api ? – Mireille

Made a minor edit as somehow I managed to downvote this answer and didn't notice! Also updated it to use the preferred method for links like this [text](link) – Neurovascular

I created an empty MVC project, ran it 1 time, and the asp-named tables were created. Then I added this minor change, deleted all my tables and migration folder (for what its worth) and ran my code again, but the tables refused to be renamed. I then created an entirely new project, made this change before running, and now its working. Am I missing something very obvious?? – Celery

This works in core, too, although the user DTO name is usually IdentityUser. You might also need to specify the type of the Key data type used, which is defaulted to string, e.g. modelBuilder.Entity<IdentityUserClaim<string>>.ToTable(... etc. – Mccarty

Answer 3

You can try overriding this method in your DbContext class to map it to a table of your choosing:

    protected override void OnModelCreating(DbModelBuilder modelBuilder) {
        modelBuilder.Entity<IdentityUser>()
            .ToTable("AspNetUsers");

Estragon

Don't forget to call base.OnModelCreating(modelBuilder); or the rest of the model won't be created. – Prevenient

I updated my question after implementing @Hao Kung's solution but the problem persists, please see my edited question above. Thanks. – Bodega

Answer 4

But it does not work in .NET CORE (MVC 6) for that we need to change binding to

like

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

    builder.Entity<IdentityRole>().ToTable("Role");
    builder.Entity<IdentityUser>(entity => 
    {
        entity.ToTable("User");
        entity.Property(p => p.Id).HasColumnName("UserId");
    });
}

It might help someone :)

Objectify

does this work for both code-first and database-first? – Dextrocular

I've not tried for database first but I guess it should work if model and database are same. – Objectify

Answer 5

Just for documentation purpose, for the one who comes to this post on the years anyears on the future, (like me XD), All answers given up my comment are right, but you can simplyfied with this method given by Alexandru Bucur on his blog

         //But this method is not longer supported on netcore > 2.2, so I need to fix it
         foreach (var entityType in modelBuilder.Model.GetEntityTypes())
         {
            var table = entityType.Relational().TableName;
             if (table.StartsWith("AspNet"))
             {
                 entityType.Relational().TableName = table.Substring(6);
             }
         };

        //This is the functional way on NetCore > 2.2
        foreach (var entityType in modelBuilder.Model.GetEntityTypes())
        {
            var tableName = entityType.GetTableName();
            if (tableName.StartsWith("AspNet"))
            {
                entityType.SetTableName(tableName.Substring(6));
            }
        }

Snowstorm

I think this is a good answer when you would like to change all the table names. I would do a very minor change inside SetTableName method and add a prefix like; entityType.SetTableName(newTableNamePrefix + tableName.Substring(6)); – Shaynashayne

Will be usefull too – Koeppel

Answer 6

You can also create configuration classes and specify every detail of each of your Identity classes, for example:

using System.Data.Entity.ModelConfiguration;

public class ApplicationUserConfig : EntityTypeConfiguration<ApplicationUser>
{
    public UserConfig()
    {
        ToTable("Users");
        Property(u => u.LocationName).IsRequired();
    }
}

And then include these configurations in the OnModelCreating() method:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Configurations.Add(new ApplicationUserConfig());
        ...
    }

This will give you complete control over every aspect of the Identity classes.

Reddish

Answer 7

Also can do it dynamically such as the below:

public class ApplicationDbContext : IdentityDbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options):  base(options)
    {
    }

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

        var entityTypes = builder.Model.GetEntityTypes(); 
        foreach (var entityType in entityTypes)
            builder.Entity(entityType.ClrType)
                   .ToTable(entityType.GetTableName().Replace("AspNet", ""));
    }

}

Komarek

Answer 8

We can change asp.net Identity default table names like this:

    public class ApplicationDbContext : IdentityDbContext
    {    
        public ApplicationDbContext(): base("DefaultConnection")
        {
        }

        protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<IdentityUser>().ToTable("user");
            modelBuilder.Entity<ApplicationUser>().ToTable("user");

            modelBuilder.Entity<IdentityRole>().ToTable("role");
            modelBuilder.Entity<IdentityUserRole>().ToTable("userrole");
            modelBuilder.Entity<IdentityUserClaim>().ToTable("userclaim");
            modelBuilder.Entity<IdentityUserLogin>().ToTable("userlogin");
        }
    }

Furthermore we can extend each class and add any property to classes like 'IdentityUser', 'IdentityRole', ...

    public class ApplicationRole : IdentityRole<string, ApplicationUserRole>
{
    public ApplicationRole() 
    {
        this.Id = Guid.NewGuid().ToString();
    }

    public ApplicationRole(string name)
        : this()
    {
        this.Name = name;
    }

    // Add any custom Role properties/code here
}


// Must be expressed in terms of our custom types:
public class ApplicationDbContext 
    : IdentityDbContext<ApplicationUser, ApplicationRole, 
    string, ApplicationUserLogin, ApplicationUserRole, ApplicationUserClaim>
{
    public ApplicationDbContext()
        : base("DefaultConnection")
    {
    }

    static ApplicationDbContext()
    {
        Database.SetInitializer<ApplicationDbContext>(new ApplicationDbInitializer());
    }

    public static ApplicationDbContext Create()
    {
        return new ApplicationDbContext();
    }

    // Add additional items here as needed
}

To save time we can use AspNet Identity 2.0 Extensible Project Template to extend all the classes.

Dextrose

Answer 9

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<AppUser>().ToTable("Users");
            modelBuilder.Entity<IdentityUser>().ToTable("Users");
            modelBuilder.Entity<IdentityRole>().ToTable("Role");
            modelBuilder.Entity<IdentityUserRole<string>>().ToTable("UserRole");
            modelBuilder.Entity<IdentityUserClaim<string>>().ToTable("UserClaim");
            modelBuilder.Entity<IdentityUserLogin<string>>().ToTable("UserLogin");
        }

Fictionalize

Answer 10

For .net 6 you need to use the Duende.IdentityServer.EntityFramework.Entities

        protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<IdentityUser>().ToTable("SecurityUsers");  
        modelBuilder.Entity<IdentityRole>().ToTable("SecurityRole");
        modelBuilder.Entity<IdentityUserRole<string>>().ToTable("SecurityUserRole");
        modelBuilder.Entity<IdentityUserClaim<string>>().ToTable("SecurityUserClaim");
        modelBuilder.Entity<IdentityUserLogin<string>>().ToTable("SecurityUserLogin");
        modelBuilder.Entity<IdentityRoleClaim<string>>().ToTable("SecurityRoleClaims");
        modelBuilder.Entity<IdentityUserToken<string>>().ToTable("SecurityUserTokens");

        modelBuilder.Entity<Duende.IdentityServer.EntityFramework.Entities.PersistedGrant>().ToTable("SecurityPersistedGrant");
        modelBuilder.Entity<Duende.IdentityServer.EntityFramework.Entities.Key>().ToTable("SecurityKey");
        modelBuilder.Entity<Duende.IdentityServer.EntityFramework.Entities.DeviceFlowCodes>().ToTable("SecurityDeviceCode");

    }

Scoot