Entity Framework: Using Multiple Junction Tables

Question

I've designed a database, paying a lot of attention to normalization. Here's a piece of it:

First of all, If you notice any issues with this design, feel free to let me know.

The goal is to have companies, each of which have some departments. Departments can be shared between companies. As in: Company 1 can have Department 1, 2 and 3. Company 2 can have Department 1, 5, 8 and 9.

The BusinessUnits will have access to departments. But it depends on the company to which a department is linked.

BusinessUnit 1 may have permission to access Department 1 of Company 1, but should not be able to access Department 1 of Company 2.

The CompanyDepartment config table is pretty obvious. It links a Company to (possibly) multiple departments.

The CompanyDepartmentBusinessUnit config table is used to link BusinessUnits to Departments of a Company. In this table, the CompanyId and DepartmentId form a composite Foreign Key to the primary key of CompanyDepartment (which is: CompanyId and DepartmentId as well).

I'm using a Database-First approach in Entity Framework. For the simple junction tables, I've overwritten the OnModelCreating method in my DbContext.

An example of how I did this:

My question now is: how do I do this for the CompanyDepartmentBusinessUnit relation?

Say that my user chose to see the departments of Company 1. I want to filter all the Departments that are linked to Company 1 but are also visible to the BusinessUnit in which the user resides (for instance Business Unit 2).

Thank you in advance and enjoy your holidays!

Answer 1

EF allows you to use implicit junction table only if (1) it has no additional columns and (2) if it's not referenced by other entity different than the two ends of the many-to-many relationships.

CompanyDepartment satisfies the condition (1), but not (2) because it's referenced from CompanyDepartmentBusinessUnit, hence you need to use explcit entity with two one-to-many relationships.

Once you do that, it can be seen that now CompanyDepartmentBusinessUnit satisfies both conditions, hence can be modelled with implicit junction table for BusinessUnit and CompanyDepartment.

With that being said, the final model would be something like this:

public class Company
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<CompanyDepartment> DepartmentLinks { get; set; }
}

public class Department
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<CompanyDepartment> CompanyLinks { get; set; }
}

public class BusinessUnit
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool IsPersonal { get; set; }
    public ICollection<CompanyDepartment> CompanyDepartments { get; set; }
}

public class CompanyDepartment
{
    public int CompanyId { get; set; }
    public int DepartmentId { get; set; }
    public Company Company { get; set; }
    public Department Department { get; set; }
    public ICollection<BusinessUnit> BusinessUnits { get; set; }
}

and taking into account the default EF conventions, with the following minimal fluent configuration:

modelBuilder.Entity<Company>().ToTable("Company");
modelBuilder.Entity<Department>().ToTable("Department");
modelBuilder.Entity<BusinessUnit>().ToTable("BusinessUnit");
modelBuilder.Entity<CompanyDepartment>().ToTable("CompanyDepartment");

modelBuilder.Entity<CompanyDepartment>()                
    .HasKey(e => new { e.CompanyId, e.DepartmentId });

modelBuilder.Entity<CompanyDepartment>()
    .HasMany(e => e.BusinessUnits)
    .WithMany(e => e.CompanyDepartments)
    .Map(m => m
        .MapLeftKey("CompanyId", "DepartmentId")
        .MapRightKey("BusinessUnitId")
        .ToTable("CompanyDepartmentBusinessUnit")
    );