How to use TPH (Table Per Hierarchy) with ASP.NET Identity 2

Question

I use ASP.NET Identity 2 in an MVC5 project and there are 2 type of user classes called Student and Coordinator as shown below. On the other hand, I tried to follow TPH (Table Per Hierarchy) approach so that using the same entity for both type of users.

public class Student : ApplicationUser
{
    public int? Number { get; set; }
}


public class Coordinator : ApplicationUser
{
    public string Room { get; set; }
}


public class ApplicationUser : IdentityUser<int, ApplicationUserLogin,
     ApplicationUserRole, ApplicationUserClaim>, IUser<int>
{
    //Custom Properties 
    public string Name { get; set; }

    public string Surname { get; set; }

    //code omitted for brevity
}

As ApplicationUser is already inherited from IdentityUser, I did not create it as an abstract class and did not add it to my DbContext as shown below:

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

On the other hand, there is another entity having Student (not ApplicationUser) as navigation property shown below:

public class Grade
{
    [Key]
    public int Id { get; set; }

    public int Grade { get; set; }

    //Navigation properties ===========================
    public virtual Experiment Experiment { get; set; }

    public virtual Student Student { get; set; }

    //public virtual ApplicationUser User { get; set; }
    //=================================================
}

}

However, when adding DbSet to DbContext I encountered "Multiple object sets per type are not supported. The object sets 'ApplicationUsers' and 'Users' can both contain instances of type 'Xxxx.ApplicationUser'." error. So, is there any mistake regarding to the approach above? Should I use DbSet in DbContext and should I add ApplicationUser as navigation property to Grade entity? Any help would be appreciated...

Answer 1

You should be able to do it without adding an ApplicationUsers DbSet to your DbContext, just like this:

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public DbSet<Student> Students { get; set; }
    public DbSet<Coordinator> Coordinators { get; set; }
    public DbSet<Grade> Grades { get; set; }
    ...
}

And it is okay to use the Student type in the navigation property of your Grade entity, you shouldn't have to use ApplicationUser.

Check that your database is synchronized with your model. Are you using EF migrations? Your tables in the migration files should look like this: (note the Discriminator field in AspNetUsers table, and the Number and Room fields. Those are proof of TPH being applied to the table creation. Also, no tables for Student or Coordinator were included in the migration.)

        CreateTable(
            "dbo.AspNetUsers",
            c => new
                {
                    Id = c.String(nullable: false, maxLength: 128),
                    Name = c.String(),
                    Surname = c.String(),
                    Email = c.String(maxLength: 256),
                    EmailConfirmed = c.Boolean(nullable: false),
                    PasswordHash = c.String(),
                    SecurityStamp = c.String(),
                    PhoneNumber = c.String(),
                    PhoneNumberConfirmed = c.Boolean(nullable: false),
                    TwoFactorEnabled = c.Boolean(nullable: false),
                    LockoutEndDateUtc = c.DateTime(),
                    LockoutEnabled = c.Boolean(nullable: false),
                    AccessFailedCount = c.Int(nullable: false),
                    UserName = c.String(nullable: false, maxLength: 256),
                    Room = c.String(),
                    Number = c.Int(),
                    Discriminator = c.String(nullable: false, maxLength: 128),
                })
            .PrimaryKey(t => t.Id)
            .Index(t => t.UserName, unique: true, name: "UserNameIndex");

        CreateTable(
            "dbo.Grades",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    GradeValue = c.Int(nullable: false),
                    Student_Id = c.String(maxLength: 128),
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("dbo.AspNetUsers", t => t.Student_Id)
            .Index(t => t.Student_Id);

If your tables don't look like these, try reverting to an empty database, deleting the migrations and generating them again, and finally updating the database with the good migrations.

I created a dummy MVC project with your entities and just those three DbSets in the DbContext, generated the migrations and created the database tables, and run the following test code (from the default controller index method). The data were correctly saved in the database:

    public void Test()
    {
        using (var context = new ApplicationDbContext())
        {
            var student = new Student
            {
                Id = "12345",
                Name = "John",
                Number = 123,
                UserName = "Johnny",
                Email = "[email protected]"
            };
            context.Students.Add(student);
            context.SaveChanges();
        }

        using (var context = new ApplicationDbContext())
        {
            var student = context.Students.Find("12345");
            var grade = new Grade { Id = 333, GradeValue = 90, Student = student };
            context.Grades.Add(grade);
            context.SaveChanges();
        }
    }

Some questions:

• The "Multiple object sets" error message says something about "The object sets 'ApplicationUsers' and 'Users'". Where is this 'Users' object set coming from, do you have a Users DbSet? Try removing it. Or perhaps you did a copy-paste error with the type parameter of the DbSet, something like writing public DbSet<wrongType> GoodTypeName (it happens).

• I had problems with this declaration:

  public class ApplicationUser : IdentityUser<int, ApplicationUserLogin, ApplicationUserRole, ApplicationUserClaim>, IUser<int>
  { ... }
  

  So I used this one (no type parameters in the base class):

  public class ApplicationUser : IdentityUser
  { ... }
  

  It seems that the default implementation of MVC project with Identity expects the ApplicationUser to extend the implementation of IdentityUser with no type parameters. Otherwise there are compilation errors in several places, for instance when declaring the ApplicationContextand trying to use ApplicationUser as type parameter of the base class IdentityDbContext.

• If you use the base class with type parameters, I think the TKey type parameter should be String and not int, unless you have changed the User.Id property type in your custom entities. Perhaps you are getting errors because of that? In the database the Id fields of these entities have the type varchar.

• I changed the name of Grade property to GradeValue (Grade is not a valid property name because it is the name of the class, I was getting a compilation error).

If you need it I can send the code of the dummy solution I used to test this.