建模多态关联数据库优先与代码优先 [英] Modelling polymorphic associations database-first vs code-first

问题描述

我们有一个数据库，其中一个表包含可以是其他几个表的子对象的记录。它具有由所有者的Id和表名组成的软外键。这种（反）模式被称为多态关联。我们知道这不是最好的数据库设计，我们将在适时的时间内进行更改，但不会在不久的将来进行更改。让我来看一个简单的例子：

事件， Person code>产品在评论中有记录。如您所见，没有困难的FK约束。

在实体框架中，可以通过子类注释进入 EventComment 等，让事件有一个 EventComments 收藏等：

从数据库生成基本模型后，将手动添加子类和关联。 OwnerCode 是此 TPH 模型中的鉴别器。请注意，事件，人和产品完全不同的实体。为他们共同的基类没有意义。

这是数据库优先。我们的现实生活中的模特就是这样，没有问题。

好的。现在我们要移动到第一代码。所以我开始将数据库逆向工程化为代码第一模型（EF Power Tools），并继续创建子类并映射关联和继承。试图连接到Linqpad中的模型。这是麻烦开始的时候。

当尝试使用此模型执行查询时，它会抛出一个 InvalidOperationExeception

外键组件OwnerId不是EventComment类型上声明的属性。验证它没有被明确排除在模型中，并且它是一个有效的原始属性。

当我有双向关联时，会发生这种情况和 OwnerId 映射为注释中的属性。我的 EventMap 类（ EntityTypeConfiguration< Event> ）中的映射如下所示：

  this.HasMany（x => x.Comments）.WithRequired（c => c.Event）
 .HasForeignKey（c => c.OwnerId）; 
  

所以我试图映射没有 OwnerId 在模型中：

  this.HasMany（x => x.Comments）.WithRequired（）。Map（m = > m.MapKey（OwnerId））; 
  

这将抛出一个 MetaDataException

指定的模式无效。错误：
（10,6）：错误0019：类型中的每个属性名称必须是唯一的。属性名称OwnerId已被定义。
（11,6）：错误0019：类型中的每个属性名称必须是唯一的。属性名称OwnerId已被定义。

如果我删除了三个实体 - 注释关联中的两个，那么它当然可以这不是一个治疗方法。

一些更详细的信息：

• 通过添加DbContext生成器项从edmx创建一个工作的DbContext模型（code second）。 （这将是现在的一个解决方案）。


• 当我将工作的代码优先模型（一个关联）导出到edmx（ EdmxWriter ）该关联似乎在存储模型中，而在原始edmx中它们是概念模型的一部分。

那么，我该如何创建这个模型代码？我认为关键是如何指导代码优先在概念模型中映射关联，而不是存储模型。

解决方案

在任何一种复杂程度的模式下使用EF时，我个人都坚持使用数据库。我在代码优先方面遇到了复杂模式的问题。也许较新的版本好一点，但担心如何尝试和编码复杂的关系似乎不太直接，然后允许引擎为您生成。此外，当关系变得复杂时，我倾向于避免尝试使用EF生成它，并尝试使用存储过程来更容易地排除可能出现的性能瓶颈。

We have a database in which one table contains records that can be child to several other tables. It has a "soft" foreign key consisting of the owner's Id and a table name. This (anti) pattern is know as "polymorphic associations". We know it's not the best database design ever and we will change it in due time, but not in the near future. Let me show a simplified example:

Both Event, Person, and Product have records in Comment. As you see, there are no hard FK constraints.

In Entity Framework it is possible to support this model by sublassing Comment into EventComment etc. and let Event have an EventComments collection, etc.:

The subclasses and the associations are added manually after generating the basic model from the database. OwnerCode is the discriminator in this TPH model. Please note that Event, Person, and Product are completely different entities. It does not make sense to have a common base class for them.

This is database-first. Our real-life model works like this, no problem.

OK. Now we want to move to code-first. So I started out reverse-engineering the database into a code first model (EF Power Tools) and went on creating the subclasses and mapping the associations and inheritance. Tried to connect to the model in Linqpad. That's when the trouble started.

When trying to execute a query with this model it throws an InvalidOperationExeception

The foreign key component 'OwnerId' is not a declared property on type 'EventComment'. Verify that it has not been explicitly excluded from the model and that it is a valid primitive property.

This happens when I have bidirectional associations and OwnerId is mapped as a property in Comment. The mapping in my EventMap class (EntityTypeConfiguration<Event>) looks like this:

this.HasMany(x => x.Comments).WithRequired(c => c.Event)
    .HasForeignKey(c => c.OwnerId);

So I tried to map the association without OwnerId in the model:

this.HasMany(x => x.Comments).WithRequired().Map(m => m.MapKey("OwnerId"));

This throws a MetaDataException

Schema specified is not valid. Errors: (10,6) : error 0019: Each property name in a type must be unique. Property name 'OwnerId' was already defined. (11,6) : error 0019: Each property name in a type must be unique. Property name 'OwnerId' was already defined.

If I remove two of the three entity-comment associations it is OK, but of course that's not a cure.

Some further details:

• It is possible to create a working DbContext model ("code second") from the edmx by adding a DbContext generator item. (this would be a work-around for the time being).
• When I export the working code-first model (with one association) to edmx (EdmxWriter) the association appears to be in the storage model, whereas in the original edmx they are part of the conceptual model.

So, how can I create this model code-first? I think the key is how to instruct code-first to map the associations in the conceptual model, not the storage model.

解决方案

I personally stick with Database first when using EF on any schema that is this level of complexity. I have had issues with complex schemas in regards to code first. Maybe the newer versions are a little better, but worrying how to try and code complex relationships seems less straight forward then allowing the engine to generate it for you. Also when a relationship gets this complex I tend to avoid trying to generate it with EF and try and use stored procedures for easier troubleshooting of performance bottlenecks that can arise.

这篇关于建模多态关联数据库优先与代码优先的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！