外键依赖于另一个外键作为其父键 [英] Foreign Key dependency on another Foreign Key as it's parent key

问题描述

我正在创建一个用作其他项目基础的库，有关该项目的详细信息，请参见在此，可以在中找到nuget包在这里

I am creating a library which works as a base for other projects, details of the project can be found here and the nuget package can be found here

由于这是基础库，我希望允许用户扩展模型，因此我在创建接口而不是具体的库（显然，默认实现是在那里，但是这超出了当前问题的范围），但是由于实体框架只能使用具体的类才能完成导航/外键，因此我正在研究通用接口的路线，它工作得很好，但是我已经达到了一个要点在这里我想基于同一张表中的另一个外键来限制外键。

Since this is a base library and I want to allow the users to extend models so I am creating interfaces instead of concrete libraries (default implementation is obviously there, but that is out of scope of current question), but since entity framework can accomplish navigation/foreign keys only with concrete class, I am going through the route of generic interfaces, it is working pretty well, but I have reached a point where I want to restrict the foreign key based on another foreign key in the same table.

在搜索答案时，这个问题最接近我的要求

While searching for the answers this question came closest to my requirement

SQL：外部是另一个外键的子键-堆栈溢出

那里提供的解决方案说，从技术上讲，您应该删除父键，因为子表已经具有可用于与父表关联的父键，我完全同意，但是在我的情况下这是不可能的，因为它会导致循环依赖地狱，这应该避免软件项目不惜一切代价。

The solution provided there says that technically you should remove the parent key as the child table already has parent key which can be used to associate to the parent table and I agree with it completely, however in my case that's not possible as it will lead to cyclic dependency hell which is something should be kept away from software projects at all costs.

IO界面

public interface IOrganization<TLinkedAddress> where TLinkedAddress : ILinkedAddress
{
    long ID { get; set; }
    string Name { get; set; }
    string Brand { get; set; }
    string Genre { get; set; }
    string Industry { get; set; }
    string Master_Security_Stamp { get; set; }
    long? Control_Branch_ID { get; set; }

    [ForeignKey("Control_Branch_ID")]
    TLinkedAddress Control_Branch { get; set; }

    IList<TLinkedAddress> Branches { get; set; }
}

ILinkedAddress接口

public interface ILinkedAddress
{
    long ID { get; set; }
    string Address { get; set; }
    long LocationID { get; set; }
    string AddressType { get; set; }
    string Contact_Person_Name { get; set; }
    string Contact_Person_Number { get; set; }
    string Contact_Person_Relation { get; set; }
    [ForeignKey("OrganizationID")]
    long? OrganizationID { get; set; }
    [ForeignKey("UserID")]
    long? UserID { get; set; }
}

教育界面

public interface IEducation<TUser, TOrganization, TLinkedAddress>
    where TUser : IUser
    where TOrganization : IOrganization<TLinkedAddress>
    where TLinkedAddress : ILinkedAddress
{
    long ID { get; set; }
    long UserID { get; set; }
    long InstituteID { get; set; }
    long InstituteLocationID { get; set; }
    string Degree { get; set; }
    string Major { get; set; }
    string Grade { get; set; }
    DateTime StartDate { get; set; }
    DateTime? EndDate { get; set; }
    string Socities { get; set; }
    string Description { get; set; }

    [ForeignKey(name: "UserID")]
    TUser User { get; set; }
    [ForeignKey(name: "InstituteID")]
    TOrganization Institute { get; set; }
    [ForeignKey(name: "InstituteLocationID")]
    TLinkedAddress InstituteLocation { get; set; }
}

默认实现

Default implementations

LinkedAddress.cs

[Table(name: "LinkedAddress", Schema = "Arinsys_CRM")]
public class LinkedAddress : DBEntity<LinkedAddress>, ILinkedAddress
{
    public long ID { get; set; }
    public string Address { get; set; }
    public long LocationID { get; set; }
    public string AddressType { get; set; }
    public string Contact_Person_Name { get; set; }
    public string Contact_Person_Number { get; set; }
    public string Contact_Person_Relation { get; set; }
    [ForeignKey("OrganizationID")]
    public long? OrganizationID { get; set; }
    [ForeignKey("UserID")]
    public long? UserID { get; set; }
}

Organization.cs

[Table(name: "Organization", Schema = "Arinsys_CRM")]
public partial class Organization : DataContext.DBEntity<Organization>,
    IOrganization<LinkedAddress>
{
    public long ID { get; set; }
    public string Name { get; set; }
    public string Brand { get; set; }
    public string Genre { get; set; }
    public string Industry { get; set; }
    public string Master_Security_Stamp { get; set; }
    public long? Control_Branch_ID { get; set; }

    [ForeignKey("Control_Branch_ID")]
    public virtual LinkedAddress Control_Branch { get; set; }

    public virtual IList<LinkedAddress> Branches { get; set; }
}

Education.cs

[Table(name: "Education", Schema = "Arinsys_CRM")]
public class Education : IEducation<User, Organization, LinkedAddress>
{
    public long ID { get; set; }
    public long UserID { get; set; }
    public long InstituteID { get; set; }
    public long InstituteLocationID { get; set; }
    public string Degree { get; set; }
    public string Major { get; set; }
    public string Grade { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime? EndDate { get; set; }
    public string Socities { get; set; }
    public string Description { get; set; }

    [ForeignKey(name: "UserID")]
    public virtual User User { get; set; }
    [ForeignKey(name: "InstituteID")]
    public virtual Organization Institute { get; set; }
    [ForeignKey(name: "InstituteLocationID")]
    public virtual LinkedAddress InstituteLocation { get; set; }
}

现在的问题是，是否可以对<$ IEducation 界面中的c $ c> InstitueLocationID ，它必须是 Organization 定义的分支机构通过 InstituteID 或我别无选择，只能让最终开发人员自己施加约束

Now the question is that is it possible to put a constraint on InstitueLocationID in IEducation interface that it must be a branch of Organization defined by InstituteID or I would have no option other than to leave on the end developer to put a constraint himself

编辑：进一步研究

我开始研究@Ivan Starostin提出的传递依存关系，并遇到了@Jeff Atwood（Co -Stack Overflow的创始人和联合创始人）介绍了他们在2008年遇到的Stack Overflow问题。

I started researching on transitive dependencies as suggested by @Ivan Starostin and came across this awesome blog post by @Jeff Atwood (Co-Founder (Emeritus) and co-creator of Stack Overflow) about the problems they faced with Stack Overflow in 2008

也许规范化是不正常的

博客文章的摘要是规范化不是灵丹妙药，有时非规范化数据会我实际上提高了生产力和性能，尽管该博客文章并未回答确切的问题，但它帮助我获得了更深入的了解，因此请在此处发布以供将来的读者使用。

Summary of the blog post is that normalization is not a silver bullet, and sometimes denormalized data will actually result in better productivity and performance, though that blog post didn't answered the exact question, it helped me gain a deeper understanding, so posting here for future readers.

推荐答案

现在的问题是，是否有可能在IEducation接口中对
InstitueLocationID施加约束，使其必须是
的分支由InstituteID定义的组织

Now the question is that is it possible to put a constraint on InstitueLocationID in IEducation interface that it must be a branch of Organization defined by InstituteID

EF EF对接口一无所知，但只关心类。实际上不可能指定暗示接口的配置。 Fluent API和数据注释配置仅适用于类。您不能为从 IEducation 派生的所有类创建约束，而只能在实现此接口的类上创建约束。

EF don't know anything about interfaces but it only care about on classes. It is not actually possible to specify configurations that implies interfaces. Fluent API and Data Annotation configuration only work with classes. You can't create constraint for all classes that derive from IEducation but only on classes that implement this interface.

我别无选择，只能让最终开发者自己施加
a约束

I would have no option other than to leave on the end developer to put a constraint himself

以ASP.Net Identity为例。为了使用具有零配置的此API，Microsoft为我们可能需要的每个类提供了默认实现： IdentityUser ，角色等等...这些默认类实现了 IUser ， IRole 等...

Take a example to ASP.Net Identity. To work with this API with zero configuration Microsoft give us default implementations for each class that we might need: IdentityUser, Role etc... Those default classes implement IUser, IRole etc...

如果默认实现不能满足您的需求，则有两个选择：

If default implementations don't satisfy your needs you have two options:

• 创建从默认实现派生的新类


• 创建新类，这些新类实现API提供的接口（由默认类实现）

如果选择后一个选项，EF配置将留给您。您必须对所有配置进行编码（使用Fluent API或数据注释），以使其他类可以与新类一起使用。我的意思是实现 IUSerStore ， IUserPasswordStore ， IUserLoginStore 等的类。 ..如果您甚至不想使用EF，则可以使用此API来实现所有 I * Store 接口等...

If you choose the latter option, the EF configurations are left to you. You must code all configurations (using Fluent API or Data Annotations) to make other classes to work with your new classes. I mean classes implementing IUSerStore, IUserPasswordStore, IUserLoginStore etc... If you don't even want to use EF this API let you implement All I*Store interfaces etc...

我的答案是为所有接口创建默认实现，并对这些类施加约束。如果开发人员想要扩展您的API，则他可以继承您的默认类，或者可以通过实现API接口来创建新类，并且必须创建使他的新类与其他API正确配合所需的所有约束。

My answer is to create a default implementation of all your interfaces and put constraints on those classes. If a developper want to extend your API he can subclass your default classes or he can create new classes by implementing the API interfaces and must create all constraints needed for the his new classes to work correctly with the rest of your API.

这篇关于外键依赖于另一个外键作为其父键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！