Using ASP.NET Identity Database First Approach

Using Asp.Net Identity DataBase first approach

A possible solution which works for me, basically I am able to integrate Asp.Net Identity User Profiles with an existing Database.

Getting the Asp.Identity Tables:

• Create an MVC Project with Authentication Individual User Account
• Open the DB listed under the DefaultConnection in Web.config. It will be called (aspnet-[timestamp] or something like that.)
• Script the database tables using SQL Server Management Studio (attach database for mdc).

Alternatively use something like http://identity.codeplex.com/

Integrating with your existing db:

• Insert the scripted tables into existing database in SQL Server Management Studio.
• Customize and add relationships to ApplicationUser (if necessary).
• Create new Web Project > MVC > DB First Project > Import DB with EF ... .
• In IdentityModels.cs change the ApplicationDbContext :base("DefaltConnection") to use your project's DbContext.

Now you have the Asp.Identity Tables in your db with ER model in your application.

Asp.Identity Profile Adding new properties:

• Enable Entity Framework Code First Database Migrations, just in VS go under Tools ‘Package Manager Console’,

• Execute the command “Enable-Migrations”; Once we enabled the database migrations, we can go ahead and add new properties for our UserProfile

• To Add new properties modify IdentityModels.cs file, example:

---

public class ApplicationUser : IdentityUser
{
public string FirstName { get; set; }
public string LastName { get; set; }
public string EmailID { get; set; }
}

---

Add New Migration

• Once we added the properties, bring the Package Manager Console and execute the following command.

  Add-Migration “YouMigrationName”

This command will generate a database script file, now execute following command to run this script file against the database.

Update-Database

Now, all the new properties will turn into table fields in the same database table.

I hope it can help others, if you have a better idea please let me know.

Asp.net identity entity framework database first approach with own table defintion

Since you're using Microsoft.AspNet.Identity you should inherit your User from IdentityUser (namespace Microsoft.AspNet.Identity.EntityFramework).

Your classes should be defined like this:

USER

public class User : IdentityUser<int, UserLogin, UserRole, UserClaim>
{
}

ROLE

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

USER-ROLE

public class UserRole : IdentityUserRole<int>
{
}

USER-CLAIM

public class UserClaim : IdentityUserClaim<int>
{
}

USER-LOGIN

public class UserLogin : IdentityUserLogin<int>
{
}

You could extend the classes adding your own custom columns:

public class User : IdentityUser<int, UserLogin, UserRole, UserClaim>
{
    public string CompanyName { get; set; }
}

Now you have to define the stores:

public class UserStore:  UserStore<User, Role, int, UserLogin, UserRole, UserClaim>
{
    public UserStore(MyContext context)
        : base(context)
    {
    }
}

and then your database context, inheriting from IdentityDbContext:

public class MyContext : IdentityDbContext<User, Role, int, UserLogin, UserRole, UserClaim>
{
    public MyContext(): base("ConnectionString")
    {

    }
}

In your database context (MyContext) you must override OnModelCreating so that you can make your columns, change types, tables names etc etc:

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

        modelBuilder.Entity<MyUser>()
            .Property(p => p.Id)
            .HasColumnType("int")
            .IsRequired();

        modelBuilder.Entity<MyRole>()
            .Property(p => p.Id)
            .HasColumnType("int")
            .IsRequired();

        modelBuilder.Entity<MyUserRole>()
            .Property(p => p.RoleId)
            .HasColumnType("int")
            .IsRequired();

        modelBuilder.Entity<MyUserRole>()
            .Property(p => p.UserId)
            .HasColumnType("int")
            .IsRequired();

        modelBuilder.Entity<MyUserClaim>()
            .Property(p => p.Id)
            .HasColumnType("int")
            .IsRequired();

        modelBuilder.Entity<MyUserClaim>()
            .Property(p => p.UserId)
            .HasColumnType("int")
            .IsRequired();

        modelBuilder.Entity<MyUserLogin>()
            .Property(p => p.UserId)
            .HasColumnType("int")
            .IsRequired();

        modelBuilder.Entity<MyUser>()
            .ToTable("Users");

        modelBuilder.Entity<MyRole>()
            .ToTable("Roles");

        modelBuilder.Entity<MyUserRole>()
            .ToTable("UserRoles");

        modelBuilder.Entity<MyUserClaim>()
            .ToTable("UserClaims");

        modelBuilder.Entity<MyUserLogin>()
            .ToTable("UserLogins");

    }

Now you can use migrations to generate your tables.

I've update a github project to reflect your situations.

UPDATE:

If you want to customize names and types of your columns you simply have to give them a name:

modelBuilder.Entity<User>()
    .Property(p => p.Id)
    .HasColumnName("user_id")
    .HasColumnType("SMALLINT")
    .IsRequired();

how to Use Asp.net Identity with DataBase first approach

To start using ASP.NET Identity I advice the following steps:

1. Generate a code model from your existing database
2. On the tables you'd like to use for your Identity implementation inherit from the appropriate classes
3. Create a migration and apply the changes to your database.

To enable identity with the files you generated from your database:

• Inherit the generated context from IdentityDbContext instead of DbContext.
• Inherit youre user model from IdentityUser

Based on your situation there are more models of your own you need to inherit from one of the identity classes or you have to add (using code first migrations). See https://msdn.microsoft.com/en-us/library/dn613255%28v=vs.108%29.aspx for more information about the IdentityDbContext.

Please note that this method requires the needed amount of maintenance when updating your database either with code first migrations or from your database itself. It's not completely impossible but you might have to regenerate your code first model several times OR turn you can turn off the model compatibility check and apply the updates manually to your model. (See: How can I disable model compatibility checking in Entity Framework 4.3?)

---

See https://msdn.microsoft.com/en-us/library/jj200620.aspx for infomation about how to generate a code first model from an existing database.

See https://msdn.microsoft.com/en-us/data/jj591621.aspx for information about enabling migrations on your project.

How can ASP.NET Identity be used with a Model First approach?

Nowadays the recommended approach is to use a separate database for authentication. This approach is enforced in ASP.NET Identity. While you could add the ASP.NET Identity tables to your application database somehow (Migrations, or manually), I would discourage you from doing so as you would only make your life harder as you would no longer be in the intended usage of the framework.

The way to go is to keep the scaffolded ASP.NET Identity DbContext and match the ASP.NET Identity Users to your application Users entities using some field, for example the email address field. The underlying idea is that you would use this same identity database to authenticate users against OAuth (Microsoft Live, Google, Facebook, etc...) thus providing you with a unified way to do authentication.

ASP.NET Identity 2.0 DB First Approach: Adding new Columns

Identity uses Code First approach for making Identity System make customization as more as possible and you are using DB first approach for your common data access. So there are 2 contexts, one is for your data and other is for you Identity. You need to write Identity classes and make a code first migration of Identity context class by typing in the Package Manager Console as:

`Enable-Migrations -ContextNameType [Your_Identity_Context]

This will enable code first migrations just for your Identity context type. If you want to add region property in your user table, then in 'ApplicationUser' (or any class derived from IdentityUser) add the required region property and then apply the migrations to update the user table in the database.

Generating SQL script and applying to the database is not a good approach.

ASP.NET Identity Database First custom user and role

The problem with the code above is that you add a role with an unknown (at least for the system) claim type. For roles the predefined claim-type is http://schemas.microsoft.com/ws/2008/06/identity/claims/role or System.Security.Claims.ClaimTypes.Role. So please change your code to

identity.AddClaim(new Claim("sub", context.UserName));
identity.AddClaim(new Claim(ClaimTypes.Role, "user"));

---

Related Topics