EF每层次映射表 [英] EF Table-per-hierarchy mapping
问题描述
在尝试规范化数据库架构并将其映射到Entity Framework中时，我发现可能会出现一堆查找表。他们最终只会包含键值和值对。我想把它们整合成一个基本上有两个列Key和Value的表。例如，我希望能够将Addresses.AddressType和Person.Gender都指向同一个表，但是确保导航属性仅返回适用于相应实体的行。
In trying to normalize a database schema and mapping it in Entity Framework, I've found that there might end up being a bunch of lookup tables. They would end up only containing key and value pairs. I'd like to consolidate them into one table that basically has two columns "Key" and "Value". For example, I'd like to be able to get Addresses.AddressType and Person.Gender to both point to the same table, but ensure that the navigation properties only return the rows applicable to the appropriate entity.
编辑：糟糕。我只是意识到我已经离开了这个段落：
Oops. I just realized that I left this paragraph out:
这似乎是一个TPH类型的问题，但是我所做的所有阅读都表明你从父实体并将字段迁移到继承的子节点。我没有任何字段可以移动到这里，因为通常只有两个。
It seems like a TPH type of problem, but all of the reading I've done indicates that you start with fields in the parent entity and migrate fields over to the inherited children. I don't have any fields to move here because there would generally only be two.
需要表示很多特定于域的键值对。其中有些会不时改变，别人不会改变。而不是选择并选择我想要使所有可编辑的东西。由于将要使用的这些类型的属性的数量，我宁可不必维护需要重新编译的列表枚举，或者最终使用大量的查找表。所以，我以为这可能是一个解决方案。
There are a lot of domain-specific key-value pairs need to be represented. Some of them will change from time to time, others will not. Rather than pick and choose I want to just make everything editable. Due to the number of these kinds of properties that are going to be used, I'd rather not have to maintain a list enums that require a recompile, or end up with lots of lookup tables. So, I thought that this might be a solution.
有没有办法在EF4中代表这种结构？或者，我正在树上错误的树？
Is there a way to represent this kind of structure in EF4? Or, am I barking up the wrong tree?
编辑：我猜另一个选择是在数据库级别构建我想要的表结构，然后在顶部写入视图并将其作为EF实体。它只是意味着需要在多个层面进行维护。那么听起来比纯粹的EF解决方案听起来更像是不是那么喜欢吗？
I guess another option would be to build the table structure I want at the database level and then write views on top of that and surface those as EF entities. It just means any maintenance needs to be done at multiple levels. Does that sound more, or less desireable than a pure EF solution?
推荐答案
每个hiearchy的表要求你有一个父实体用作子实体的基类。所有实体都映射到同一个表，并且有特殊的标识符列来区分存储在数据库记录中的实体类型。您通常可以使用它，即使您的子实体没有定义任何新的属性。您还必须为表定义主键，否则它将作为EF中的只读实体处理。因此，您的表格可能如下所示：
Table per hiearchy demands that you have one parent entity which is used as base class for child entities. All entities are mapped to the same table and there is special discriminator column to differ type of entity stored in database record. You can generally use it even if your child entities do not define any new properties. You will also have to define primary key for your table otherwise it will be handled as readonly entity in EF. So your table can look like:
CREATE TABLE KeyValuePairs
(
Id INT NOT NULL IDENTITY(1,1),
Key VARCHAR(50) NOT NULL,
Value NVARCHAR(255) NOT NULL,
Discriminator VARCHAR(10) NOT NULL,
Timestamp Timestamp NOT NULL
)
您将使用属性Id，Key，Value和Timestamp定义顶级的KeyValuePair实体设置为并发模式固定）。 Discriminator列将用于继承映射。
You will define your top level KeyValuePair entity with properties Id, Key, Value and Timestamp (set as concurrency mode fixed). Discriminator column will be used for inheritance mapping.
请注意，EF映射是静态的。如果您定义了AddressType和Gender实体，您将能够使用它们，但是您将无法动态定义新的类型，如PhoneType。这将始终需要修改您的EF模型，重新编译和重新部署您的应用程序。
Be aware that EF mapping is static. If you define AddressType and Gender entities you will be able to use them but you will not be able to dynamically define new type like PhoneType. This will always require modifying your EF model, recompiling and redeploying your application.
从OOP的角度来看，不要将此模型作为对象hiearchy更好，而是使用条件映射多个不相关的实体到同一个表。不幸的是，EF支持条件映射，我从来没有能够将两个实体映射到同一个表。
From OOP perspective it would be nicer to not model this as object hiearchy and instead use conditional mapping of multiple unrelated entities to the same table. Unfortunatelly even EF supports conditional mapping I have never been able to map two entities to the same table yet.
这篇关于EF每层次映射表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
