Question with Entity Framework with many questions

Question

Please help EF n00b create its own database. I have several companies that produce several products, so there are many relationships between companies and products. I have a staging table, Company_Product, which links them.

Each combination of companies / products has a unique SKU. For example, Acme widgets have SKU 123, but Omega widgets have SKU 456. I added SKU as a field in the Company_Product staging table.

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

Do I need to put SKU in my table and write a connection, or is there a better way?

Answer 1

I just tested this in the new VS2010 project (EFv4), and here is what I found:

When your associative table in the middle (Company_Product) has ONLY two foreign keys to other tables (CompanyID and ProductID), then adding all three tables to the designer leads to modeling many, many relationships. It does not even create a class for the Company_Product table. Each company has a collection of products, and each product has a collection of companies.

However, if your association table (Company_Product) has other fields (such as SKU, your own Primary Key, or other descriptive fields, such as dates, descriptions, etc.), then the EF model will create a separate class, and this will make you already have seen.

Having a class in the middle with a 1: * relationship to the company and the product is not bad, and you can still get the data you need with simple 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 is not so easy to navigate the model relations when you already have entity objects, for example, but this is what is for the data layer. Encapsulate requests that receive the data you need. If you really want to get rid of this middle class Company_Product and have many to many directly represented in the class model, then you will have to split the Company_Product table to contain only 2 foreign keys and get rid of SKU.

Actually, I should not say that you need to do this ... you could make some changes to the designer and set it up that way anyway. I will try and send a report.

UPDATE

Saving the SKU in the Company_Product table (which means that my EF model had 3 classes, not 2, it created the Company_Payload class with 1: * two other tables), I tried to add an association directly between the company and the Product. The following steps followed:

• Right click on a company class in the designer
• Add> Association
• Set "End" on the left to be a Company (it should be already)
• Set "End" to the right of the Product
• Change both multiples to "* (Many)"
• Navigation properties should be called Products and Companies.
• Click OK.
• Right-click on the association in the model> click on Table Mapping
• In the "Add a table or view" section, select "Company_Product"
• Company Map → ID (left) to CompanyID (right)
• Product Card → ID (left) for ProductID (right)

But that will not work. It gives this error: Error 3025: a problem with the display of fragments starting from line 175: you must specify the display of all the key properties (Company_Product.SKU) of the Company_Product table.

So a certain association is invalid because it uses Company_Product as a table, but does not map the SKU field to anything.

Also, while I was studying this, I came across this “best practice” from the Entity Framework 4.0 Recipies book (note that for the association table with additional fields other than 2 FK, they refer to additional fields as a “payload.” In your case SKU is the payload in Company_Product).

Best practice

Unfortunately, a project that starts with a few, useless, many-to-many relationships often ends with a few, payload-rich, many-to-many relationships. Refactoring a model, especially late in designing a cycle to accommodate payloads in a many-to-many relationship, can be tedious. Not only additional entities, but queries and navigation patterns through relationships also change. Some developers argue that each many-to-many relationship should begin with some payload, usually a synthetic key, so the inevitable addition of a larger payload has a significantly smaller impact on the project.

So here is the best practice. If you do not have useful information, many-to-many relationships and you think there is a chance that it can include a payload over time, start with an additional identification column in the link table. When you import tables into your model, you will get two one-to-many relationships, which means the code you are writing, and the model you will be ready for any number of additional payload columns that will come as soon 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. Basics of modeling entity data, 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 like to add the following to Samuel:

If you want to directly query the many-to-many relationship (with the payload) on the one hand for 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); 

Instead of the variable products there would be all Product records associated with the Company c record. If you want to enable SKU for each of the products, you can use an anonymous class:

 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 request in a read-only property for simplicity as follows:

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

You cannot do this with a query that includes SKUs because you cannot return anonymous types. You should have a specific class, which is usually done by adding an unlabeled property to the Product class or creating another class that inherits from Product , which will add the SKU property. However, if you use an inherited class, you will not be able to make changes to it and manage it EF - this would be useful only for display.

Greetings. :)