实体框架多对多问题 [英] Entity Framework many-to-many question

问题描述

请帮助EF n00b设计他的数据库. 我有几家公司生产几种产品，所以公司与产品之间存在多对多的关系.我有一个与它们相关的中间表Company_Product.

Please help an EF n00b design his database. I have several companies that produce several products, so there's a many-to-many relationship between companies and products. I have an intermediate table, Company_Product, that relates them.

每个公司/产品组合都有唯一的SKU.例如，Acme小部件的SKU为123，而Omega小部件的SKU为456.我将SKU作为字段添加到Company_Product中间表中.

Each company/product combination has a unique SKU. For example Acme widgets have SKU 123, but Omega widgets have SKU 456. I added the SKU as a field in the Company_Product intermediate table.

EF生成了一个模型，该模型在company和Company_Product表之间具有1:*的关系，在product和Company_Product表之间具有1:*的关系.我真的想要公司与产品之间的:关系.但是，最重要的是，无法直接从模型访问SKU.

EF generated a model with a 1:* relationship between the company and Company_Product tables, and a 1:* relationship between the product and Company_Product tables. I really want a : relationship between company and product. But, most importantly, there's no way to access the SKU directly from the model.

我是否需要将SKU放在自己的表中并编写联接，还是有更好的方法?

Do I need to put the SKU in its own table and write a join, or is there a better way?

推荐答案

为了确定，我刚刚在一个新的VS2010项目(EFv4)中对此进行了测试，这是我发现的内容:

I just tested this in a new VS2010 project (EFv4) to be sure, and here's what I found:

当中间的关联表(Company_Product)仅将2个外键指向其他表(CompanyID和ProductID)时，然后将所有3个表添加到设计器中，最终会为多对多关系建模.它甚至不会为Company_Product表生成一个类.每个公司都有一个产品集合，每个产品都有一个公司集合.

When your associative table in the middle (Company_Product) has ONLY the 2 foreign keys to the other tables (CompanyID and ProductID), then adding all 3 tables to the designer ends up modeling the many to many relationship. It doesn't even generate a class for the Company_Product table. Each Company has a Products collection, and each Product has a Companies collection.

但是，如果您的关联表(Company_Product)具有其他字段(例如SKU，它自己的主键或其他描述性字段，例如日期，描述等)，则EF建模人员将创建一个单独的类，并且您已经看到的内容.

However, if your associative table (Company_Product) has other fields (such as SKU, it's own Primary Key, or other descriptive fields like dates, descriptions, etc), then the EF modeler will create a separate class, and it does what you've already seen.

让类与公司和产品之间保持1:*的关系不是一件坏事，并且您仍然可以通过一些简单的查询来获取所需的数据.

Having the class in the middle with 1:* relationships out to Company and Product is not a bad thing, and you can still get the data you want with some easy queries.

// Get all products for Company with ID = 1
var q =
    from compProd in context.Company_Product
    where compProd.CompanyID == 1
    select compProd.Product;

是的，例如，当您已经加载了实体对象时，导航模型的关系并不容易，但这就是数据层的用途.封装获取所需数据的查询.如果您真的想摆脱该中间Company_Product类，并在类模型中直接表示多对多，则您必须精简Company_Product表以仅包含2个外键，并摆脱掉SKU.

True, it's not as easy to just navigate the relationships of the model, when you already have your entity objects loaded, for instance, but that's what a data layer is for. Encapsulate the queries that get the data you want. If you really want to get rid of that middle Company_Product class, and have the many-to-many directly represented in the class model, then you'll have to strip down the Company_Product table to contain only the 2 foreign keys, and get rid of the SKU.

实际上，我不应该说您必须这样做...您也许可以在设计器中进行一些编辑并以这种方式进行设置.我会试一试并报告.

Actually, I shouldn't say you HAVE to do that...you might be able to do some edits in the designer and set it up this way anyway. I'll give it a try and report back.

更新

将SKU保留在Company_Product表中(这意味着我的EF模型具有3个类，而不是2个；它创建了Company_Payload类，并且对其他2个表使用了1:*)，我试图直接在Company和产品.我遵循的步骤是:

Keeping the SKU in the Company_Product table (meaning my EF model had 3 classes, not 2; it created the Company_Payload class, with a 1:* to the other 2 tables), I tried to add an association directly between Company and Product. The steps I followed were:

• 右键单击设计器中的Company类
• 添加>关联
• 在左侧将"End"设置为Company(应该已经)
• 在产品右侧设置结束"
• 将两个多重性都更改为"*(很多)"
• 导航属性应命名为产品"和公司"
• 好的.
• 右键单击模型中的关联>单击表映射"
• 在添加表或视图"下，选择公司产品"
• 将公司映射-> ID(在左侧)到CompanyID(在右侧)
• 将产品-> ID(在左侧)映射为ProductID(在右侧)

• Right click on the Company class in the designer
• Add > Association
• Set "End" on the left to be Company (it should be already)
• Set "End" on the right to Product
• Change both multiplicities to "* (Many)"
• The navigation properties should be named "Products" and "Companies"
• Hit OK.
• Right Click on the association in the model > click "Table Mapping"
• Under "Add a table or view" select "Company_Product"
• Map Company -> ID (on left) to CompanyID (on right)
• Map Product -> ID (on left) to ProductID (on right)

但是，它不起作用.它给出了这个错误: 错误3025:映射片段的问题始于第175行:必须为表Company_Product的所有关键属性(Company_Product.SKU)指定映射.

But, it doesn't work. It gives this error: Error 3025: Problem in mapping fragments starting at line 175:Must specify mapping for all key properties (Company_Product.SKU) of table Company_Product.

该特定关联无效，因为它使用Company_Product作为表，但未将SKU字段映射到任何内容.

So that particular association is invalid, because it uses Company_Product as the table, but doesn't map the SKU field to anything.

此外，当我进行研究时，我从Entity Framework 4.0 Recipies一书中遇到了这个最佳实践"花絮(请注意，对于带有额外字段的关联表，除了2个FK之外，它们将额外字段称为: 有效负载".在您的情况下，SKU是Company_Product中的有效负载).

Also, while I was researching this, I came across this "Best Practice" tidbit from the book Entity Framework 4.0 Recipies (note that for an association table with extra fields, besides to 2 FKs, they refer to the extra fields as the "payload". In your case, SKU is the payload in Company_Product).

最佳做法

不幸的是，一个项目 首先是几个 无有效载荷，多对多 关系常常以 几个，有效载荷丰富，多对多 关系.重构模型， 特别是在开发后期 循环，以将有效载荷容纳在 多对多关系可以是 乏味.不仅是额外的 实体介绍，但查询 和导航模式 关系也随之改变.一些 开发人员争辩说，每个 多对多关系应该开始 带着一些有效载荷，通常是 合成键，所以不可避免 增加了更多的有效载荷 大大减少了对 项目.

Unfortunately, a project that starts out with several, payload-free, many-to-many relationships often ends up with several, payload-rich, many-to-many relationships. Refactoring a model, especially late in the development cycle, to accommodate payloads in the many-to-many relationships can be tedious. Not only are additional entities introduced, but the queries and navigation patterns through the relationships change as well. Some developers argue that every many-to-many relationship should start off with some payload, typically a synthetic key, so the inevitable addition of more payload has significantly less impact on the project.

因此，这是最佳做法. 如果您没有有效载荷， 多对多关系和你 认为有可能 随时间变化以包含有效载荷， 从中的额外身份列开始 链接表.当您导入 表放入模型中，您将获得 两个一对多的关系 表示您编写的代码和模型 您将准备好使用任意数量的电话 的其他有效负载列 随着项目的成熟而发展.这 附加整数身份的成本 列通常是一个很小的价格 支付以保持模型更多 灵活.

So here's the best practice. If you have a payload-free, many-to-many relationship and you think there is some chance that it may change over time to include a payload, start with an extra identity column in the link table. When you import the tables into your model, you will get two one-to-many relationships, which means the code you write and the model you have will be ready for any number of additional payload columns that come along as the project matures. The cost of an additional integer identity column is usually a pretty small price to pay to keep the model more flexible.

(从第2章实体数据建模基础知识，第2.4章.建立具有有效负载的多对多关系")

(From Chapter 2. Entity Data Modeling Fundamentals, 2.4. Modeling a Many-to-Many Relationship with a Payload)

听起来像个好建议.尤其是因为您已经有一个有效载荷(SKU).

Sounds like good advice. Especially since you already have a payload (SKU).

这篇关于实体框架多对多问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！