如何创建查找表并定义关系 [英] How to create Lookup table and define relationships

问题描述

正如您在下面看到的，有一个枚举值的查找表，我想在表的枚举值和查找表的 LookupKey 列(而不是 ID 列)之间创建关系查找表).

As you can see at below, there are a Lookup table for the enum values and I want to create a relationship between a table's enum values and LookupKey column of the Lookup table (instead of ID column of the Lookup table).

查找表:

ID   | LookupType | LookupKey | LookupValue |
101  | Status     | 0         | Passive     | 
106  | Gender     | 1         | Male        | 
113  | Status     | 1         | Active      | 
114  | Gender     | 2         | Female      | 
118  | Status     | 2         | Cancelled   | 

主表:

ID | Status     | Gender    | Name              | ...
1  | 0          | 1         | John Smith        | ...
2  | 1          | 2         | Christof Jahnsen  | ...
3  | 2          | 1         | Alexi Tenesis     | ...
4  | 0          | 2         | Jurgen Fechtner   | ...
5  | 1          | 2         | Andreas Folk      | ...

但是，当在 DataAnnotations - InverseProperty Attribute 关系是使用 Lookup 表的 ID 列创建的，我无法与 LookupKey 列建立关系.你能举例说明如何实现这一目标吗?

However, when using PK-FK relation and InverseProperty as on DataAnnotations - InverseProperty Attribute the relation is created with the ID column of the Lookup table and I cannot make the relation to the LookupKey column. Could you give an example how to achieve this?

推荐答案

我们这里有一个通用的查找表.它看起来和你的很相似.LookupData 具有 LookupTypes 的主键和外键，相当于您的枚举和值.我们可能还有其他一些简单的字段，例如在 LookupType 元数据表中标识的标志或代码.然后在主表中，我们可能有指向 LookupData.Id 字段的GenderLookupId".ID 本身没有任何意义，可以按任意顺序输入.如果您希望性别 1 和 2 有意义，您可能应该为此添加另一个属性(请参阅代理键).

We have a common lookup table here. It looks simlar to yours. LookupData has a primary key and a foreign key to LookupTypes which is equivalent to your enum and the value. We might also have some other simple fields like a flag or code which are identified in the LookupType metadata table. Then in out main table we might have "GenderLookupId" which points to the LookupData.Id field. The IDs themselves have no meaning and can be entered in any order. If you want gender 1 and 2 to have meaning, you should probably add another attribute for that (see surrogate keys).

数据示例:

查找类型

ID    Description    CodeDesc        BooleanDesc  
1     Genders        Gender Code     NULL
2     Races          Race Code       Is Active

查找数据

ID    LookupTypeId    Description    Code    Boolean
789   1               Male           M       NULL
790   2               White          W       True
791   1               Female         F       NULL
792   2               Hispanic       H       False

主名称表

NameId   Name          GenderLookupId   RaceLookupId
1234     Joe Smith     789              790
1235     Mary Meyers   791              792

类:

public class LookupType
{
    public int Id { get; set; }
    public string Description { get; set; }
    public string CodeDescription { get; set; }
    public string BooleanDescription { get; set; }

}

public class LookupData
{
    public int Id { get; set; }
    public int LookupTypeId { get; set; }
    public string Description { get; set; }
    public string Code { get; set; }
    public bool? BooleanValue { get; set; }
    public LookupType LookupType { get; set; } 

}

public class Name
{
    public int Id { get; set; }
    public string FullName { get; set; }
    public int? GenderLookupId { get; set; }
    public LookupData Gender { get; set; } 
}

查找数据配置:

HasRequired(p => p.LookupType).WithMany(p=>p.LookupData).HasForeignKey(p=>p.LookupTypeId).WillCascadeOnDelete(false);

名称配置:

HasOptional(p => p.Gender).WithMany(p=>p.Name).HasForeignKey(p=>p.GenderLookupId).WillCascadeOnDelete(false);

这篇关于如何创建查找表并定义关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！