实体框架:使用多个连接表

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.

目标是成立公司，每个公司都有一些部门。部门可以在公司之间共享。如:公司1可以有部门1、2和3。公司2可以有1、5、8、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.

BusinessUnit 1可以进入第1公司的第1部门，但不能进入第2公司的第1部门。

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

CompanyDepartment配置表非常明显。它将公司与(可能)多个部门联系起来。

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).

使用CompanyDepartmentBusinessUnit配置表将BusinessUnits配置表连接到公司的各个部门。在这个表格中，公司id和部门组成了公司部门(也就是:公司id和部门)的主键的复合外接键。

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

我在实体框架中使用数据库优先的方法。对于简单的连接表，我在DbContext中重写了onmodelcreation方法。

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).

假设我的用户选择查看公司1的部门。我想要过滤所有与公司1相关但对用户所在的BusinessUnit可见的部门(例如BusinessUnit 2)。

Thank you in advance and enjoy your holidays!

提前谢谢您，祝您节日愉快!

1 个解决方案

#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.

EF只允许在(1)没有附加列的情况下使用隐式连接表;(2)如果与多对多关系的两端不同的其他实体没有引用隐式连接表的话。

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.

CompanyDepartment满足条件(1)，但不满足条件(2)，因为它是由CompanyDepartmentBusinessUnit引用的，所以需要使用一对多关系的explcit实体。

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.

一旦这样做了，就可以看出现在的CompanyDepartmentBusinessUnit满足这两个条件，因此可以用BusinessUnit和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:

并考虑到默认的EF约定，使用以下最简单的fluent配置:

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")
    );

#1