Incorporating MS Identity database into an application database

I’ve just started writing my first serious .NET MVC application. It’s intended to be a slightly cut-down web version of Calibre, supporting only epub format (at least, initially). It has a SQL Server database where everything is stored, and will allow the user to upload epubs, then extract metadata from them, and put it all into database records organised by user. All properly normalised, of course, so that if, for example, two users upload the same epub file it will only store one copy.

To start with I created a dll that could read an epub file. It also has a code first database defined in it that can load the details of a book into the database from the Epub object.

I’ve put the source on github.

Then I created an MVC web app using the VS 2015 templates, and included the Identity system into it for handling user registration and logins. It was pretty easy to get this working, but there was one problem. When I come to put this on a hosting site I’m going to have to pay serious money every month (or year) for each SQL Server database it uses. I didn’t want to have to pay separately for the identity database and the ebook database, so I would have to get them both into the same SQL Server database.

After some googling I found a few partial solutions, that perhaps worked with older versions of Entity Framework, and I eventually managed to expand them and cobble together a solution that actually does work.

Incidentally, I wanted to keep the database context definition entirely within the original dll, because that makes it easy to use with LinqPad, and by using the full context, rather than the one LinqPad creates by default, I can do things like display book covers in query results.

Essentially, what is required is to take out the code that the template creates in Models\IdentityModels.cs and move it into the Ebook context definition. The problem with that is that the ApplicationDbContext the template created derives from IdentityDbContext, which already knows how to create the identity tables. One solution might be to move all the code first definitions, and everything else I’ve defined, for the Ebook db into ApplicationDbContext, but that seems a bit back to front, as I really just want to add a few tables to my database, rather than the other way around.

The first thing I had to do was to add the Identity libraries into the dll’s references. These are all installed from nuget by installing Microsoft.AspNet.Identity.EntityFramework.

Then there was a set of code changes to make in EbooksContext.cs. (Adding using clauses as required, of course).

The remaining steps are:

Copy the definition of ApplicationUser from IdentityModels.cs into EbookContext.cs.

1) Add the DbSet definitions to EbooksContext

public virtual DbSet<IdentityRole> AspNetRoles { get; set; }
public virtual DbSet<ApplicationUser> AspNetUsers { get; set; }
public virtual DbSet<IdentityUserClaim> AspNetUserClaims { get; set; }
public virtual DbSet<IdentityUserLogin> AspNetUserLogins { get; set; }
public virtual DbSet<IdentityUserRole> AspNetUserRoles { get; set; }

2) Add the mappings for the identity tables to EbooksContext.OnModelCreating (note that it already knows some things about the tables and fields, but not quite enough).

modelBuilder.Entity<ApplicationUser>().ToTable("AspNetUser");
modelBuilder.Entity<ApplicationUser>().HasKey(u => u.Id);
modelBuilder.Entity<ApplicationUser>().Property(u => u.PasswordHash)
    .HasMaxLength(500);
modelBuilder.Entity<ApplicationUser>().Property(u => u.SecurityStamp)
    .HasMaxLength(500);
modelBuilder.Entity<ApplicationUser>().Property(u => u.PhoneNumber)
    .HasMaxLength(50);

modelBuilder.Entity<IdentityRole>().ToTable("AspNetRole");
modelBuilder.Entity<IdentityRole>().HasKey(u => u.Id);

modelBuilder.Entity<IdentityUserRole>().ToTable("AspNetUserRole");
modelBuilder.Entity<IdentityUserRole>().HasKey(u => new { u.UserId, u.RoleId });

modelBuilder.Entity<IdentityUserLogin>().ToTable("AspNetUserLogin");
modelBuilder.Entity<IdentityUserLogin>()
    .HasKey(u => new { u.LoginProvider, u.ProviderKey, u.UserId });

modelBuilder.Entity<IdentityUserClaim>().ToTable("AspNetUserClaim");
modelBuilder.Entity<IdentityUserClaim>().HasKey(u => u.Id);
modelBuilder.Entity<IdentityUserClaim>().Property(u => u.ClaimType)
    .HasMaxLength(150);
modelBuilder.Entity<IdentityUserClaim>().Property(u => u.ClaimValue)
    .HasMaxLength(500);

3) Add the static Create method to EbooksContext

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

4) Remove IdentityModels.cs from the Web application.

5) Change all references to ApplicationDbContext to EbooksContext, and add using clauses for EbooksObjects where necessary.

6) Remove DefaultConnection from web.config, and add in the connection info for EbooksContext.

Note that you don’t have to use these names for the Identity tables. You can put whatever you like in the ToTable calls, and call the DbSet properties anything you want (although it’s best to give them pluralised versions of the names of the tables, of course).