实体框架CTP5代码- first:用另一个类的多个集合映射一个类

With EF CTP5 Code-First I am trying to map a class model which contains multiple collections in one class pointing to another class. Here is an example of what I mean:

使用EF CTP5代码- first，我试图映射一个类模型，该模型包含一个类中的多个集合指向另一个类。我的意思是:

public class Company
{
    public int CompanyId { get; set; }
    public IList<Person> FemaleEmployees { get; set; }
    public IList<Person> MaleEmployees { get; set; }
}

public class Person
{
    public int PersonId { get; set; }
    public Company Company { get; set; }
}

If I let the database create from this model with a DbContext without further customization, like so:

如果我让数据库使用DbContext从这个模型创建，而不进行进一步的定制，如下所示:

public class MyContext : DbContext
{
    public DbSet<Company> Companies { get; set; }
    public DbSet<Person> People { get; set; }
}

... then I get two tables in SQL Server, a simple Companies table with only a CompanyId column and a People table with the following columns ("FKRN" means "Foreign Key Relationship Name", as created by EF in SQL Server):

…然后，我在SQL Server中得到两个表，一个简单的公司表，只有一个CompanyId列和一个具有以下列的人员表(“FKRN”表示“外键关系名称”，由EF在SQL Server中创建):

PersonId            int     not nullable
CompanyCompanyId    int     nullable       FKRN: Company_FemaleEmployees
CompanyCompanyId1   int     nullable       FKRN: Company_MaleEmployees
CompanyCompanyId2   int     nullable       FKRN: Person_Company

The last three columns have all a foreign key relationship to the primary key CompanyId of the Companies table.

最后三列都与公司表的主关键公司id有外键关系。

Now I have several questions:

现在我有几个问题:

• 1) Why do I get three foreign key columns in the People table? I actually expected two. If I remove the property public Company Company { get; set; } from the Person the third column CompanyCompanyId2 disappears but I also lose the reference property in the class.

  1)为什么在人员表中有三个外键列?我预计两个。如果我移走物业上市公司的股份;设置;从第三列CompanyCompanyId2消失的人身上，我也失去了类中的reference属性。

• 2) Let's say I drop the Company property from the Person table (I don't need it really in my model). Is there a way to give the two remaining foreign key columns another name than the auto-created CompanyCompanyId and CompanyCompanyId1? (For instance FCompanyId and MCompanyId to indicate the relation to the FemaleEmployees and MaleEmployees collections.)

  假设我从Person表中删除公司属性(在我的模型中不需要它)。除了自动创建的companycompanycompanyid和companyid1之外，还有什么方法可以给剩下的两个外键列另一个名称吗?(例如FCompanyId和MCompanyId，用来表示与女性雇员和男性雇员集合的关系。)

• 3) Is there any way to define this model with only one foreign key CompanyId in the People table? Surely I would need a differentiating additional column in the Person class (like bool IsFemale). A Person is either part of the FemaleEmployees or the MaleEmployees collection, never in both (naturally in this example), so with SQL I could fetch those collections by something like WHERE IsFemale = true/false AND CompanyId = 1. I am wondering if I could give EntityFramework a hint to load the two collections this way. (Here I would like to avoid to extend the model by a FemalePerson and MalePerson class which both derive from Person as base class and then use for instance Table-Per-Hierarchy mapping, since these derived classes would be empty and artificial and had no other purpose except enabling the mapping to SQL Server.) Having only one foreign key CompanyId would allow me to make it non-nullable which isn't possible with two foreign keys (both can never be non-null in the same row).

  3)是否有办法在人员表中定义只有一个外键公司id的模型?当然，我需要在Person类中添加一个不同的列(比如bool IsFemale)。一个人要么是女性雇员集合的一部分，要么是男性雇员集合的一部分，两者都不是(在本例中，这是自然的)，因此，使用SQL，我可以通过IsFemale = true/false和CompanyId = 1之类的方法获取这些集合。我想知道我是否可以给EntityFramework一个提示，让它以这种方式加载这两个集合。(这里我想避免扩展模型FemalePerson和MalePerson类都源于人的基类,然后使用例如Table-Per-Hierarchy映射,因为这些派生类将空空如也和人工,没有其他目的,除了使映射到SQL Server)。只有一个外键连id就可以使它不可为空，这对于两个外键是不可能的(在同一行中，两个键都不能为非空)。

Thank you for feedback and suggestions in advance!

感谢您的反馈和建议!

2 个解决方案

#1

• To question (1): EF cannot map the single reference property Company in class Person to two different collection endpoints FemaleEmployees and MaleEmployees in class Company at the same time. The mapping conventions assume that there is actually a third endpoint in Company which isn't exposed in the model. Therefore a third foreign key is created.

  问题(1):EF不能同时将类人的单个参考属性公司映射到类公司的两个不同的集合端点，即FemaleEmployees和MaleEmployees。映射约定假定公司中实际上有第三个端点不在模型中公开。因此创建了第三个外键。

• To question (2): With the EF 4.1 Release Candidate it is now possible to specify the database column name of foreign keys in the Fluent API (which wasn't possible with EF CTP5) by using the Map method of the ForeignKeyNavigationPropertyConfiguration class:

  问题(2):通过使用ForeignKeyNavigationPropertyConfiguration类的Map方法，现在可以在Fluent API中指定外键的数据库列名(这在EF CTP5中是不可能的):

  modelBuilder.Entity<Company>()
              .HasMany(c => c.FemaleEmployees)
              .WithOptional()
              .Map(conf => conf.MapKey("FCompanyId"))
              .WillCascadeOnDelete(false);
  
  modelBuilder.Entity<Company>()
              .HasMany(c => c.MaleEmployees)
              .WithOptional()
              .Map(conf => conf.MapKey("MCompanyId"))
              .WillCascadeOnDelete(false);
  

• To question (3): I still have no idea.

  问(3):我还是不知道。

Edit

编辑

Just to close this old question now: (3) (relating two navigation properties in one entity to the same endpoint of another entity) is not possible, for example: Specific Entity Framework Code First Many to 2 Model Mapping ... (and I remember many other questions which were looking for a solution for such a scenario without success)

现在结束这个老问题:(3)(将一个实体中的两个导航属性与另一个实体的相同端点关联)是不可能的，例如:特定的实体框架代码首先是许多到两个模型映射…(我还记得很多其他的问题，这些问题都是在寻找解决方案，但没有成功)

#2

I think you could do this:

我认为你可以这样做:

public class Company
{
    public int CompanyId { get; set; }
    public ICollection<Person> Employees { get; set; }
    public IEnumerable<Person> MaleEmployees {
        get
        {
            Employees.Where(x=> !x.IsFemale);
        }
    }
}

public class Person
{
    public int PersonId { get; set; }
    public Company Company { get; set; }
}

You just have one CompanyID FK at People table.

你只有一个公司在员工桌。

You can load Male Employees using EF context:

您可以使用EF上下文加载男性员工:

context.Entry(companyInstance)
    .Collection(p => p.Employees)
    .Query()
    .Where(u => !u.IsFemale)
    .Load();

I think that your approach isn´t so good, because, what happens when I add a male to Company.FemaleEmployees? EF dont know this rules

我认为你的方法是´t那么好,因为,当我添加一个男性Company.FemaleEmployees怎么办?我不知道这个规则