ASP.NET Identity with EF Database First MVC5

Question

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<AspNetUser>  //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; }

Rheo

do you have sample code of your implementation? as when i try to replicate the above, i get an error when i try to login or register a user."The entity type AspNetUser is not part of the model for the current context" where AspNetUser is my User entity – Adore

Did you add the AspNetUser table to your EDMX? Also, make sure your AccountController is using MVC5Test_DBEntities (or whatever your DB context is named) rather than ApplicationContext. – Rheo

yes i did for both the above. – Adore

Hi Patrick, i have been struggling with this and was wondering if you found a working solution? – Phaedra

Glad to know I am NOT the only one with this problem. I too have resorted to a similar solution. Less than ideal...but it works. – Electroform

ASP.NET Identity is a steaming pile of ____. Horrible support for database-first, no documentation, poor referential constraints (missing ON CASCADE DELETE on SQL server) and uses strings for IDs (performance issue and index fragmentation). And this is their 297th attempt at identity framework ... – Vanish

@Vanish Identity supports DB-first the same as Code-first. The templates are setup to do code-first so if you start from a template you have to change some things around. Cascade delete works just fine, you can change the strings to ints easily. See my answer below. – Terrieterrier

@Shoe I have to say that I think you may be wrong. I have yet to find a working, comprehensive example/tutorial on how to implement this in db-first (no documentation). The API tries to reference the junction table "IdentityUserRoles" via the property IdentityUser.Roles, which breaks the relationship on EF db-first since junction tables aren't exposed as entities (poor referential constraints-ish). I disagree about the strings for IDs since that can be customized by specifying the type parameters in the inherited classes. To sum it up, looks to me like they didn't have DB first in mind at all. – Gaston

@Lopsided I've been using dbfirst with mvc5 now for 2 years. Sorry it isn't working out for you but it is possible without too much effort – Terrieterrier

Answer 1

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).

Anthony

Thanks. I'll have to give this a try and report if it works out. – Rheo

I tried what you mentioned. See my post for updated info... but the solution wasn't very elegant :/ – Rheo

I've been having just the same problem. It seems that documentation on DB-first is very sparse. This is a nice suggestion but I think you're right, it doesn't quite work – Reactionary

Is there any solution as yet that is not a hack? – Syphon

I am using Onion Architecture and all of POCOs are in Core. There it is not recommended to use IUser. So anyother solution? – Trichoid

Answer 2

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:

<add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\aspnet-SSFInventory-20140521115734.mdf;Initial Catalog=aspnet-SSFInventory-20140521115734;Integrated Security=True" providerName="System.Data.SqlClient" />

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.

Coeval

this solved a problem, I couldn't see the application user in the database, but after replacing the default connection, it worked. – Copious

Answer 3

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

Imitative

The issue isn't DBContext, but that UserManager and RoleManager expect a class that inherits from Microsoft.AspNet.Identity.EntityFramework.IdentityUser – Rheo

public class IdentityDbContext<TUser> : DbContext where TUser : Microsoft.AspNet.Identity.EntityFramework.IdentityUser. When using database first, the generated entity classes do not inherit from any base classes. – Rheo

Then just exclude them from the database when you generate the classes with the entity framework. – Imitative

If you exclude the Identity tables from the EDMX, then you will lose the Navigation properties in other classes that have foreign keys to your UserID – Rheo

Did you figure it out? Why are you reluctant to move to code first? – Imitative

I'm not reluctant to move to code first... It's just that in some scenarios and companies, the db admin creates the base tables, not the coder. – Rheo

@PatrickTran I know the post is old, you can have your dbcontext inherit from identityDbContext<T> and then you have all the access you need to those fields. – Honshu

Answer 4

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.

Terrieterrier

I recently had a chance to implement this, and for some reason ( I believe because of the 3.0 update of the identity ) I was not able to implement the login by inheriting IdentityUser and then overriding the properties; but writing a custom UserStore and inheriting IUser worked fine; Just giving an update, maybe someone finds this useful. – Hedva

Can you give link for all interface implementation or for full project? – Elmira

is there a specific reason why you used a partial class here? – Plaster

If you're using an edmx to generate your models you must use a partial class. If you're doing code first then you may omit this – Terrieterrier

Answer 5

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

Gourd

Answer 6

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);
            }
        }
    }

Deloresdeloria

Answer 7

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.

Cub

I tried everything you mentioned in your blog twice, it didn't work out for me. – Approximate

@Badhon I'm very sorry the instructions in my blog post didn't work out for you. I've had countless people express their thanks as they've found success following my article. Always remember that if Microsoft updates something it can affect the outcome. I wrote the article for ASP.NET MVC 5 with Identity Framework 2.0. Anything beyond that may experience problems but so far I've received very recent comments indicating success. I'd love to hear more about your problem. – Cub

I will try to follow once again, the problem I faced is I could not use my custom database, it used the auto built database. Anyway, I didn't mean to say something wrong with your article. Thanks for sharing your knowledge. – Approximate

@Badhon No worries my friend, I never felt you were saying anything was wrong with the article. We all have unique situations with various edge cases so sometimes what works for others may not necessarily work for us. I'm hope you were able to solve your problem. – Cub

Answer 8

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:

Parcheesi

Answer 9

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)));
}

Phage