How to add a Foreign key in Customer table (CreatedBy column) for AspNetUser table (Id column) in ASP.NET MVC 5 Identity 2.0 using Code First

Question

I have created Empty MVC (ASP.NET Web Application) project using Visual Studio 2013 Update 2 RC & then added AspNet Identity Samples using:

PM>Install-Package Microsoft.AspNet.Identity.Samples -Pre

I have enabled and added migrations & then updated database, which created default tables.

I want to create Customer table which contains 2 columns as Foreign Keys to:

• Groups table (GroupId column)
• AspNetUsers table (Id column)

So I have created 2 classes Customer & Group and added Foreign Keys using Data-annotations as shown below:

namespace IdentitySample.Models
{
    // You can add profile data for the user by adding more properties to your ApplicationUser class, please visit http://go.microsoft.com/fwlink/?LinkID=317594 to learn more.
    public class ApplicationUser : IdentityUser
    {
        public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<ApplicationUser> manager)
        {
            // Note the authenticationType must match the one defined in CookieAuthenticationOptions.AuthenticationType
            var userIdentity = await manager.CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);
            // Add custom user claims here
            return userIdentity;
        }
    }

    public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
    {
        public ApplicationDbContext()
            : base("DefaultConnection", throwIfV1Schema: false)
        {
        }

        static ApplicationDbContext()
        {
            // Set the database intializer which is run once during application start
            // This seeds the database with admin user credentials and admin role
            Database.SetInitializer<ApplicationDbContext>(new ApplicationDbInitializer());
        }

        public static ApplicationDbContext Create()
        {
            return new ApplicationDbContext();
        }
        public DbSet<Customer> Customers { get; set; }
        public DbSet<Group> Groups { get; set; }
    }

    public class Customer
    {
        public int CustomerId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int Age { get; set; }
        public int GroupId { get; set; }
        public string CreatedBy { get; set; }



        [ForeignKey("GroupId")]
        public Group Groups { get; set; }

        [ForeignKey("CreatedBy")]
        public ApplicationUser ApplicationUsers { get; set; }
    }

    public class Group
    {
        public int GroupId { get; set; }
        public string GroupName { get; set; }
    }
}

Everything seems fine, the ApplicationDbContext.edmx diagram created using EF Power tools also looks fine & project builds properly.

I have then added CustomersController using "MVC 5 Controller with views, using Entity Framework" scaffolding template.

Now I am getting compilation error (at db.ApplicationUsers)

// GET: Customers/Create
public ActionResult Create()
{
    ViewBag.CreatedBy = new SelectList(db.ApplicationUsers, "Id", "Email");
    ViewBag.GroupId = new SelectList(db.Groups, "GroupId", "GroupName");
    return View();
}

Error details: 'IdentitySample.Models.ApplicationDbContext' does not contain a definition for 'ApplicationUsers' and no extension method 'ApplicationUsers' accepting a first argument of type 'IdentitySample.Models.ApplicationDbContext' could be found (are you missing a using directive or an assembly reference?)

When I add below code in ApplicationDbContext

public DbSet<ApplicationUser> ApplicationUsers { get; set; }

I get error:

Multiple object sets per type are not supported. The object sets 'ApplicationUsers' and 'Users' can both contain instances of type 'IdentitySample.Models.ApplicationUser'

I want to add CreatedBy as foreign key to AspNetUsers, using which the template generate CreatedBy dropdown similar to GroupId dropdown:

How can we use Identity generated tables with other user created tables, where user created table have Foreign Key references to Identity generated tables. And have first class scaffolding code generation experience using "MVC 5 Controller with views, using Entity Framework"?
(Similar to what we have with Customers & Groups table, where Customers table have GroupId Foreign key reference)

Solution 1

RTFE: you do not have a property ApplicationUsers on your ApplicationDbContext class.

Just add:

    public DbSet<ApplicationUser> ApplicationUsers { get; set; }

Solution 2

Entity Framework is smart enough to recognize conventional name for IDs/keys. So in order to add a foreign key you would do this:

public class Customer
{
    public string CustomerId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int Age { get; set; }

    public int ApplicationUserId { get; set; }
    public virtual ApplicationUser ApplicationUser { get; set; } 
}

By the convention it matches a class name plus “Id” as a foreign key. If you don't want to use this convention then you can use ForeignKey data annotation to help EF understand what should be the foreign key:

public class Customer
{
    public string CustomerId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int Age { get; set; }

    public int UserId { get; set; }
    [ForeignKey("UserId")]
    public ApplicationUser ApplicationUser { get; set; }
}

Take a look at this and this articles for more info.