Entity Framework - Complex Model using Code First

Question

Good day guys,

I've got quite a specific data model that I'm trying to create in POCO classes, and I've tried so many different approaches and visited so many websites, but none seem to cover my specific example.

So I'm going to throw this out there and hope someone can help me.

I've got products, and products have ingredients. In our business though, these products can get supplied by different vendors, each with a slightly different set of ingredients.

So I've got my base classes:

public class Product
{  
    public int ProductID { get; set; }
    public string ProductNumber { get; set; }
    public string Description { get; set; }
    public virtual ICollection<Supplier> Suppliers { get; set; }
}

public class Vendor
{
    public int VendorID { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Supplier> Suppliers { get; set; }
}

public class Ingredient
{
    public int IngredientID { get; set; }        
    public string Name { get; set; }
}

public class Supplier
{
    public int SupplierID { get; set; }

    public virtual ICollection<Product> Products { get; set; }
    public virtual ICollection<Vendor> Vendors { get; set; }
}

So as you can see, I've gotten as far as realising that my model needs a join entity, because my ingredients come from a specific vendor for a product, I need that join entity called supplier (I think).

This is where I am getting stuck... My product can be supplied by one or more vendors. Each vendor, per product, will have a list of ingredients for that product

I can't even get past whether I need the join entity or not.

What would be awesome was if I could navigate the data structure like this (or something similar):

articles[1].Vendors[3].Ingredients;

Yet again, I don't know enough about Entity Framework to know if this is possible.

The way I'm doing it now feels... wrong. What is the correct way to be doing what I want to do? Do I need the join table or can I somehow navigate the ingredients for a vendor for a product, maybe using the modelbuilder to make the product->vendor->ingredient link required?

Any help would be greatly appreciated!

Edit: I also think I don't understand enough about Entity Framework's relationship ideas.

So one product can have multiple vendors, which is a 1-to-many relationship. But a Vendor can have many products. So is this actually a many-to-many?

I can definitely model what I want to do on a SQL Express server, but I really want to use Entity Framework. I just need a better understanding of my modelling requirements.

It looks like there is a ternary relationship among Product, Supplier and Ingredient. Is it correct? — Eranga
– Eranga, Commented Feb 23, 2012 at 7:46
I had to lookup ternary relationships... but I believe so. Product will always have 1-to-many Suppliers (vendors) and a Supplier will have 1-to-many ingredients for that product. I've also wondered whether I should rather carry an ingredient set and associate a set of ingredients with a supplier for a given product. — Tiny
– Tiny, Commented Feb 23, 2012 at 8:07

Ladislav Mrnka · Accepted Answer · 2012-02-23 09:17:06Z

3

It is not problem of EF. It is common entity-relationship task. You must simply improve your model. You can for example divide Product into two entities: ProductDefinition, ProductInstance

public class ProductDefinition
{  
    public int ProductID { get; set; }
    public string ProductNumber { get; set; }
    public string Description { get; set; }
    public virtual ICollection<ProductInstance> Instances { get; set; }
}


public class ProductInstance
{
    public int ProductInstanceID { get; set; }
    public virtual Supplier Supplier { get; set; }
    public virtual ProductDefinition Definition { get; set; }
    public vritual ICollection<Ingredient> Ingredients { get; set; }
}

In your system you will work with ProductDefinition instead of original Product. This definition is like group of same products and it points to all possible manufacturings of products provided by different suppliers with different ingredients.

I don't know your context but it seems strange to count products with different ingredients as "same".

answered Feb 23, 2012 at 9:17

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Tiny Over a year ago

Trying this model now. Will give feedback as soon as possible. In answer to your last comment: The product has one barcode, but sold in different regions. In each region, we basically have a different supplier making that product on our behalf. It's here where the different suppliers use slightly different sets of ingredients. They don't differ by much, but they do differ and we need to keep record of it.

Tiny Over a year ago

Okay, the definition looks good, but how do I add records to the product instance? Here is what I have now:

var productInstance = new List<ProductInstance>             {                 new ProductInstance { Definition = context.Products.Find(1), Vendor = context.Vendors.Find(1), Ingredients = new List<Ingredient>                     {                         new Ingredient { Name = "Water" },                         new Ingredient { Name = "Sugar" }                     }             }

This doesn't seem to work, so I'm not sure I understand it all...

Ladislav Mrnka Over a year ago

What exactly doesn't work? It looks like your primer problem is not understanding how to use EF basics. Perhaps you should go through some tutorial prior to creating your application.

Tiny Over a year ago

I went through two specific examples: The MVC Music Store; and Contoso University Examples. I think my problem is that I'm thinking in SQL terms, instead of EF terms. Do I need to populate my vendor and product tables with information first, then link that to my productInstance table? Or do I create a productInstance that populates my vendor and product automatically as I save my productInstance. An example of adding something to the entities involved, like product, vendor and productInstance, would be awesome. Other than that, your answer has helped me immensely already :)

Tiny Over a year ago

Believe me, I've tried applying the principles in the two examples to my application, but I just can't get it to work.

|

Malmi · Accepted Answer · 2012-02-23 09:12:08Z

0

Why do you use the code first, instead of making the design of the database first? Maybe this will be a bit easier. Additionally i cant see the relation between Vendor and Ingredient, or Product and Ingredient

For my understanding: It's a new product, when a vendor uses other ingredients?

answered Feb 23, 2012 at 9:12

Malmi

3873 silver badges8 bronze badges

2 Comments

Tiny Over a year ago

Mostly using Code First because I want to learn Code First. This is a brand new application. For your understanding: No, it's not a new product when a vendor uses other ingredients. It's one product, supplied by different vendors. Each vendor uses slightly different ingredients.

Tiny Over a year ago

The biggest issue is that I can see the relation, I just don't know how to model that relation successfully in Entity Framework

Collectives™ on Stack Overflow

Entity Framework - Complex Model using Code First

2 Answers 2

8 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related