如何创建查找表和定义关系 [英] 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      | ...

然而，使用PK-FK关系，InverseProperty作为当<一href=\"http://www.entityframeworktutorial.net/$c$c-first/inverseproperty-dataannotations-attribute-in-$c$c-first.aspx\"相对=nofollow> DataAnnotations - InverseProperty属性的关系与查找表的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?

推荐答案

在这里，我们有一个共同的查找表。它看起来simlar到你。 LookupData具有主键和一个外键LookupTypes相当于您的枚举和的值。我们可能还有一些其它的简单字段，比如标志或code这是在LookupType元数据表标识。然后在出主表，我们可能有GenderLookupId，它指向LookupData.Id领域。的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).

数据示例：

LookupType

LookupType

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

LookupData

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

LookupData配置：

LookupData Config:

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