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

问题描述

每个表都包含一个 TenantId ，它是所有（复合）主键和外键（Multi-Tenancy）的一部分。

A / code>是一个客户或供应商，我尝试通过Table-Per-类型（TPT）继承映射：

  public abstract class Company 
 {
 public int TenantId {get;组; } 
 public int CompanyId {get;组; } 
 
 public int AddressId {get;组; } 
 public Address Address {get;组; } 
} 
 
 public class Customer：Company 
 {
 public string CustomerName {get;组; } 
 
 public int SalesPersonId {get;组; } 
 public Person SalesPerson {get;组; 
} 
 
 public class供应商：公司
 {
 public string SupplierName {get;组; 
} 
  

使用Fluent API映射：

  modelBuilder.Entity< Company>（）
 .HasKey（c => new {c.TenantId，c.CompanyId} ）; 
 
 modelBuilder.Entity< Customer>（）
 .ToTable（Customers）; 
 
 modelBuilder.Entity< Supplier>（）
 .ToTable（Suppliers）; 
  

基表公司与$ 地址（每个公司都有一个地址，无论客户或供应商）的关系，我可以为此关联创建一个映射：

  modelBuilder.Entity< Company>（）
 .HasRequired（c => c.Address）
 .WithMany（）
 .HasForeignKey（c => new {c.TenantId，c.AddressId}）; 
  

外键由主键的一部分组成 - TenantId - 和另一列 - AddressId 。这个工作。

正如您在数据库架构中可以看到的那样，从数据库角度来说， Customer 和 与公司和地址 - 外键再次由 TenantId （主键的一部分）和列 SalesPersonId 组成。 （只有一个客户有一个销售人员，而不是一个供应商，因此这次关系是在派生类中，而不是在基类中。）

我尝试与Fluent API创建一个与Fluent API的关系映射，方法与以前一样：

  modelBuilder.Entity< Customer>（）
 .HasRequired（c => c.SalesPerson）
 .WithMany（）
 .HasForeignKey（c =>新的{c.TenantId，c.SalesPersonId}）; 
  

但是，当EF尝试编译模型时， InvalidOperationException 被抛出：

外键组件TenantId不是
上声明的属性，类型客户。验证它没有从
模型中明确排除，并且它是一个有效的原始属性。

显然我不能从基类中的属性和派生类中的另一个属性组成一个外键（尽管在数据库模式中，外键由导出类型的表 >

我尝试了两个修改，以使其正常工作：

• 将客户和 Person 之间的外键关联更改为独立关联，即删除财产 SalesPersonId ，然后尝试映射：

    modelBuilder.Entity< Customer>（）
   .HasRequired（c => c.SalesPerson）
   .WithMany（）
   .Map（m => m .MapKey（TenantId，SalesPersonId））; 
    

  它没有帮助（我没有真正希望，它会），异常是：

  
  

  指定的模式无效。 ...类型中的每个属性名称必须为
  是唯一的。属性名称TenantId已经定义。

  
  




• 将TPT更改为TPH映射，即删除两个 ToTable 呼叫。但是它也引起了同样的例外。

我看到两种解决方法：

• 将 SalesPersonTenantId 引入客户类：

    public class Customer：Company 
   {
   public string CustomerName {get;组; } 
   
   public int SalesPersonTenantId {get;组; } 
   public int SalesPersonId {get;组; } 
   public Person SalesPerson {get;组; } 
  } 
    

  和映射：

    modelBuilder.Entity< Customer>（）
   .HasRequired（c => c.SalesPerson）
   .WithMany ）
   .HasForeignKey（c => new {c.SalesPersonTenantId，c.SalesPersonId}）; 
    

  我测试了这个，它的工作原理。但是，除了 TenantId c $ c c $ c> c $ c> c code $ c SalesPersonTenantId / code>。此列是冗余的，因为两个列总是从业务角度来看必须具有相同的值。




• 放弃继承映射并在<$ c之间创建一对一的映射$ c>公司和客户之间公司和供应商。 公司必须成为一个具体的类型，而不是抽象，我将在公司中有两个导航属性。但是，这种模式不能正确表达一家公司是客户或供应商，也不能同时兼顾。我没有测试它，但我相信它会工作。

我粘贴了我测试的完整示例（控制台应用程序，参考EF 4.3.1汇编，通过NuGet下载）在这里，如果有人喜欢尝试：

  using System; 
使用System.Data.Entity; 
 
命名空间EFTPTCompositeKeys 
 {
 public abstract class Company 
 {
 public int TenantId {get;组; } 
 public int CompanyId {get;组; } 
 
 public int AddressId {get;组; } 
 public Address Address {get;组; } 
} 
 
 public class Customer：Company 
 {
 public string CustomerName {get;组; } 
 
 public int SalesPersonId {get;组; } 
 public Person SalesPerson {get;组; 
} 
 
 public class供应商：公司
 {
 public string SupplierName {get;组; } 
} 
 
 public class Address 
 {
 public int TenantId {get;组; } 
 public int AddressId {get;组; } 
 
 public string City {get;组; } 
} 
 
 public class Person 
 {
 public int TenantId {get;组; } 
 public int PersonId {get;组; } 
 
 public string Name {get;组; } 
} 
 
 public class MyContext：DbContext 
 {
 public DbSet< Company>公司{get;组; } 
 public DbSet< Address>地址{get;组; } 
 public DbSet< Person>人{get;组; 
 
 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 =>新{c.TenantId，c.AddressId}）; 
 
 modelBuilder.Entity< Customer>（）
 .ToTable（Customers）; 
 
 //以下映射不起作用，并导致异常
 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}）; 
} 
} 
 
类程序
 {
 static void Main（string [] args）
 {
 Database.SetInitializer （新的DropCreateDatabaseAlways< MyContext>（））; 
使用（var ctx = new MyContext（））
 {
 try 
 {
 ctx.Database.Initialize（true）; 
} 
 catch（异常e）
 {
 throw; 
} 
} 
} 
} 
} 
  

问题：有没有办法将上面的数据库模式映射到具有Entity Framework的类模型？

解决方案

嗯，我似乎没有对任何内容发表评论，所以我把它作为一个答案。

我在CodePlex上为此问题创建了一个问题，所以希望他们能尽快研究。敬请关注！

http：//entityframework.codeplex .com / workitem / 865

---

CodePlex发行的结果（已在同时）这个问题的方案是不支持的，目前还没有计划在不久的将来支持它。

来自CodePlex的实体框架团队的报价：

这是一个更基本的限制的一部分，其中EF不支持
具有基类型定义的属性，在派生类型中使用它作为
外键。不幸的是，这是一个限制，
将很难从我们的代码库中删除。鉴于我们没有
看到很多请求，在这个阶段我们计划不要
的地址，所以我们正在关闭这个问题。

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

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

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; }
}

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

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

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

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

But when EF tries to compile the model an InvalidOperationException is thrown:

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:

• 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:

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

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

I see two workarounds:

• Introduce a SalesPersonTenantId into the Customer class:

  public class Customer : Company
  {
      public string CustomerName { get; set; }
  
      public int SalesPersonTenantId { get; set; }
      public int SalesPersonId { get; set; }
      public Person SalesPerson { get; set; }
  }
  

  and the mapping:

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

  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.

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

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;
                }
            }
        }
    }
}

Question: Is there any way to map the database schema above to a class model with Entity Framework?

解决方案

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

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

http://entityframework.codeplex.com/workitem/865

---

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.

Quote from the Entity Framework team at CodePlex:

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屋！