How to set up the Entity Framework model for Identity Framework to work against an existing database?

Question

I am migrating my old website from PHP to C# MVC. I want to use Microsoft's Identity set-up as it looks rather neat.

I already have my solution set up using database-first entity framework. I have the required tables (Users, UserRoles, UserLogins, UserClaims) with all of the foreign keys set up.

I've looked at a few ways of setting up the IdentityUser, ones that have used MySqlDatabase and code first, but I'm not sure how to implement my IdentityUser when I already have an established database, including an existing Users table.

I want my IdentityUser to manipulate my Users using the Entity Framework classes that I've already created. Is there a way of making my User model in EF to derive from IdentityUser and match my existing database?

One thing specifically that I am struggling with is that my database doesn't use a string value as the primary key, it uses an auto-incrementing int.

At the moment I have the following class:

// Copyright (c) KriaSoft, LLC.  All rights reserved.  See LICENSE.txt in the project root for license information.

namespace KriaSoft.AspNet.Identity.EntityFramework
{
    using Microsoft.AspNet.Identity;
    using System;

    public partial class IdentityUser : IUser<int>
    {
        /// <summary>
        /// Default constructor 
        /// </summary>
        public IdentityUser()
        {
            Id = Guid.NewGuid().ToString();
        }

        /// <summary>
        /// Constructor that takes user name as argument
        /// </summary>
        /// <param name="userName"></param>
        public IdentityUser(string userName)
            : this()
        {
            UserName = userName;
        }

        /// <summary>
        /// User ID
        /// </summary>
        public string Id { get; set; }

        /// <summary>
        /// User's name
        /// </summary>
        public string UserName { get; set; }

        /// <summary>
        ///     Email
        /// </summary>
        public virtual string Email { get; set; }

        /// <summary>
        ///     True if the email is confirmed, default is false
        /// </summary>
        public virtual bool EmailConfirmed { get; set; }

        /// <summary>
        ///     The salted/hashed form of the user password
        /// </summary>
        public virtual string PasswordHash { get; set; }

        /// <summary>
        ///     A random value that should change whenever a users credentials have changed (password changed, login removed)
        /// </summary>
        public virtual string SecurityStamp { get; set; }

        /// <summary>
        ///     PhoneNumber for the user
        /// </summary>
        public virtual string PhoneNumber { get; set; }

        /// <summary>
        ///     True if the phone number is confirmed, default is false
        /// </summary>
        public virtual bool PhoneNumberConfirmed { get; set; }

        /// <summary>
        ///     Is two factor enabled for the user
        /// </summary>
        public virtual bool TwoFactorEnabled { get; set; }

        /// <summary>
        ///     DateTime in UTC when lockout ends, any time in the past is considered not locked out.
        /// </summary>
        public virtual DateTime? LockoutEndDateUtc { get; set; }

        /// <summary>
        ///     Is lockout enabled for this user
        /// </summary>
        public virtual bool LockoutEnabled { get; set; }

        /// <summary>
        ///     Used to record failures for the purposes of lockout
        /// </summary>
        public virtual int AccessFailedCount { get; set; }
    }
}

Of course, my Users entity within my Entity Framework database context already has all of these properties, so it seems a bit pointless having them specified in there as well as my context... I'm really not sure :(.

I guess my question is: How do use my existing entity framework User model (database first) as my IdentityUser for Asp.net Identity?

Answer 1

I am now a lot more familiar with this.

The most straightforward way to get this to work either code-first or database-first, is to change your existing database so that it has at least the minimum database schema (tables, columns and foreign keys) that is used by ASP.NET Identity Framework.

You can see the minimum schema in the image below:

Although it doesn't have column types, it's still useful to see. You can get the precise schema from the SQL Database Project template listed on this page.

I'm sure it's possible to avoid having to make your existing database adhere to this schema by creating some kind of mappings either within your code (code-first) or using the EF tools (database-first) to map from the column name to another name within your code... but I haven't tried it.

I created most of the tables from scratch, other than the User table, where I changed my original Username column to the name UserName (case correction) to match and added the additional columns that didn't already exist.

Code First

Once you have the database in place and you know that the schema is correct, then you may want to consider using the reverse-engineer code first feature of Visual Studio to scaffold your EF classes for you. This way your new EF classes will match your table layouts precisely. Otherwise, you'll have to code all of your models yourself with all of the mappings.

Once you have the EF classes in place, you should make them inherit from different classes from Identity Framework. As you're doing this as code first, you can add the inheritance to the EF classes without any fear of them being overwritten (unlike with database first).

public class User : IdentityUser<int, UserLogin, UserRole, UserClaim>
{
    // Any additional columns from your existing users table will appear here.
}

public class Role : IdentityRole<int, UserRole>
{
}

public class UserClaim : IdentityUserClaim<int>
{
}

public class UserLogin : IdentityUserLogin<int>
{
}

public class UserRole : IdentityUserRole<int>
{
}

Notice the int specified in each, this specifies the primary key type of the User table. This is, by default, a string, but my Id value in my existing database is an int that auto-increments.

Database First

When you're using EF database-first, you don't have the luxury of adding the inheritance of the Identity Framework classes directly to the automatically generated classes. This is because they are overwritten every time you make a change to the model using the Entity Framework Visual Studio tools.

However, the classes that are created are auto generated are all partial classes, so it can be achieved by creating a new file with the definition of the partial classes that won't get overwritten. They must be in the same namespace and exactly the same name.

So for example, this might be the class generated by EF:

namespace Dal
{
    public partial class User
    {
        // This class is auto generated by using EF database-first
        // We can't edit this class to add the inheritance,
        // because it will be overwritten every time
        // we update our model using the database-first tools
    }
}

And this is the model that we can create to add our inheritance to:

// same namespace as existing class
namespace Dal
{
    // Same name as existing class
    public partial class User : IdentityUser<int, UserLogin, UserRole, UserClaim>
    {
        // This can probably be left blank
    }
}

So you would do this for each of the classes required by ASP.NET Identity Framework:

public partial class User : IdentityUser<int, UserLogin, UserRole, UserClaim>
{
    // Any additional columns from your existing users table will appear here.
}

public partial class Role : IdentityRole<int, UserRole>
{
}

public partial class UserClaim : IdentityUserClaim<int>
{
}

public partial class UserLogin : IdentityUserLogin<int>
{
}

public partial class UserRole : IdentityUserRole<int>
{
}

Answer 2

The scripts if somebody need:

CREATE TABLE [User](
    UserID               INT IDENTITY(1,1) NOT NULL,
    UserName             NVARCHAR (100)           NOT NULL,
    Email                NVARCHAR (100)          NULL,
    EmailConfirmed       bit           NOT NULL,
    PasswordHash         NVARCHAR (100)         NULL,
    SecurityStamp        NVARCHAR (100)         NULL,
    PhoneNumber          NVARCHAR (100)          NULL,
    PhoneNumberConfirmed bit           NOT NULL,
    TwoFactorEnabled     bit           NOT NULL,
    LockoutEndDateUtc    DATETIME               NULL,
    LockoutEnabled       bit           NOT NULL,
    AccessFailedCount    INT                    NOT NULL,

    CONSTRAINT PK_User_UserID PRIMARY KEY CLUSTERED (UserID ASC),
    CONSTRAINT UK_User_UserName UNIQUE NONCLUSTERED (UserName ASC)
);

---
CREATE TABLE UserClaim
(
    UserID     INT                   NOT NULL,
    ClaimID    INT IDENTITY (1,1) NOT NULL,
    ClaimType  NVARCHAR (MAX)        NULL,
    ClaimValue NVARCHAR (MAX)        NULL,

    CONSTRAINT PK_UserClaim_ClaimID PRIMARY KEY CLUSTERED (ClaimID ASC),
    CONSTRAINT FK_UserClaim_User FOREIGN KEY (UserID) REFERENCES [User] (UserID) ON DELETE CASCADE
);

GO
CREATE NONCLUSTERED INDEX IX_UserClaim_UserID
    ON UserClaim (UserID ASC);

---
CREATE TABLE UserLogin
(
    UserID        INT            NOT NULL,
    LoginProvider NVARCHAR (128) NOT NULL,
    ProviderKey   NVARCHAR (128) NOT NULL,

    CONSTRAINT PK_UserLogin_UserID_LoginProvider_ProviderKey PRIMARY KEY CLUSTERED (UserID ASC, LoginProvider ASC, ProviderKey ASC),
    CONSTRAINT FK_UserLogin_User FOREIGN KEY (UserID) REFERENCES [User] (UserID) ON DELETE CASCADE
);

GO
CREATE NONCLUSTERED INDEX IX_UserLogin_UserID
    ON UserLogin (UserID ASC);  

---
CREATE TABLE UserRole
(
    RoleID INT          NOT NULL,
    [Name]   NVARCHAR (100) NOT NULL,

    CONSTRAINT PK_UserRole_RoleID PRIMARY KEY CLUSTERED (RoleID ASC),
    CONSTRAINT UK_UserRole_Name UNIQUE NONCLUSTERED (Name ASC)
);  


---
CREATE TABLE UserUserRole
(
    UserID INT NOT NULL,
    RoleID INT NOT NULL,

    CONSTRAINT PK_UserUserRole_UserID_RoleID PRIMARY KEY CLUSTERED (UserID ASC, RoleID ASC),
    CONSTRAINT FK_UserUserRole_User FOREIGN KEY (UserID) REFERENCES [User] (UserID) ON DELETE CASCADE,
    CONSTRAINT FK_UserUserRole_UserRole FOREIGN KEY (RoleID) REFERENCES UserRole (RoleID) ON DELETE CASCADE
);

GO
CREATE NONCLUSTERED INDEX IX_UserUserRole_UserID
    ON UserUserRole (UserID ASC);

GO
CREATE NONCLUSTERED INDEX IX_UserUserRole_RoleID
    ON UserUserRole (RoleID ASC);