Use SQL Server database instead of local database asp.net mvc 5 database-first asp.net Identity tables

Question

I have created an MVC Web App using Database First. The default db for the ASP.Net Identity data is a local database. I need to change this to my sql server db which already exists. I have looked everywhere. The only articles about this seem to deal with Code First approach. I need DB first approach. I have tried running the PM in the console-Enable Migration. I end up with this error:

"Creating a DbModelBuilder or writing the EDMX from a DbContext created using Database First or Model First is not supported. EDMX can only be obtained from a Code First DbContext created without using an existing DbCompiledModel."

I have also simply changed the "Default Connection" connection string in my web config to point to my Sql Server and still nothing.

Can anyone point me in the right direction on how to create the asp.net identity tables in a sql server database when running my app Instead of a local db?

Solution

By default the connection settings for your database will be determined by a connection string named "DefaultConnection" which you cite in your question.

The name of the appropriate connection string to use can in fact be changed in the constructor of the ApplicationDbContext class, changing from-

public ApplicationDbContext() : base("DefaultConnection", throwIfV1Schema: false)
        {
        }

To-

public ApplicationDbContext() : base("SomeOtherConnection", throwIfV1Schema: false)
        {
        }

(The throwIfV1Schema property is obviously an addition in version two, and will not be present in version one)

However simply changing the "DefaultConnection" connection string details to those of another database (and a user account with appropriate credentials) is normally sufficient in the absence of a pre-existing connection string you wish to share/re-use.

By default ASP.NET Identity will create the tables (if these do not already exist) on the first attempt by the ASP.NET Identity context to interact with the database - by attempting to register or logon for example. If you have simply changed the connection strings and run the application, the chances are it has not attempted to create the tables - you will need to explicitly carry out a user related action.

In your case you want to create the tables yourself.

It is possible to map the ASP.NET Identity classes and properties by overriding the OnModelCreating event. This will look something like-

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

    var user = modelBuilder.Entity<IdentityUser>().HasKey(u => u.Id).ToTable("User", "Users"); //Specify our our own table names instead of the defaults

    user.Property(iu => iu.Id).HasColumnName("Id");
    user.Property(iu => iu.UserName).HasColumnName("UserName");
    user.Property(iu => iu.Email).HasColumnName("EmailAddress").HasMaxLength(254).IsRequired();
    user.Property(iu => iu.IsConfirmed).HasColumnName("EmailConfirmed");
    user.Property(iu => iu.PasswordHash).HasColumnName("PasswordHash");
    user.Property(iu => iu.SecurityStamp).HasColumnName("SecurityStamp");

    user.HasMany(u => u.Roles).WithRequired().HasForeignKey(ur => ur.UserId);
    user.HasMany(u => u.Claims).WithRequired().HasForeignKey(uc => uc.UserId);
    user.HasMany(u => u.Logins).WithRequired().HasForeignKey(ul => ul.UserId);
    user.Property(u => u.UserName).IsRequired();
...

By using "ToTable" for each entity and "HasColumnName" for each property you should be able to map to your own database tables.

What you may wish to do, assuming you don't already have user related database tables, is simply create a new schema (such as "Users") within your database, map the ASP.NET tables to use that (the ToTable method has an overload that accepts schema), and let it create the tables as normal. This way you can be reasonably confident it will not affect your existing tables, stored procedures and database functions (as always however, backup first).

Alternatively you can create the tables yourself using SQL scripts. I found it easiest to create a new empty MVC (it can be Web Forms if preferred), application download the sample NuGet project, update the ASP.NET Identity and Entity Framework packages to the stable versions I was using, and point it to a blank database - then attempt to log on. This gives you all the table and schema information you need to script your own.

I detailed the steps necessary to map the entities to the script created database in my November 2013 question Create ASP.NET Identity tables using SQL script which you may find useful for reference - although you should keep in mind that the schema has changed since then with the release of ASP.NET Identity 2 and 2.1, and properties we had added ourselves in 1.0 are now standard in 2.0 onwards. Other properties we added will not appear at all. The answer will be a reference on method rather than simply a copy and paste solution.

Keep also in mind that, whilst your primary Entity framework context can be allowed to know about the ASP.NET Identity tables - it should not interact with them beyond reading. You will run into problems where both your ASP.NET Identity context and your main Entity framework data context are are updating or inserting to the same tables at the same time, and there will be no change tracking across contexts.

Hope this helps.

Comments

[EB.]

Well, before I dove too deep into the approach you suggested, I took another look at the default connection string. I didn't have the providerName="System.Data.SqlClient" part written correctly. It was a quotation issue. I fixed the connection string properly and lo and behold the tables were created in my Sql Server database. Thanks for the in depth help and suggestions. I hope this post will remain useful to someone. As for me, it was just a stupid syntax error.

[RickAndMSFT]

This is a great answer to how to create DB first Identity tables - reviewed by the Identity team.

[Roberto]

Great response, however I would add that, if the connection string name is the same as the ApplicationDBContext class, it will pick it up automatically without adding : base("SomeOtherConnection",...