继承和组合外键 - 在基类中的关键的一部分，在派生类中的另一部分 [英] Inheritance and composite foreign keys - one part of the key in base class, the other part in derived class

问题描述

我有问题要建立一个实体框架code-首先映射下面的示例数据库模式（在SQL Server）：

I am having problems to create an Entity Framework Code-First mapping for the following sample database schema (in SQL Server):

每个表中包含 TenantId 这是所有（复合材料）的一部分的主键和外键（多租户）。

Every table contains a TenantId which is part of all (composite) primary and foreign keys (Multi-Tenancy).

A 公司或者是一个客户或公司我尝试通过表，每类模型这个（TPT）继承映射：

A Company is either a Customer or a Supplier and I try to model this via Table-Per-Type (TPT) inheritance mapping:

public abstract class Company
{
    public int TenantId { get; set; }
    public int CompanyId { get; set; }

    public int AddressId { get; set; }
    public Address Address { get; set; }
}

public class Customer : Company
{
    public string CustomerName { get; set; }

    public int SalesPersonId { get; set; }
    public Person SalesPerson { get; set; }
}

public class Supplier : Company
{
    public string SupplierName { get; set; }
}

用流利的API映射：

Mapping with Fluent API:

modelBuilder.Entity<Company>()
    .HasKey(c => new { c.TenantId, c.CompanyId });

modelBuilder.Entity<Customer>()
    .ToTable("Customers");

modelBuilder.Entity<Supplier>()
    .ToTable("Suppliers");

基表公司有一个一对多的关系，一个地址（每家公司都有一个地址，不管客户或供应商），我可以创建此关联的映射：

The base table Companies has a one-to-many relationship to an Address (every company has an address, no matter if customer or supplier) and I can create a mapping for this association:

 modelBuilder.Entity<Company>()
     .HasRequired(c => c.Address)
     .WithMany()
     .HasForeignKey(c => new { c.TenantId, c.AddressId });

外键组成的主键的一个部分 - 的 TenantId - 和一个单独的列 - 在 AddressId 。这个工程。

The foreign key is composed of one part of the primary key - the TenantId - and a separate column - the AddressId. This works.

正如你可以在数据库架构看，从数据库的角度来看的关系客户和人基本同类型的一对许多关系，公司和地址之间 - 的外键再由 TenantId （主键的一部分）和列 SalesPersonId 。 （只有一个客户有一个销售人员，而不是一个公司，所以关系是在派生类中这个时候，而不是在基类。）

As you can see in the database schema, from database perspective the relationship between Customer and Person is basically the same kind of one-to-many relationship as between Company and Address - the foreign key is composed again of the TenantId (part of the primary key) and the column SalesPersonId. (Only a customer has a sales person, not a Supplier, therefore the relationship is in the derived class this time, not in the base class.)

我尝试创建与流利的API，这种关系像以前一样以同样的方式映射：

I try to create a mapping for this relationship with Fluent API the same way as before:

modelBuilder.Entity<Customer>()
    .HasRequired(c => c.SalesPerson)
    .WithMany()
    .HasForeignKey(c => new { c.TenantId, c.SalesPersonId });

但是，当EF试图编译模型中的 InvalidOperationException异常被抛出：

外键组成部分TenantId'不声明的属性上   型客户。验证它没有被明确排除在   该模型，它是一个有效的原始属性。

The foreign key component 'TenantId' is not a declared property on type 'Customer'. Verify that it has not been explicitly excluded from the model and that it is a valid primitive property.

显然，我不能构成从属性的外键在基类和派生类的另一个属性（虽然在数据库架构中的外键是由列的两个的派生型的表客户）。

Apparently I cannot compose a foreign key from a property in the base class and from another property in the derived class (although in the database schema the foreign key is composed of columns both in the derived type's table Customer).

我试着两项修改得到它的工作也许是：

I tried two modifications to get it working perhaps:

• 改的客户和人来一个独立的协会，即外键关联移走的财产 SalesPersonId ，然后试图映射：

• Changed the foreign key association between Customer and Person to an independent association, i.e. removed the property SalesPersonId, and then tried the mapping:

modelBuilder.Entity<Customer>()
    .HasRequired(c => c.SalesPerson)
    .WithMany()
    .Map(m => m.MapKey("TenantId", "SalesPersonId"));

这没有帮助（我真的不希望它会）和例外是：

It doesn't help (I didn't really hope, it would) and the exception is:

指定的架构是无效的。 ......在一个类型必须在每个属性名   是唯一的。属性名称TenantId'已经定义。

Schema specified is not valid. ... Each property name in a type must be unique. Property name 'TenantId' was already defined.

变更TPT到TPH映射，即去掉了两个 ToTable 通话。但它抛出同样的异常。

Changed TPT to TPH mapping, i.e. removed the two ToTable calls. But it throws the same exception.

我看到两种解决方法：

• 介绍一个 SalesPersonTenantId 到客户类：

public class Customer : Company
{
    public string CustomerName { get; set; }

    public int SalesPersonTenantId { get; set; }
    public int SalesPersonId { get; set; }
    public Person SalesPerson { get; set; }
}

和映射：

modelBuilder.Entity<Customer>()
    .HasRequired(c => c.SalesPerson)
    .WithMany()
    .HasForeignKey(c => new { c.SalesPersonTenantId, c.SalesPersonId });

我测试了这一点，它的工作原理。我却要 SalesPersonTenantId 在客户一个新的列表中除了 TenantId 。此列是多余的，因为两列总是必须从业务的角度相同的值。

I tested this and it works. But I will have a new column SalesPersonTenantId in the Customers table in addition to the TenantId. This column is redundant because both columns always must have the same value from business perspective.

放弃继承映射，并创建公司之间的一个一对一的映射和客户之间公司和公司。 公司必须成为一个具体的类型，然后，不是抽象的，我会在公司两个导航性能。但这种模式也不会EX preSS正确的，一个公司的或者的客户的或的供应商，并在同一时间不能同时。我没有测试，但我认为这是可行的。

Abandon inheritance mapping and create one-to-one mappings between Company and Customer and between Company and Supplier. Company must become a concrete type then, not abstract and I would have two navigation properties in Company. But this model wouldn't express correctly that a company is either a customer or a supplier and cannot be both at the same time. I didn't test it but I believe it would work.

我贴全例子中，我与（控制台应用程序，参考EF 4.3.1组装，通过的NuGet下载）在这里进行测试，如果有人喜欢尝试吧：

I paste the full example I tested with (console application, reference to EF 4.3.1 assembly, downloaded via NuGet) in here if someone likes to experiment with it:

using System;
using System.Data.Entity;

namespace EFTPTCompositeKeys
{
    public abstract class Company
    {
        public int TenantId { get; set; }
        public int CompanyId { get; set; }

        public int AddressId { get; set; }
        public Address Address { get; set; }
    }

    public class Customer : Company
    {
        public string CustomerName { get; set; }

        public int SalesPersonId { get; set; }
        public Person SalesPerson { get; set; }
    }

    public class Supplier : Company
    {
        public string SupplierName { get; set; }
    }

    public class Address
    {
        public int TenantId { get; set; }
        public int AddressId { get; set; }

        public string City { get; set; }
    }

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

        public string Name { get; set; }
    }

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

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Company>()
                .HasKey(c => new { c.TenantId, c.CompanyId });

            modelBuilder.Entity<Company>()
                .HasRequired(c => c.Address)
                .WithMany()
                .HasForeignKey(c => new { c.TenantId, c.AddressId });

            modelBuilder.Entity<Customer>()
                .ToTable("Customers");

            // the following mapping doesn't work and causes an exception
            modelBuilder.Entity<Customer>()
                .HasRequired(c => c.SalesPerson)
                .WithMany()
                .HasForeignKey(c => new { c.TenantId, c.SalesPersonId });

            modelBuilder.Entity<Supplier>()
                .ToTable("Suppliers");

            modelBuilder.Entity<Address>()
                .HasKey(a => new { a.TenantId, a.AddressId });

            modelBuilder.Entity<Person>()
                .HasKey(p => new { p.TenantId, p.PersonId });
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            Database.SetInitializer(new DropCreateDatabaseAlways<MyContext>());
            using (var ctx = new MyContext())
            {
                try
                {
                    ctx.Database.Initialize(true);
                }
                catch (Exception e)
                {
                    throw;
                }
            }
        }
    }
}

问：是否有任何方式对数据库架构映射上面一类模型与实体框架

推荐答案

嗯，我似乎无法在任何东西，所以我加入这个作为一个答案发表评论。

Well, I can't seem to comment on anything so I'm adding this as an answer.

我创建了一个问题，在codePLEX针对此问题，所以希望他们将寻找到它很快。敬请期待！

I created an Issue on CodePlex for this problem so hopefully they will look into it soon. Stay tuned!

HTTP：//entityframework.$c$cplex.com/workitem/865

发行在codePLEX（已在此期间被关闭）的结果是，该方案的问题是不支持的，目前有没有计划，以支持它在不久的将来。

Result of the Issue at CodePlex (which has been closed in the meantime) is that the scenario in the question is not supported and there are currently no plans to support it in the near future.

从实体框架团队codePLEX报价：

Quote from the Entity Framework team at CodePlex:

这是一个更根本的限制，其中EF不支持部分   具有在碱型定义的属性，然后用它作为一个   外键的派生型。不幸的是，这是一个限制，即   将很难从我们的code碱基删除。由于我们还没有   看多了吧请求，这不是我们计划   在这个阶段地址，以便我们正在关闭这个问题。

This is part of a more fundamental limitation where EF doesn't support having a property defined in a base type and then using it as a foreign key in a derived type. Unfortunately this is a limitation that would be very hard to remove from our code base. Given that we haven't seen a lot of requests for it, it's not something we are planning to address at this stage so we are closing this issue.

这篇关于继承和组合外键 - 在基类中的关键的一部分，在派生类中的另一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！