Need many to many relationship for multiple properties on one entity EF Core 2.2

Question

I have an entity for Users and an entity for Projects.

I need to be able to assign multiple users to 3 different list properties on my project entity. I have been able to do this successfully for one property (the many to many relationship) by a join entity. I could specify the UserType on the Users table and just use the one property, but I may run into scenarios where Users may perform more than one role (type) and then that wouldn't work.

I thought I could just put the UserType on the join table (entity) but I'm at a loss as to how to build that entity in my DBContext.

Here is what I have that's working with one property defined:

ProjectEntity:

public class Project : IInt32Identity
{
    public int Id { get; set; }
    public string ProjectName { get; set; }
    ...
    public bool ProjectActive { get; set; }
    public List<ProjectFile> ProjectFiles { get; set; }
    public List<ProjectUsers> ProjectUsers { get; set; }
    public DateTime ProjectCreatedDate { get; set; }
    public DateTime ProjectModifiedDate { get; set; }
}

UserEntity:

public class User : IInt32Identity
{
    public int Id { get; set; }
    public string UserEmail { get; set; }
    ...
    public List<ProjectUsers> ProjectUsers { get; set; }
    public DateTime UserCreatedDate { get; set; }
    public DateTime UserLastLoggedInDate { get; set; }
    public DateTime UserModifiedDate { get; set; }
}

JoinEntity:

public class ProjectUsers
{
    public int UserId { get; set; }
    public User User { get; set; }
    public int ProjectId { get; set; }
    public Project Project { get; set; }
}

And my OnModelCreating() in my DBContext

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<ProjectUsers>()
            .HasKey(bc => new { bc.UserId, bc.ProjectId });
        modelBuilder.Entity<ProjectUsers>()
            .HasOne(bc => bc.User)
            .WithMany(b => b.ProjectUsers)
            .HasForeignKey(bc => bc.UserId);
        modelBuilder.Entity<ProjectUsers>()
            .HasOne(bc => bc.Project)
            .WithMany(c => c.ProjectUsers)
            .HasForeignKey(bc => bc.ProjectId);
    }

That all works fine as I said above, but here's what I would like:

ProjectEntity:

public class Project : IInt32Identity
{
    public int Id { get; set; }
    public string ProjectName { get; set; }
    ...
    public bool ProjectActive { get; set; }
    public List<ProjectFile> ProjectFiles { get; set; }
    public List<ProjectUsers> ProjectClients { get; set; }
    public List<ProjectUsers> ProjectBuilders { get; set; }
    public List<ProjectUsers> ProjectDesigners { get; set; }
    public DateTime ProjectCreatedDate { get; set; }
    public DateTime ProjectModifiedDate { get; set; }
}

UserEntity is the same.

JoinEntity:

public class ProjectUsers
{
    public int UserId { get; set; }
    public User User { get; set; }
    public int ProjectId { get; set; }
    public Project Project { get; set; }
    public string UserType { get; set; }
}

Where I'm lost is on the OnModelBinding() code and I'm also not sure if EF would be smart enough to populate the lists correctly based on that UserType meta property.

Any help or guidance would be greatly appreciated.

TIA

Answer 1

It might seem possible to treat ProjectUser as the base class/entity, and create different class/entity/type for ProjectClient, ProjectBuilder and ProjectDesigner that are inherited from ProjectUser. And then you create tables for each type and one-to-many relationship to the project. This is typically called Table Per Type (TPT) approach.

However, TPT is not yet implemented in EF Core.

You can still achieve it using Table Per Hierarchy (TPH), but you will have just one list in the project for all project users, where UserId, ProjectId and UserType become the complex key. Project clients, builders and designers will be calculated properties off that one project user list.

Entities

public class Project
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<ProjectUser> ProjectUsers { get; set; }

    public IEnumerable<ProjectUser> ProjectClients => this.ProjectUsers
        .Where(x => x.UserType == "Client");
    public IEnumerable<ProjectUser> ProjectBuilders => this.ProjectUsers
        .Where(x => x.UserType == "Builder");
    public IEnumerable<ProjectUser> ProjectDesigners => this.ProjectUsers
        .Where(x => x.UserType == "Designer");
}

public class User
{
    public int Id { get; set; }
    public string Email { get; set; }

    public virtual ICollection<ProjectUser> UserProjects { get; set; }
}

public class ProjectUser
{
    public int UserId { get; set; }
    public virtual User User { get; set; }

    public int ProjectId { get; set; }
    public virtual Project Project { get; set; }

    public string UserType { get; set; }
}

Configurations

public class ProjectConfiguration : IEntityTypeConfiguration<Project>
{
    public void Configure(EntityTypeBuilder<Project> builder)
    {
        builder.HasKey(x => x.Id);
        builder.Property(x => x.Name).IsRequired();
        builder.HasIndex(x => x.Name).IsUnique();

        builder.Ignore(x => x.ProjectBuilders);
        builder.Ignore(x => x.ProjectClients);
        builder.Ignore(x => x.ProjectDesigners);

        builder.ToTable("Project");
    }
}

public class UserConfiguration : IEntityTypeConfiguration<User>
{
    public void Configure(EntityTypeBuilder<User> builder)
    {
        builder.HasKey(x => x.Id);
        builder.Property(x => x.Email).IsRequired();
        builder.HasIndex(x => x.Email).IsUnique();

        builder.ToTable("User");
    }
}

public class ProjectUserConfiguration : IEntityTypeConfiguration<ProjectUser>
{
    public void Configure(EntityTypeBuilder<ProjectUser> builder)
    {
        builder.HasKey(x => new { x.ProjectId, x.UserId, x.UserType });
        builder.Property(x => x.UserType).IsRequired();

        builder.HasOne(x => x.Project)
            .WithMany(x => x.ProjectUsers)
            .HasForeignKey(x => x.ProjectId);

        builder.HasOne(x => x.User)
            .WithMany(x => x.UserProjects)
            .HasForeignKey(x => x.UserId);
    }
}

The virtual keyword is there for lazy loading support. If you're not doing lazy loading, you don't have to have virtual there. Also you have to [NotMapped] those 3 calculated properties, which is the same as using .Ignore in fluent API's speaking.

DbContext

public class AppDbContext : DbContext
{
    public DbSet<Project> Projects { get; set; }
    public DbSet<User> Users { get; set; }

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

        modelBuilder.ApplyConfiguration(new ProjectConfiguration());
        modelBuilder.ApplyConfiguration(new UserConfiguration());
        modelBuilder.ApplyConfiguration(new ProjectUserConfiguration());
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);

        optionsBuilder
            .UseLazyLoadingProxies()
            .UseSqlServer("Data Source=.\\SQLEXPRESS;Initial Catalog=DL.SO.ProjectUsersDemo;Integrated Security=True;MultipleActiveResultSets=False;");
    }
}

Nothing special here. After you add the migration and update the database, yours should look like

After seeding the database with sample data, although it's hard to show here, you can see those 3 lists are filled with correct data: