Entity Framework many-to-many question

Question

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.

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 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.

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

Answer 1

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

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.

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.

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;

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.

UPDATE

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:

• 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)

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.

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

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).

Best Practice

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.

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

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

Answer 2

I would just like to add the following to Samuel's answer:

If you want to directly query from one side of a many-to-many relationship (with payload) to the other, you can use the following code (using the same example):

Company c = context.Companies.First();
IQueryable<Product> products = c.Company_Products.Select(cp => cp.Product);

The products variable would then be all Product records associated with the Company c record. If you would like to include the SKU for each of the products, you could use an anonymous class like so:

var productsWithSKU = c.Company_Products.Select(cp => new {
  ProductID = cp.Product.ID,
  Name = cp.Product.Name,
  Price = cp.Product.Price,
  SKU = cp.SKU
});
foreach (var 

You can encapsulate the first query in a read-only property for simplicity like so:

public partial class Company
{
  public property IQueryable<Product> Products
  {
    get { return Company_Products.Select(cp => cp.Product); }
  }
}

You can't do that with the query that includes the SKU because you can't return anonymous types. You would have to have a definite class, which would typically be done by either adding a non-mapped property to the Product class or creating another class that inherits from Product that would add an SKU property. If you use an inherited class though, you will not be able to make changes to it and have it managed by EF - it would only be useful for display purposes.

Cheers. :)