设计背景:
我正在尝试为以下数据库结构创建代码优先的EF6映射:
数据库设计如下: 我们没有像所有相关实体（就业、支出、收入等）上都有“CustomerID”作为外键，而是有一个CustomerRelationship表，其中将包含CustomerID，然后是一个“RelatedID”列，其中将包含相关实体的关键字。例如，假设我为CustomerID=1添加了一条就业记录，那么将发生以下情况：
在CustomerRelationship中创建记录，设置CustomerID = 1 RelatedID = {新自动生成的EmploymentID，比如5} CustomerRelationshipTypeID = 55 (查找表中的ID，指示此记录是就业类型)
在Employment表中创建记录(EmploymentID=5)
上述结构适用于与客户相关的所有实体。
对于Employment，我的关系映射已经起作用了，这是我的类:
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 匹配且 CustomerRelationshipTypeID = 55（Employments 的关键值）的记录。请参见下面的返回结果。
当我尝试连接另一个实体类型，比如：Expense，按照与Employment相同的方法进行操作，创建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);
}
}
一旦我创建了地图条目（如上所示），当查询GetEmployments（）方法时，我现在会得到以下异常：“实体类型'ExpenseRelationship'和'EmploymentRelationship'不能共享表'tblCustomerRelationship'，因为它们不在同一类型层次结构中或它们之间没有有效的一对一外键关系，这些关系具有匹配的主键。” 我错过了什么？
更新：根据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();
我得到了以下异常：
“外键组件'RelatedId'在类型'EmploymentRelationship'上未声明为属性。请验证它没有被明确地从模型中排除，并且它是一个有效的基元属性。”