c# - EF 6-代码优先无效的一对一外键关系

设计背景:

我正在尝试为以下数据库结构创建代码优先EF6映射:

数据库设计如下:我们没有在所有相关实体(就业，费用，收入等)上使用“CustomerID”作为外键，而是拥有一个CustomerRelationship表，其中将包含CustomerID，然后是一个“RelatedID” ”列，其中将包含相关实体的密钥。例如，假设我为CustomerID = 1添加了一个就业记录，那么将会发生以下情况:



上面的结构适用于与客户绑定(bind)的所有实体。

我有用于“就业”的关系映射，这是我的类(class):

public abstract class EntityBase : IEntity
{
    #region IEntity Members
    public int Id { get; set; }

    public DateTime CreatedDate { get; set; }

    public int CreatedUserId { get; set; }

    public int CreatedSource { get; set; }

    public DateTime ModifiedDate { get; set; }

    public int ModifiedUserId { get; set; }

    public int? DataMigrationId { get; set; }

    public bool IsActive { get; set; }
    #endregion
}


public class Employment : EntityBase
{
    // ... all properties here.. removed most so easier to read
    public int EmploymentTypeId { get; set; }

    **public virtual ICollection<EmploymentRelationship> EmploymentRelationships { get; set; }**
}

    public EmploymentMap()
    {
        this.HasKey(t => t.Id);
        ToTable("tblEmployment");
        Property(t => t.Id).HasColumnName("EmploymentID");
        // Mapping for all properties follow
    }

public abstract partial class CustomerRelationship : EntityBase
{
    public int CustomerId { get; set; }

    public decimal? PercentageShare { get; set; }

    public int CustomerRelationshipTypeId { get; set; }

    public int RelatedId { get; set; }
}

public class EmploymentRelationship : CustomerRelationship
{
    public virtual Employment Employment { get; set; }
}

    public EmploymentRelationshipMap()
    {
        this.HasKey(t => t.Id);

        Map<EmploymentRelationship>(m =>
        {
            m.Requires("CustomerRelationshipTypeID").HasValue(55).IsRequired(); // Define lookup value for type of employment
            m.ToTable("tblCustomerRelationship");
        });

        Property(t => t.Id).HasColumnName("CustomerRelationshipID");
        Property(t => t.CustomerId).HasColumnName("CustomerID");
        Property(t => t.RelatedId).HasColumnName("RelatedID");

        HasRequired(t => t.Employment)
            .WithMany(t => t.EmploymentRelationships)
            .HasForeignKey(t => t.RelatedId);
    }

public class Customer : EntityBase
{
    // Customer Properties...
    public Customer()
    {
        EmploymentRelationships = new List<EmploymentRelationship>();
    }

    public virtual ICollection<EmploymentRelationship> EmploymentRelationships { get; set; }
}

    public CustomerMap()
    {
        this.HasKey(t => t.Id);

        ToTable("tblCustomer");

        Property(t => t.Id).HasColumnName("CustomerID");
    }


public class CustomerContext
{
    public CustomerContext()
        : base(SymmetryCopy.context_connectionstring_main)
    {
    }

    public virtual DbSet<Customer> Customers { get; set; }
    public virtual DbSet<Employment> Employments { get; set; }

    #region Customer Relationship entity mappings
    public virtual DbSet<EmploymentRelationship> EmploymentRelationships { get; set; }
    #endregion

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new CustomerMap());
        modelBuilder.Configurations.Add(new EmploymentMap());

        #region Customer Relationship entity mappings
        modelBuilder.Configurations.Add(new EmploymentRelationshipMap());
        #endregion
    }
}

CustomerRepo以查询上下文并返回结果:

public class CustomerRepository : BaseRepository<Customer, CustomerContext>, ICustomerRepository
{
    public CustomerRepository() :
        base(new CustomerContext())
    {

    }

    public async Task<List<Employment>> GetEmployments(int customerId)
    {
        List<Employment> employments = new List<Employment>();
        using (var context = new CustomerContext())
        {
            var employmentRelationships = context.EmploymentRelationships.Where(l => l.CustomerId == customerId).ToList();
            employments = employmentRelationships.Select(x => x.Employment).ToList();
        }
        return employments;
    }
}

然后，上述方法 GetEmployments 返回所有与CustomerID匹配且CustomerCustomerlationshipTypeID = 55(就业关键值)的记录。请参阅下面的退货。



现在来看我的实际问题:

当我尝试连接另一个实体类型(即:Expense)时，采用与雇佣相同的方法，创建Expense.cs，ExpenseMap.cs，ExpenseRelationship.cs，ExpenseRelationshipMap.cs，并在ExpenseRElationshipMap.cs中具有以下内容:

public class ExpenseRelationshipMap
{
    public ExpenseRelationshipMap()
    {
        HasKey(t => t.Id);

        Map<ExpenseRelationship>(m =>
        {
            m.Requires("CustomerRelationshipTypeID").HasValue(60).IsRequired();
            m.ToTable("tblCustomerRelationship");  // Define lookup value for type of Expense
        });

        Property(t => t.Id).HasColumnName("CustomerRelationshipID");
        Property(t => t.CustomerId).HasColumnName("CustomerID");
        Property(t => t.RelatedId).HasColumnName("RelatedID");
        Property(t => t.PercentageShare).HasColumnName("PercentageShare");

        HasRequired(t => t.Expense)
            .WithMany(t => t.ExpenseRelationships)
            .HasForeignKey(t => t.RelatedId);
    }
}

如上所示，一旦创建了Map条目，当查询GetEmployments()方法时，我现在得到以下异常:



我缺少什么？

更新

根据jjj的评论，我更新了映射并创建了CustomerRelationship.cs基类。

public class Employment : EntityBase
{
    public string EmployerName { get; set; }

    public string EmployerContactFirstName { get; set; }

    public string EmployerContactSurname { get; set; }

    public virtual ICollection<EmploymentRelationship> EmploymentRelationships { get; set; }
}

public class Expense : EntityBase
{
    public string Description { get; set; }

    public virtual ICollection<ExpenseRelationship> ExpenseRelationships { get; set; }
}

public abstract class CustomerRelationship : EntityBase
{
    public int CustomerId { get; set; }

    public int? CustomerRelationshipTypeId { get; set; }

    public int RelatedId { get; set; }
}

public class EmploymentRelationship : CustomerRelationship
{
    public virtual Employment Employment { get; set; }
}

public class ExpenseRelationship: CustomerRelationship
{
    public virtual Expense Expense{ get; set; }
}

public class CustomerRelationshipMap : BaseMap<CustomerRelationship>
{
    public CustomerRelationshipMap()
    {
        ToTable("CustomerRelationship");

        Map<EmploymentRelationship>(m => m.Requires("CustomerRelationshipTypeID").HasValue(55));
        Map<ExpenseRelationship>(m => m.Requires("CustomerRelationshipTypeID").HasValue(60));

        Property(t => t.Id).HasColumnName("CustomerRelationshipID");
        Property(t => t.CustomerId).HasColumnName("CustomerID");
        Property(t => t.RelatedId).HasColumnName("RelatedID");
    }

public class EmploymentRelationshipMap : BaseMap<EmploymentRelationship>
{
    public EmploymentRelationshipMap()
    {
        HasRequired(t => t.Employment)
            .WithMany(t => t.EmploymentRelationships)
            .HasForeignKey(t => t.RelatedId);
    }
}

public class ExpenseRelationshipMap : BaseMap<ExpenseRelationship>
{
    public ExpenseRelationshipMap()
    {
        HasRequired(t => t.Expense)
            .WithMany(t => t.ExpenseRelationships)
            .HasForeignKey(t => t.RelatedId);
    }
}

public class CustomerContext : BaseContext
{
    public CustomerContext()
        : base(context_connectionstring_main)
    {
    }

    public virtual DbSet<Customer> Customers { get; set; }
    public virtual DbSet<Employment> Employments { get; set; }

    public virtual DbSet<CustomerRelationship> CustomerRelationships { get; set; }
    public virtual DbSet<EmploymentRelationship> EmploymentRelationships { get; set; }
    public virtual DbSet<ExpenseRelationship> ExpenseRelationships { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new CustomerMap());
        modelBuilder.Configurations.Add(new EmploymentMap());

        modelBuilder.Configurations.Add(new CustomerRelationshipMap());
        modelBuilder.Configurations.Add(new EmploymentRelationshipMap());
        modelBuilder.Configurations.Add(new ExpenseRelationshipMap());
    }
}

当我这样查询客户上下文时:

var relationships = context.CustomerRelationships.Where(l => l.CustomerId == customerId).ToList();

我得到以下异常:

最佳答案

您需要所有共享属性(包括主键)的基类配置。

public class CustomerRelationshipMap : EntityTypeConfiguration<CustomerRelationship>
{
    public CustomerRelationshipMap()
    {
        ToTable("tblCustomerRelationship");

        Map<EmploymentRelationship>(m => m.Requires("CustomerRelationshipTypeID").HasValue(55));
        Map<ExpenseRelationship>(m => m.Requires("CustomerRelationshipTypeID").HasValue(60));

        HasKey(t => t.Id);
        Property(t => t.Id).HasColumnName("CustomerRelationshipID");
        Property(t => t.CustomerId).HasColumnName("CustomerID");
        Property(t => t.RelatedId).HasColumnName("RelatedID");
    }
}

然后，您应该能够在其他配置类中具有特定于派生类的配置(尽管这不是我之前尝试过的)。

编辑

同样，对于使用相同基类属性的派生类，您不能具有不同的外键关联。我可以想到几种选择，但这取决于您的情况:

用于EmploymentRelationship-Employment和ExpenseRelationship-Expense之间的关联的独立外键。

也给Employment和Expense一个通用的基类-尽管这可能会破坏您要尝试执行的操作的目的。...

CustomerRelationship和Employment / Expense之间的1:0..1关系(以及摆脱EmploymentRelationship和ExpenseRelationship的关系)

TPT继承，其中Employment和Expense从CustomerRelationship继承(并摆脱EmploymentRelationship和ExpenseRelationship)

来源

Difficulty Concerning EF Code First Fluent API, TPH, and Foreign Keys

How to map foreign keys between TPH TPT objects - Entity Framework Code First

关于c# - EF 6-代码优先无效的一对一外键关系，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30294728/