ASP.NET Identity with EF Database First MVC5

Is it possible to use the new Asp.net Identity with Database First and EDMX? Or only with code first?

Here's what I did:

1. I made a new MVC5 Project and had the new Identity create the new User and Roles tables in my database.

2. I then opened my Database First EDMX file and dragged in the new Identity Users table since I have other tables that relate to it.

3. Upon saving the EDMX, the Database First POCO generator will auto create a User class. However, UserManager and RoleManager expects a User class inheriting from the new Identity namespace (Microsoft.AspNet.Identity.IUser), so using the POCO User class won't work.

I guess a possible solution is to edit my POCO Generation Classes to have my User class inherit from IUser?

Or is ASP.NET Identity only compatible with Code First Design?

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Update: Following Anders Abel's suggestion below, this is what I did. It work's, but I'm wondering if there is a more elegant solution.

1. I extended my entity User class by creating a partial class within the same namespace as my auto generated entities.

   namespace MVC5.DBFirst.Entity { public partial class AspNetUser : IdentityUser { } }

2. I changed my DataContext to inherit from IdentityDBContext instead of DBContext. Note that every time you update your EDMX and regenerate the DBContext and Entity classes, you'll have to set this back to this.

   public partial class MVC5Test_DBEntities : IdentityDbContext //DbContext

3. Within your auto generated User entity class, you must add the override keyword to the following 4 fields or comment these fields out since they are inherited from IdentityUser (Step 1). Note that every time you update your EDMX and regenerate the DBContext and Entity classes, you'll have to set this back to this.

    override public string Id { get; set; }
    override public string UserName { get; set; }
    override public string PasswordHash { get; set; }
    override public string SecurityStamp { get; set; }
   

It should be possible to use the identity system with POCO and Database First, but you'll have to make a couple of tweaks:

1. Update the .tt-file for POCO generation to make the entity classes partial. That will make it possible for you to supply additional implementation in a separate file.
2. Make a partial implementation of the User class in another file

 

partial User : IUser
{
}

That will make the User class implement the right interface, without touching the actual generated files (editing generated files is always a bad idea).

My steps are very similar but I wanted to share.

1. Create a new MVC5 project

2. Create a new Model.edmx. Even if it's a new database and has no tables.

3. Edit web.config and replace this generated connectionstring:

with this connectionstring:

<add name="DefaultConnection" connectionString="Data Source=.\SQLExpress;database=SSFInventory;integrated security=true;" providerName="System.Data.SqlClient" />

Afterwards, build and run the application. Register a user and then the tables will be created.

EDIT: ASP.NET Identity with EF Database First for MVC5 CodePlex Project Template.

---

I wanted to use an existing database and create relationships with ApplicationUser. This is how I did it using SQL Server but the same idea would probably work with any DB.

1. Create an MVC Project
2. Open the DB listed under the DefaultConnection in Web.config. It will be called (aspnet-[timestamp] or something like that.)
3. Script the database tables.
4. Insert the scripted tables into existing database in SQL Server Management Studio.
5. Customize and add relationships to ApplicationUser (if necessary).
6. Create new Web Project > MVC > DB First Project > Import DB with EF ... Excluding the Identity Classes you inserted.
7. In IdentityModels.cs change the ApplicationDbContext :base("DefaltConnection") to use your project's DbContext.

---

Edit: Asp.Net Identity Class Diagram

IdentityUser is worthless here because it's the code-first object used by the UserStore for authentication. After defining my own User object, I implemented a partial class that implements IUser which is used by the UserManager class. I wanted my Ids to be int instead of string so I just return the UserID's toString(). Similarly I wanted n in Username to be uncapitalized.

public partial class User : IUser
{

    public string Id
    {
        get { return this.UserID.ToString(); }
    }

    public string UserName
    {
        get
        {
            return this.Username;
        }
        set
        {
            this.Username = value;
        }
    }
}

You by no means need IUser. It's only an interface used by the UserManager. So if you want to define a different "IUser" you would have to rewrite this class to use your own implementation.

public class UserManager<TUser> : IDisposable where TUser: IUser

You now write your own UserStore which handles all of the storage of users, claims, roles, etc. Implement the interfaces of everything that the code-first UserStore does and change where TUser : IdentityUser to where TUser : User where "User" is your entity object

public class MyUserStore<TUser> : IUserLoginStore<TUser>, IUserClaimStore<TUser>, IUserRoleStore<TUser>, IUserPasswordStore<TUser>, IUserSecurityStampStore<TUser>, IUserStore<TUser>, IDisposable where TUser : User
{
    private readonly MyAppEntities _context;
    public MyUserStore(MyAppEntities dbContext)
    { 
        _context = dbContext; 
    }
     
    //Interface definitions
}

Here are a couple examples on some of the interface implementations

async Task IUserStore<TUser>.CreateAsync(TUser user)
{
    user.CreatedDate = DateTime.Now;
    _context.Users.Add(user);
    await _context.SaveChangesAsync();
}

async Task IUserStore<TUser>.DeleteAsync(TUser user)
{
    _context.Users.Remove(user);
    await _context.SaveChangesAsync();
}

Using the MVC 5 template, I changed the AccountController to look like this.

public AccountController()
        : this(new UserManager<User>(new MyUserStore<User>(new MyAppEntities())))
{
}

Now logging in should work with your own tables.

Take a look at this project on GitHub: https://github.com/KriaSoft/AspNet.Identity

Which includes:

• SQL Database Project Template for ASP.NET Identity 2.0
• Entity Framework Database-First Provider(s)
• Source Code and Samples

Also see: How to create Database-First provider for ADO.NET Identity

Good question.

I'm more of a database-first person. The code first paradigm seems to loosey-goosey to me, and the "migrations" seem too error prone.

I wanted to customize the aspnet identity schema, and not be bothered with migrations. I'm well versed with Visual Studio database projects (sqlpackage, data-dude) and how it does a pretty good job at upgrading schemas.

My simplistic solution is to:

1. Create a database project that mirrors the aspnet identity schema
2. use the output of this project (.dacpac) as a project resource
3. deploy the .dacpac when needed

For MVC5, modifying the ApplicationDbContext class seems to get this going...

1. Implement IDatabaseInitializer

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>, IDatabaseInitializer<ApplicationDbContext> { ... }

2. In the constructor, signal that this class will implement database initialization:

Database.SetInitializer<ApplicationDbContext>(this);

3. Implement InitializeDatabase:

Here, I chose to use DacFX and deploy my .dacpac

    void IDatabaseInitializer<ApplicationDbContext>.InitializeDatabase(ApplicationDbContext context)
    {
        using (var ms = new MemoryStream(Resources.Binaries.MainSchema))
        {
            using (var package = DacPackage.Load(ms, DacSchemaModelStorageType.Memory))
            {
                DacServices services = new DacServices(Database.Connection.ConnectionString);
                var options = new DacDeployOptions
                {
                    VerifyDeployment = true,
                    BackupDatabaseBeforeChanges = true,
                    BlockOnPossibleDataLoss = false,
                    CreateNewDatabase = false,
                    DropIndexesNotInSource = true,
                    IgnoreComments = true,

                };
                services.Deploy(package, Database.Connection.Database, true, options);
            }
        }
    }

I spent several hours working through this and finally found a solution which I have shared on my blog here. Basically, you need to do everything said in stink's answer but with one additional thing: ensuring Identity Framework has a specific SQL-Client connection string on top of the Entity Framework connection string used for your application entities.

In summary, your application will use a connection string for Identity Framework and another for your application entities. Each connection string is of a different type. Read my blog post for a full tutorial.

I found that @JoshYates1980 does have the simplest answer.

After a series trials and errors I did what Josh suggested and replaced the connectionString with my generated DB connection string. what I was confused about originally was the following post:

How to add ASP.NET MVC5 Identity Authentication to existing database

Where the accepted answer from @Win stated to change the ApplicationDbContext() connection name. This is a little vague if you are using Entity and a Database/Model first approach where the database connection string is generated and added to the Web.config file.

The ApplicationDbContext() connection name is mapped to the default connection in theWeb.config file. Therefore, Josh's method works best, but to make the ApplicationDbContext() more readable I would suggest changing the name to your database name as @Win originally posted, making sure to change the connectionString for the "DefaultConnection" in the Web.config and comment out and/or remov the Entity generated database include.

Code Examples:

We have a Entity Model DLL project where we keep our model class. We also keep a Database Project with all of database scripts. My approach was as follows

1. Create your own project that has the EDMX using database first

2. Script the tables in your db, I used VS2013 connected to localDB (Data Connections) and copied the script to database project, add any custom columns, e.g. BirthDate [DATE] not null

3. Deploy the database

4. Update the Model (EDMX) project Add to the Model project

5. Add any custom columns to the application class

   public class ApplicationUser : IdentityUser { public DateTime BirthDate { get; set; } }

In the MVC project AccountController added the following:

The Identity Provider want a SQL Connection String for it to work, to keep only 1 connection string for the database, extract the provider string from EF connection string

public AccountController()
{
   var connection = ConfigurationManager.ConnectionStrings["Entities"];
   var entityConnectionString = new EntityConnectionStringBuilder(connection.ConnectionString);
        UserManager =
            new UserManager<ApplicationUser>(
                new UserStore<ApplicationUser>(
                    new ApplicationDbContext(entityConnectionString.ProviderConnectionString)));
}