ASP.NET Core Identity 3.0 : Modifying the Identity Database

Lets get it started! Open up Visual Studio 2015 Community and go to File New Project (CTRL-SHIFT-N). Now I am naming this project CoolWaves.

I also clicked on the Change Authentication button and subsequently selected Individual Accounts option. Then I pressed the OK magic button.

Sanity check! Now compile the project to make sure things are in order. If you cannot compile a project template then stop and fix that issue before continuing.

Now before we change anything let us find the most important files and open all three of them we will be examining them below.

• Program.cs — is where the application get boostrapped.
• Startup.cs — there a lot here but I will focus on what is relevant only for ASP NET Core Identity 3.0
• appsettings.json — variables in the app and for us we are looking for the database connection string.
• The Data folder contains Entity Framework classes that are pivotal to using Asp Net Identity 3.0. The migrations folder contains two classes that will apply instructions that SQL Server will interpret and set up the equivalent database tables with the rules outlined in this file. This is by no means the only way to modify how the database interprets properties in our c# poco classes. Let evaluate what the following snippet from ApplicationDbContextModelSnapshot.cs does.

b.Property<string>("UserName")
                     .HasAnnotation("MaxLength", 256);

What does it do? It sets the Username property to have a length of 256 characters in the database.

• ApplicationDbContext is the way we issue commands to the database and also configure Identity 3.0.

Program.cs

Our program.cs looks like a console application that invokes a class called WebHostBuilder with several extension methods.In particular we are interested in .UseStartup<Startup>() extension method because it points us to the settings where ASP NET Core Identity 3.0 are located.

public class Program
   {
       public static void Main(string[] args)
       {
           var host = new WebHostBuilder()
               .UseKestrel()
               .UseContentRoot(Directory.GetCurrentDirectory())
               .UseIISIntegration()
               .UseStartup<Startup>()
               .Build();
 
           host.Run();
       }

Code Snippets from Startup.cs

Examine the Configure method app.UseIdentity() is what allows us to use ASP Net Core Identity 3.0 within our app.

public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
{
    loggerFactory.AddConsole(Configuration.GetSection("Logging"));
    loggerFactory.AddDebug();
 
    app.UseApplicationInsightsRequestTelemetry();
 
    if (env.IsDevelopment())
    {
        app.UseDeveloperExceptionPage();
        app.UseDatabaseErrorPage();
        app.UseBrowserLink();
    }
    else
    {
        app.UseExceptionHandler("/Home/Error");
    }
 
    app.UseApplicationInsightsExceptionTelemetry();
 
    app.UseStaticFiles();
 
    app.UseIdentity();
 
  ....
}

Also note

• services.AddDbContext which is Entity Framework
• UseSqlServer which sends the connection string that Entity framework will utilize.
• Services.AddIdentity allows the definition of our AspNetUsers (ApplicationUser class) and AspNetRoles (IdentityRole class).

public void ConfigureServices(IServiceCollection services)
       {
           // Add framework services.
           services.AddApplicationInsightsTelemetry(Configuration);
 
           services.AddDbContext<ApplicationDbContext>(options =>
                  options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
 
           services.AddIdentity<ApplicationUser, IdentityRole>()
               .AddEntityFrameworkStores<ApplicationDbContext>()
               .AddDefaultTokenProviders();
 
          ...

Noteworthy is that our appsettings.json file is being invoked in the builder.

public Startup(IHostingEnvironment env)
       {
           var builder = new ConfigurationBuilder()
               .SetBasePath(env.ContentRootPath)
               .AddJsonFile("appsettings.json", optional: true,   
                reloadOnChange: true)
               .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true);
... 

appsettings.json

This is where our application will try to connect to for our database.

"ConnectionStrings": {
  "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=aspnet-CoolWaves-76c450d4-e1ab-446f-9ef3-980363d55be6;Trusted_Connection=True;MultipleActiveResultSets=true"
},

Ok. Lets make some changes.

First lets change the connection string to localhost and name our database CoolWaves. (Quick guide to installing SQL Server 2014 Developer edition is here but I would configure it with mixed mode so that you have an sa account).

"ConnectionStrings": {
  "DefaultConnection": "Server=.;Database=CoolWaves;Trusted_Connection=True;MultipleActiveResultSets=true;"
},

Then I want to deploy the database. So I open the command prompt and go to the same directory my solution is located and use the brand new dotnet cli. Here a link to the documentation: Dot Net CLIo for migrations documentation

I simply have to issue the dotnet ef database update command and it will deploy the database to the connection string located in my appsettings.json.

Later on if we make changes to Entity classes we can use dotnet ef migrations add a new migration by using dotnet ef migrations add version2coolwaves. You will receive a friendly message that if you want to remove this migration you can use dotnet ef migration remove which will revert back to the previous migration.

Lets take a look at the result of issuing donet ef database update command int he dot net cli:

So now you go and show this to the DBA and he tells you we will never use ASP NET Core Identity because nvarchars and GUIDs are evil. That the Id column should be named AspNetUserId and the tables should not be pluralized. So instead of AspNeUsers it should be AspNetUser. In addition, that nothing should be on the dbo schema instead it should be in its own schema called Security.

…

So after reading the whole repo here: https://github.com/aspnet/Identity/tree/dev/src/Microsoft.AspNetCore.Identity.EntityFrameworkCore I found out exactly how Entity Framework Identity 3.0 works. Thank you open source!

Lets tackle the first challenge to change the primary key from a nvarchar to a int (identity key which is an auto generated number that increments).

Go to Startup.cs and change services.AddIdentity

services.AddIdentity<ApplicationUser, IdentityRole<int>>()
        .AddEntityFrameworkStores<ApplicationDbContext, int>()
        .AddDefaultTokenProviders();

Go to ApplicationContext.cs and change the class to the following signature.

public class ApplicationDbContext : IdentityDbContext<ApplicationUser, IdentityRole<int>, int>

Go to ApplicationUser.cs and change the class to the following signature.

public class ApplicationUser : IdentityUser<int>
{
}

The changes are so drastic that the easiest way to go forward is to delete everything in the migrations folder (except the migrations folder of course). Then open to command prompt on the directory of the project and lets add a migration. If a migration is giving you issues drop the database and just deploy again.

dotnet ef migrations add Version2CoolWaves -o Data\Migrations

The -o parameter just outputs the migration in the correct folder instead of root folder. If you do not put -o it will create another migrations folder. Just try it yourself and see what happens when you do not put -o.

which generated :

Follow that up with dotnet database update and let see the results so far.

Not bad… we made the primary key and fk relationships utilize integers but naming among the tables is inconsistent. Delete all your migrations from the migrations folder and drop the database(this is the least path of resistance).

protected override void OnModelCreating(ModelBuilder builder)
   {
       base.OnModelCreating(builder);
 
       // Customize the ASP.NET Identity model and override the defaults if needed.
       // For example, you can rename the ASP.NET Identity table names and more.
       // Add your customizations after calling base.OnModelCreating(builder);
       builder.Entity<ApplicationUser>(entity =>
       {
           entity.ToTable(name:"AspNetUser",schema: "Security");
           entity.Property(e => e.Id).HasColumnName("AspNetUserId");
 
       });
   }

Create a brand new migration and deploy
dotnet migrations add version3CoolWaves
dotnet database database update

At this point I expect you to be thinking that there must be a better way to deploy changes to the database with entity framework. The answer is absolutely, but look at the length of this article already! Here a good opinion article https://www.infoq.com/articles/Database-Change-Deployment-Automation that outlines some of the challenges.

Ok back to our results!

So I got the schema to Security, the table is called AspNetUser and the PK is called AspNetUserId. Now I have to fix AspNetUserRoles, and all the others tables as well.

Here you go. Now that you have the concepts we can just copy and paste because making you type it would not really be learning anything new. *Note when using the migrations I dropped the database several times and started fresh to makes things easier for me, I said this before in the article but is worth mentioning again.

builder.Entity<ApplicationUser>(entity =>
           {
               entity.ToTable(name:"AspNetUser",schema: "Security");
               entity.Property(e => e.Id).HasColumnName("AspNetUserId");
 
           });
 
           builder.Entity<IdentityRole<int>>(entity =>
           {
               entity.ToTable(name: "AspNetRole", schema: "Security");
               entity.Property(e => e.Id).HasColumnName("AspNetRoleId");
 
           });
 
           builder.Entity<IdentityUserClaim<int>>(entity =>
           {
               entity.ToTable("AspNetUserClaim", "Security");
               entity.Property(e => e.UserId).HasColumnName("AspNetUserId");
               entity.Property(e => e.Id).HasColumnName("AspNetUserClaimId");
             
           });
 
           builder.Entity<IdentityUserLogin<int>>(entity =>
           {
               entity.ToTable("AspNetUserLogin", "Security");
               entity.Property(e => e.UserId).HasColumnName("AspNetUserId");
             
           });
 
           builder.Entity<IdentityRoleClaim<int>>(entity =>
           {
               entity.ToTable("AspNetRoleClaim", "Security");
               entity.Property(e => e.Id).HasColumnName("AspNetRoleClaimId");
               entity.Property(e => e.RoleId).HasColumnName("AspNetRoleId");
           });
 
           builder.Entity<IdentityUserRole<int>>(entity =>
           {
               entity.ToTable("AspNetUserRole", "Security");
               entity.Property(e => e.UserId).HasColumnName("AspNetUserId");
               entity.Property(e => e.RoleId).HasColumnName("AspNetRoleId");
 
           });
 
 
           builder.Entity<IdentityUserToken<int>>(entity =>
           {
               entity.ToTable("AspNetUserToken", "Security");
               entity.Property(e => e.UserId).HasColumnName("AspNetUserId");
               
           });

So finally we fulfilled the request from the dba :)

There only one more thing people ask all the time. How do I add a property to AspNetUser or AspNetRole. Note* right now you can’t do the AspNetRoleClaim, AspUserRole and many other tables that would definitly would add great value if they were modifiable. The good news is that there is a commit in Github that will resolve that issue. Until that commit makes it to the main branch , ASP Net Identity 3.0 is less flexible, in this regard, than ASP Net Identity 2.0.

For AspNetUser we just modify the ApplicationUser.cs

public class ApplicationUser : IdentityUser<int>
{
    public string Test { get; set; }
}

To modify AspNetRole we create a new class called ApplicationRole.cs

public class ApplicationRole : IdentityRole<int>
    {
        public ApplicationRole() { }
        public ApplicationRole(string name)
         : this()
        {
            this.Name = name;
        }
        public string ShowingItsPossible { get; set; }
    }

Then find any IdentityRole<Int> in the app and replace it with ApplicationRole. In our case I found three places:

ApplicationDbContext.cs

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

also

builder.Entity<ApplicationRole>(entity =>
           {
               entity.ToTable(name: "AspNetRole", schema: "Security");
               entity.Property(e => e.Id).HasColumnName("AspNetRoleId");
 
           });

StartUp.cs

services.AddIdentity<ApplicationUser, ApplicationRole>()
                .AddEntityFrameworkStores<ApplicationDbContext, int>()
                .AddDefaultTokenProviders();

Apply TWO MIGRATIONS QUIRK ALERT! wait what…!

Dont ask me…please contact this FBI division.

dotnet ef migrations add v1

dotnet ef migrations add v2

Your v2 file will have something like this:

public partial class ver2 : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AddColumn<string>(
                name: "ShowingItsPossible",
                schema: "Security",
                table: "AspNetRole",
                nullable: true);
        }
 
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropColumn(
                name: "ShowingItsPossible",
                schema: "Security",
                table: "AspNetRole");
        }
    }

Good luck! any questions feel free to ask me here or in linkedin: https://www.linkedin.com/in/josé-javier-vélez-colón-2268b116

If this article helped you at all leave a comment so that I know you are alive.