如何创建查找表并定义关系

本文介绍了如何创建查找表并定义关系的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

正如您在下面看到的，有一个枚举值的查找表，我想在表的枚举值和查找表的 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);