ASP.NET MVC 4 with Entity Framework and SimpleMembership linking data between tables

Question

I currently have a ASP.NET MVC4 application in which I am trying to set up SimpleMembership for user accounts. So far, I have a database setup automatically with EF code first migrations and have gotten it to generate the SimpleMembership tables in my database.

I would like to have it set up so that there is a relationship between user that is currently logged in and a specific entity that I have created. I have two different types of users: Students & Teachers. Both of these are derived from a base "User" class.

For example: When a student logs in, he/she will have access to things such as their account and other data stored in a separate student table different from the "UserProfile" table created by EF.

I have done some research into implementing this and I believe that I need to create a link using "UserId" as a foreign key attribute for the users. But what are the next steps I need to take after that to actually be able to store information into different tables?

What is the best approach to take for tying together data from the SimpleMembership table and the Student table through the UserId key?

Here are my user classes with what I believe is the correct way to set up the relationship between the two.

User

    public class User
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int UserId { get; set; }

    [Display(Name = "First Name")]
    [Required(ErrorMessage = "First name is required.")]
    [MaxLength(50)]
    public virtual string FirstName { get; set; }

    [Display(Name = "Last Name")]
    [Required(ErrorMessage = "Last name is required.")]
    [MaxLength(50)]
    public virtual string LastName { get; set; }
}

Student

    public class Student : User
{
    public virtual ICollection<Account> Accounts { get; set; }
}

Teacher

public class Teacher : User
{
    public virtual ICollection<Student> Students { get; set; }
    public virtual ICollection<Classroom> Classrooms { get; set; }
}

Answer 1

Since there are comments about issues when integrating SimpleMembershipProvider within a single DbContext within a single app I would tackle this problem by implementing a loosely coupled ORM approach using EF by creating two separate projects where one is a class library which initializes a database that contains the 4 tables plus the UserProfile table and the App project itself. If you have problems with SMP in a class library create an empty MVC4 web application using vs2012 instead.

This ultimately creates two DbContext in which you will have to use some architectural pattern for your DAL (WebApi, Repo, IoC, etc..) which in turn can be its own class library for reusability. Just make sure you provide referential integrity between the data models in both application/projects using UserId FK.

1. Create Two Projects; Class Library & Web App MVC4

SMPIdentity Project (class library)

• dbo.UsersInRoles
• dbo.Roles
• dbo.Memberships
• dbo.OAuthMembership
• dbo.UserProfile

Application Project (Web Application)

• dbo.Students
• dbo.Classroom
• etc...

2.SMPIdentity Class Library Project

You will have to install these nuget packages using console manager for SimpleMembershipProvider (4.0) to work:

• PM> Install-Package Microsoft.AspNet.WebPages.OAuth
• PM> Install-Package EntityFramework -Version 5.0.0
• PM> Install-Package Microsoft.AspNet.Mvc -Version 4.0.30506
• PM> Install-Package WebMatrix.WebData

3.Enable-migrations in SMPIdentity Class Library

• PM> Enable-Migrations

4.Add to .config file in SMPIdentity project:

     <connectionStrings>
        <add name="SMPConnection" providerName="System.Data.SqlClient" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=SMP;Integrated Security=True;MultipleActiveResultSets=False" />
      </connectionStrings>

<system.web>
     <roleManager enabled="true" defaultProvider="SimpleRoleProvider">
      <providers>
        <clear />
        <add name="SimpleRoleProvider" type="WebMatrix.WebData.SimpleRoleProvider, WebMatrix.WebData" />
      </providers>
    </roleManager>
    <membership defaultProvider="SimpleMembershipProvider">
      <providers>
        <clear />
        <add name="SimpleMembershipProvider" type="WebMatrix.WebData.SimpleMembershipProvider, WebMatrix.WebData" />
      </providers>
    </membership>
    <pages>
      <namespaces>
        <add namespace="System.Web.Helpers" />
        <add namespace="System.Web.Mvc" />
        <add namespace="System.Web.Mvc.Ajax" />
        <add namespace="System.Web.Mvc.Html" />
        <add namespace="System.Web.Routing" />
        <add namespace="System.Web.WebPages" />
      </namespaces>
    </pages>
  </system.web>

6.Create an empty DbContext class (SMPEntities.cs) to store SimpleMembershipProvider tables and UserProfile table if you wish to add additional properties to UserProfile model.If not just leave blank

using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;

namespace SMPIdentityMVC4
{
    public class SMPContext : DbContext
    {
        public SMPContext()
            : base("name=SMPConnection")
        {
        }
        //add DbSet<UserProfile> Users if you want custom properties   
        //dbo.UserProfile will only have int UserId & string UserName
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            base.OnModelCreating(modelBuilder);
        }

    }

    //Create if you want custom properties
    [Table("UserProfile")]
    public class UserProfile
    {
        //...ctors here
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int UserId { get; set; }
        public string UserName { get; set; }
        [DataType(DataType.EmailAddress)]
        [Display(Name = "Email address")]
        public string Email { get; set; }
        [ScaffoldColumn(false), Required]
        public string ImageUrl { get; set; }
        [DisplayFormat(DataFormatString = "{0}")] //{0:d} or {0:D}
        [DataType(DataType.DateTime), ScaffoldColumn(false)]
        public DateTime DateJoined { get; set; }
    }

}

6.Create Database from CF Migrations in SMPIdentity class library:

• PM> Add-migration SMPInitial

  7.Edit Configuration.cs in Migrations folder and add System.Web reference to Project if needed:

  using System;
  using System.Data.Entity;
  using System.Data.Entity.Migrations;
  using System.Linq;
  using System.Web.Security;
  using WebMatrix.WebData;
  
  internal sealed class Configuration : DbMigrationsConfiguration<SMPContext>
  {
      public Configuration()
      {
          AutomaticMigrationsEnabled = true;
      }
  
      protected override void Seed(DomainContext context)
      {
  
           WebSecurity.InitializeDatabaseConnection(
              "SMPConnection",
              "UserProfile",
              "UserId",
              "UserName", autoCreateTables: true);
  
           if (!WebSecurity.UserExists("yardpenalty"))
              WebSecurity.CreateUserAndAccount(
              "yardpenalty",
              "password",
              new
              {
                  Email = "yardpenalty@email.com",
                  ImageUrl = "/Content/Avatars/yardpenalty.jpg",
                  DateJoined = DateTime.Now,
              },
              false);
  
          if (!Roles.RoleExists("Administrator"))
              Roles.CreateRole("Administrator");
          if (!Roles.RoleExists("Blogger"))
              Roles.CreateRole("Blogger");
  }
  

8. Initialize and populate Tables in SMP Database

• PM> Update-database

9. Create Actual Web Application and perform the following:

• Add Reference or entire SMPIdentity Project to actual MVC4 Web App

• Add connectionString for SMP so there is two connectionStrings; SMPConnection & DefaultConnection

• Choose Architectural Pattern for both DbContext and you are all set! You can use code First migrations on web app while SMP project is intact

Simplified sample code in a controller (Dependency Injection?):

  public class studentController : Controller
    {
        private readonly SimpleMembershipRepository _accounts = null;
        private readonly StudentRepository _students = null;
        public StudentController(IRepository students, IRepository account)
        {
            _accounts = accounts;
            _students = students;
        }
    }

Now you have an accounts database which includes SimpleMembershipProvider for an entire domain if you wish! Create a WebApi controller to access the Simple Membership Provider database for easy access.

Answer 2

You could generate Configuration classes that inherit from EntityTypeConfiguration, where T is your model class (Account, Student or Classroom in your case). Using the fluent API, you could create the many-to-many relationship, such as many students may have many accounts; the junction table of Student_x_Account could then represent the relationship of STUDENT_ID to ACCOUNT_ID.