visual-studio 定义实体框架 1:1 关联

Defining an Entity Framework 1:1 association

提问by Craig Fisher

I'm trying to define a 1:1 association between two entities (one maps to a table and the other to a view - using DefinedQuery) in an Entity Framework model. When trying to define the mapping for this in the designer, it makes me choose the (1) table or view to map the association to. What am I supposed to choose? I can choose either of the two tables but then I am forced to choose a column from that table (or view) for each end of the relationship. I would expect to be able to choose a column from one table for one end of the association and a column from the other table for the other end of the association, but there's no way to do this.

我试图在实体框架模型中定义两个实体（一个映射到表，另一个映射到视图 - 使用 DefinedQuery）之间的 1:1 关联。当试图在设计器中为此定义映射时，它让我选择 (1) 表或视图来映射关联。我应该选择什么？我可以选择两个表中的任何一个，但随后我不得不为关系的每一端从该表（或视图）中选择一列。我希望能够从一个表中为关联的一端选择一列，从另一个表中为关联的另一端选择一列，但没有办法做到这一点。

Here I've chosen to map to the "DW_ WF_ClaimInfo" view and it is forcing me to choose two columns from that view - one for each end of the relationship.

在这里，我选择映射到“DW_WF_ClaimInfo”视图，这迫使我从该视图中选择两列 - 关系的每一端各一列。

I've also tried defining the mapping manually in the XML as follows:

我还尝试在 XML 中手动定义映射，如下所示：

<AssociationSetMapping Name="Entity1Entity2" TypeName="ClaimsModel.Entity1Entity2"
     StoreEntitySet="Entity1">
  <EndProperty Name="Entity2">
    <ScalarProperty Name="DOCUMENT" ColumnName="DOCUMENT" />
  </EndProperty>
  <EndProperty Name="Entity1">
    <ScalarProperty Name="PK_DocumentId" ColumnName="PK_DocumentId" />
  </EndProperty>
</AssociationSetMapping>

But this gives: Error 2010: The Column 'DOCUMENT' specified as part of this MSL does not exist in MetadataWorkspace. Seems like it still expects both columns to come from the same table, which doesn't make sense to me.

但这给出了：错误 2010：元数据工作区中不存在指定为此 MSL 一部分的列“DOCUMENT”。似乎它仍然希望两列都来自同一个表，这对我来说没有意义。

Furthermore, if I select the same key for each end, e.g.:

此外，如果我为每一端选择相同的键，例如：

<AssociationSetMapping Name="Entity1Entity2" TypeName="ClaimsModel.Entity1Entity2"
     StoreEntitySet="Entity1">
  <EndProperty Name="Entity2">
    <ScalarProperty Name="DOCUMENT" ColumnName="PK_DocumentId" />
  </EndProperty>
  <EndProperty Name="Entity1">
    <ScalarProperty Name="PK_DocumentId" ColumnName="PK_DocumentId" />
  </EndProperty>
</AssociationSetMapping>

I then get:

然后我得到：

Error 3021: Problem in Mapping Fragment starting at line 675: Each of the following
columns in table AssignedClaims is mapped to multiple conceptual side properties:
  AssignedClaims.PK_DocumentId is mapped to
    <AssignedClaimDW_WF_ClaimInfo.DW_WF_ClaimInfo.DOCUMENT,
    AssignedClaimDW_WF_ClaimInfo.AssignedClaim.PK_DocumentId>

What am I not getting?

我没有得到什么？

回答by driAn

You have to select the table, that cotains the foreign key. The next step is to remove that foreign key from the entity, as it is already expressed using the relation you just created.

您必须选择包含外键的表。下一步是从实体中删除该外键，因为它已经使用您刚刚创建的关系表达了​​。

Example:

例子：

table A
-------
A_ID int
B_ID int


table B
-------
B_ID int

In this case, you would select the table A in the designer, as it contains the foreign key. Also you'll need to remove B_ID from the A entity afterwards.

在这种情况下，您将在设计器中选择表 A，因为它包含外键。之后您还需要从 A 实体中删除 B_ID 。

回答by driAn

I was dealing with a legacy app so adding an additional primary key was not an option for me. What I had to do was map it as a 1:(0 or 1) rather than a 1:1 to get it to work. For example if I had two tables say Customer and CustomerDetails that both had a primary key called CustomerID. To create the association, I had to set it up as 1 Customer can relate to (0 or 1) CustomerDetails records. Eveytime you Insert a Customer, make sure to also insert the CustomerDetails so you can maintain the 1:1.

我正在处理一个遗留应用程序，所以添加一个额外的主键对我来说不是一个选择。我必须做的是将其映射为 1:(0 或 1) 而不是 1:1 以使其工作。例如，如果我有两个表说 Customer 和 CustomerDetails，它们都有一个称为 CustomerID 的主键。要创建关联，我必须将其设置为 1 个客户可以关联到（0 或 1）个 CustomerDetails 记录。每次插入客户时，请确保同时插入 CustomerDetails，以便保持 1:1。

回答by Watki02

I agree that the way this is set up seems counter-intuitive. For anyone that is too slow (like me) to get driAn's answer: Per the forum post below, I remembered that we are dealing with an entity, not a table directly. The association is on the entity(which could model the table, but doesn't have to).

我同意这种设置方式似乎违反直觉。对于速度太慢而无法得到 driAn 答案的任何人（如我）：根据下面的论坛帖子，我记得我们正在处理一个实体，而不是直接处理一个表。关联位于实体上（可以为表建模，但不是必须的）。

http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/2823634f-9dd1-4547-93b5-17bb8a882ac2/

http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/2823634f-9dd1-4547-93b5-17bb8a882ac2/

The entity framework is associating PROPERTIESto TABLE COLUMNS. That is why we must delete the "foreign key" PROPERTYon the ENTITY(note: we are not deleting the table column). To reference the column in code, (using the ENTITYmodelingthe given example of TABLE A & TABLE B) you would write something like this:

实体框架将PROPERTIES与TABLE COLUMNS相关联。这就是为什么我们必须删除ENTITY上的“外键” PROPERTY（注意：我们不会删除表列）。要在代码中引用该列（使用实体建模表 A 和表 B 的给定示例），您将编写如下内容：

variable_A = tableAs.A_ID
variable_B = tableAs.tableBs.B_ID

The second assignment is using the association defined in the entity to get to the data. There is no PROPERTYcalled "B_ID" on table A.

第二个任务是使用实体中定义的关联来获取数据。表 A 上没有名为“B_ID”的PROPERTY。

That is all assuming I understand it right :). It is at least intelisensing correctly for me now.

这一切都假设我理解正确:)。现在至少对我来说是正确的智能感知。

:-Dan

：-担

回答by user328691

First create a foreign key in the database and then create association or update model from database.

首先在数据库中创建一个外键，然后从数据库中创建关联或更新模型。

回答by BlueRaja - Danny Pflughoeft

In Entity Framework, there should be no foreign keys in the conceptual design.I believe this is now allowed in EF4 (with some tweaking), but in EF3.5, it can't be done.

在实体框架中，概念设计中不应该有外键。我相信现在这在 EF4 中是允许的（有一些调整），但在 EF3.5 中，它不能完成。

To fix this, simply delete all properties which represent foreign keys in the EF-designer. Do not delete the primary keys!

要解决此问题，只需删除 EF 设计器中表示外键的所有属性。不要删除主键！

If you then get an "Association end is not mapped.."error, see (my answer to) this post.

如果您随后收到“未映射关联端...”错误，请参阅（我的回答）这篇文章。

回答by Antony

I received the same error when I tried to do a one-to-one between two tables joined on their primary keys. (No, I didn't design the db). So, the solution of deleting the FK doesn't work - the FK is also the PK, so you are not going to delete it. As an experiment, I changed one side of the association to 0..1, and lo and behold, the model compiled w/o errors. I was using EF3.5

当我尝试在以主键连接的两个表之间进行一对一操作时，我收到了同样的错误。（不，我没有设计数据库）。所以，删除FK的解决方案是行不通的——FK也是PK，所以你不打算删除它。作为一项实验，我将关联的一侧更改为 0..1，瞧，模型编译时没有错误。我使用的是 EF3.5

回答by Pankaj Awasthi

In simple terms I did the following:

简单来说，我做了以下事情：

1. Create Table with Primary Key (PK) on a column.
2. Create a view with Foreign Key (FK) on a column. or vice-versa.
3. In edmx Diagram Designer, created a Association .
4. Compile the code.

1. 在列上使用主键 (PK) 创建表。
2. 在列上创建带有外键 (FK) 的视图。或相反亦然。
3. 在 edmx Diagram Designer 中，创建了一个 Association 。
4. 编译代码。

In XAML metadata code, Parent Entity having PK will show up the child view Entity, that can be associated to bind the items.

在 XAML 元数据代码中，具有 PK 的父实体将显示子视图实体，可以关联以绑定项目。

回答by JoeCool

After pretty much a whole day working on this, I figured it out! In fact, I found 2 different ways to solve this problem, assuming that I had the same problem Craig had.

经过几乎一整天的工作，我想通了！事实上，我找到了 2 种不同的方法来解决这个问题，假设我遇到了与 Craig 相同的问题。

First off, driAn's answer doesn't really help (once again, if Craig has the same issue I have), since the issue is that there IS no separate foreign key in table A. Table A's primary key is the exact same primary key for Table B! Specifically in my case, I'm using a view and a table, rather than two tables:

首先，driAn 的回答并没有真正帮助（再一次，如果 Craig 有同样的问题），因为问题是表 A 中没有单独的外键。表 A 的主键是完全相同的主键表B！特别是在我的情况下，我使用的是一个视图和一个表，而不是两个表：

table A
-------
A_ID int
col1 int
col2 int

view A_extra
-------
A_ID int
col3 int

So, here are the solutions I found:

所以，这里是我找到的解决方案：

1. Map to two different entities, but use a hack.So the whole problem with driAn's answer is that we don't have a separate foreign key column to map to, which we can then delete from the properties (we can't delete it from the properties because it's the primary key of view A_extra!). So the hack is insanely simple once you realize it: edit A_extra and simply create a copy of A_ID and make it the stand-in foreign key:

1. 映射到两个不同的实体，但使用 hack。所以 driAn 的答案的整个问题是我们没有单独的外键列要映射到，然后我们可以从属性中删除它（我们不能从属性中删除它，因为它是视图 A_extra 的主键！ ）。因此，一旦您意识到这一点，黑客就非常简单：编辑 A_extra 并简单地创建 A_ID 的副本并将其设为替代外键：

table A
-------
A_ID int
col1 int
col2 int

view A_extra
-------
A_ID int
A_FK_ID int        
col3 int

A_FK_ID is simply defined as 'A_ID as A_FK_ID' in the view's SQL, and it will always have the exact same value as A_ID.

A_FK_ID 在视图的 SQL 中简单地定义为“A_ID as A_FK_ID”，并且它始终具有与 A_ID 完全相同的值。

Now, to entity framework, this is a very familiar scenario. Simply create the 1-to-1 association, map the association to A_extra, and map property A_ID in table A to A_FK_ID and property A_ID in A_extra to A_ID. Now you're no longer making the same column do double duty according to EF! However, the funny thing is of course, A_FK_ID will always be identical to A_ID in A_extra.

现在，对于实体框架来说，这是一个非常熟悉的场景。只需创建一对一关联，将关联映射到 A_extra，并将表 A 中的属性 A_ID 映射到 A_FK_ID，将 A_extra 中的属性 A_ID 映射到 A_ID。现在，根据 EF，您不再让同一列执行双重任务！然而，有趣的是，A_FK_ID 总是与 A_extra 中的 A_ID 相同。

Now you have a navigation property to A_extra and col3 is accessible, just as you'd expect. Even better, you can still natively update table_A, since EF sees that it's a table (if you had made a single view from this combination and imported that into EF, you can't natively update it).

现在您有了 A_extra 的导航属性，并且可访问 col3，正如您所期望的那样。更好的是，您仍然可以本机更新 table_A，因为 EF 认为它是一个表（如果您从这个组合中创建了一个视图并将其导入到 EF 中，则您无法本机更新它）。

2. Map the table and view to a single entity.You don't need a hack for this solution, and it's preferable because you don't have to go through a navigation property to get to col3.

2. 将表和视图映射到单个实体。对于此解决方案，您不需要 hack，而且更可取，因为您不必通过导航属性即可到达 col3。

To get this configuration, add both A and A_extra to EF. Copy and paste col3 from A_extra into A, so that you now have unmapped property "col3" sitting in A, and nothing in A_extra. Now go to the mapping for A and add A_extra as a view to be mapped to (so that the entity "A" is now mapped to both the table "A" and the view "A_extra"). Map the property col3 (in entity A) to the column col3 (in view A_extra). Now here's how you show EF how to join the two to create the single entity:map the column A_ID in A_extra to the property ID in entity A. If you notice, you now have TWO columns mapped to the same property. This is ok, since they represent the same thing, and is precisely how EF knows to join on that column.

要获得此配置，请将 A 和 A_extra 添加到 EF。将 col3 从 A_extra 复制并粘贴到 A 中，以便您现在在 A 中拥有未映射的属性“col3”，而在 A_extra 中没有任何内容。现在转到 A 的映射并添加 A_extra 作为要映射到的视图（以便实体“A”现在映射到表“A”和视图“A_extra”）。将属性 col3（在实体 A 中）映射到列 col3（在视图 A_extra 中）。现在，您将向 EF 展示如何连接两者以创建单个实体：将 A_extra 中的列 A_ID 映射到实体 A 中的属性 ID。如果您注意到，现在有两列映射到同一属性。这是可以的，因为它们代表相同的事物，并且正是 EF 知道加入该列的方式。

Now, to finish cleaning things up, you'll have to delete the floating entity "A_extra" in the designer. (You might want to make sure it's not mapped to view A_extra anymore just to be sure that it doesn't take away the store mapping for A_extra either -- I would hope it wouldn't since entity A is now mapped to it). Now you should be able to build and access col1, col2, and col3 all from entity A!

现在，要完成清理工作，您必须删除设计器中的浮动实体“A_extra”。（您可能想确保它不再映射到视图 A_extra 只是为了确保它也不会带走 A_extra 的商店映射——我希望它不会因为实体 A 现在映射到它）。现在您应该能够从实体 A 构建和访问 col1、col2 和 col3！

The cool thing is you can update data to col1 and col2 fine, since they're mapped to a table (A), but EF will prevent you like it should from updating col3, since it's mapped to a view (A_extra). This is more convenient than making A a combined view in the DB and importing it, because EF would prevent you from updating anycolumns, since they're all from a view as far as it's concerned.

很酷的事情是你可以将数据更新到 col1 和 col2 很好，因为它们被映射到一个表 (A)，但是 EF 会阻止你更新 col3，因为它被映射到一个视图 (A_extra)。这比在数据库中创建 A 组合视图并导入它更方便，因为 EF 会阻止您更新任何列，因为就它而言，它们都来自视图。

Whew, I'm so glad I got this finally working. Hope my solutions helped you guys!

哇，我很高兴我终于可以使用它了。希望我的解决方法对大家有所帮助！

-Robert

-罗伯特

回答by Sylvain Rodrigue

Not shure to have the answer, but with Entity Framework, I always create a primary key in each table (even if I don't need it). Example :

不知道答案，但使用实体框架，我总是在每个表中创建一个主键（即使我不需要它）。例子 ：

• Table Customer have CustumerID as primary key
• Table Product have ProductID as primary key
• Table Order of course use CustomerID + ProductID as the primary key. Well, I also create a single local primary key : OrderID.

• 表客户以客户 ID 作为主键
• 表 Product 以 ProductID 作为主键
• Table Order 当然使用 CustomerID + ProductID 作为主键。好吧，我还创建了一个本地主键：OrderID。