实体框架将两个表列映射到相同的表格Key [英] Entity Framework mapping two table columns to same related table Key

问题描述

我处于一个名为元素的表格中。现在我正在创建一个名为 Divergences 的表，它将基本上存储元素的对。 Divergence 的目的是检查两个元素是否有不同的答案。

I'm in a situation where I have a table called Elements. Now I'm creating a table called Divergences that will store basically pairs of Elements. The purpose of Divergence is to check if two Elements have diverging answers.

Element               Divergence
---------             ----------
ElementId             ElementId1
                      ElementId2

在上表中， ElementId1 ElementId2 是 映射到 ElementId 元素 code>表格，并为 Divergence 表格形成复合主键。

In the above table schema, ElementId1 and ElementId2 are Foreign Keys mapping to ElementId in Elements table and form the composite primary key for the Divergence table.

我使用数据库第一种方法我在SQL Server Management Studio中创建表，稍后我从数据库... 中执行更新模型实体框架设计器。

I use Database First approach where I create the tables in SQL Server Management Studio and later I do a Update Model from Database... in Entity Framework Designer.

我遇到的问题是，当EF Designer生成模型时，它会创建2集 ICollection< Element> 元素元素元素元素 s1 。

The problem I'm facing is that when EF Designer generates the model it creates 2 sets ICollection<Element> inside the Element class namely Elements and Elements1.

这不给我一个 Divergences DbSet。

This doesn't give me a Divergences DbSet.

我想要的是一个 Divergence 类，我会在代码中做这样的事情：

What I'd want to have is a Divergence class where I'd do something like this in code:

Divergence d = new Divergence();
d.Element1 = element1;
d.Element2 = element2;

Database.Divergences.Add(d);
Database.SaveChanges();

及以后：

Element e = Database.Elements.Single(e => e.ElementId == 7);

var divergences = e.Divergences;

我尝试向 Divergence 这样的表：

Element               Divergence
---------             ------------
ElementId             DivergenceId
                      ElementId1
                      ElementId2

这正确地导致 1< - > *实体框架设计器中的关系。我终于得到一个 Divergences DbSet，但$ code> DivergenceId 属性在代码中是无用的，我仍然得到2集在元素类。请注意， ElementId1 和 ElementId2 仍然是复合主键。

This correctly leads to a 1 <-> * relationship in Entity Framework Designer. I finally get a Divergences DbSet but DivergenceId property is useless in the code and I still get the 2 sets in the Element class. It's important to note that ElementId1 and ElementId2 still form the composite primary key.

你认为映射这种具体情况的正确方法是什么？感谢您的输入。

What do you think is the correct way of mapping this specific situation? Thanks for the input.

推荐答案

而不是...

Divergence d = new Divergence();
d.Element1 = element1;
d.Element2 = element2;

Database.Divergences.Add(d);
Database.SaveChanges();

...您实际可以使用：

... you could actually use:

element1.Elements = new List<Element>();
// if that collection isn't already instantiated, for example in the constructor
element1.Elements.Add(element2);

Database.SaveChanges();

这将创建完全相同的SQL INSERT 链接表的语句，而不需要有一个 Divergence 实体。 （更改跟踪将识别您通过向该集合添加项目并从该更改中推断出必要的SQL命令来更改关系。 element1 和 element2 必须附加到状态不变中的上下文，但是为了正常工作，您的原始代码也是必需的。）

This will create exactly the same SQL INSERT statements to the link table without the need of having a Divergence entity. (Change tracking will recognize that you changed the relationship by adding an item to the collection and infer the necessary SQL commands from this change. element1 and element2 must be attached to the context in state Unchanged, but that is also required for your original code in order to work correctly.)

另外，而不是...

Element e = Database.Elements.Single(e => e.ElementId == 7);
var divergences = e.Divergences;

...您可以从 Divergences 表如下：

... you can fetch the columns from the Divergences table like so:

var divergences = Database.Elements.Where(e => e.ElementId == 7)
    .SelectMany(e1 => e1.Elements.Select(e2 => new
    {
        ElementId1 = e1.ElementId,
        ElementId2 = e2.ElementId,
    }))
    .ToList();

所以，你会得到你想要的结果，而没有发散实体，老实说，我会这样使用它。我不知道有办法强制EF用数据库第一种方式创建该实体，除非您像您一样介绍一些人造附加列。如果有一种方法可能是一个黑客或更难以实现和维护，而不仅仅是使用EF的默认，那就是没有一个 Divergence 实体。

So, you will get your desired results without the Divergence entity and honestly I would use it this way. I'm not aware of a way to force EF to create that entity with database first approach, unless you introduce some artificial additional column like you did. If there is a way it is probably a hack or more difficult to achieve and maintain than just working with EF's default, that is without a Divergence entity.

但是，您可以考虑删除其中一个集合（只需在模型表面中删除它）。在我看来，这个模型有点混乱。或者至少将它们重命名为 SourceElements 和 TargetElements ，例如：）

However, you could consider to remove one of the collections (just delete it in the model surface). Having both is a bit confusing in this model in my opinion. Or at least rename them to SourceElements and TargetElements for example :)

这篇关于实体框架将两个表列映射到相同的表格Key的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！