0

I have some misunderstanding how my objects should be represented in a relational db structure.

For example if I have a simple tables structure that looks like that:

enter image description here

And if I generate an Entity Framework model based on that, it would be look like:

enter image description here

As you can see it's many-to-many association. (Any Dish contains many Ingredients, any Ingredient can be used in many Dishes)

Now, what if I need some additional parameters, like quantity of the Ingredient in a Dish? (Of course it usually would be distinct number for each ingredient in any different Dish).

If I add some additional columns directly to Dish_FooIngridient table, it breaks nicety of automatic model generation, and then I have to fix associations manually in EF model designer and later use some cumbersome queries to operate with objects. Because it will be generated into something like this:

enter image description here

As you can see, now there are other redundant properties, which I don't need. Is there any better way to manage that? Maybe using complex properties or inherited entity, or something else?

iLemming
  • 34,477
  • 60
  • 195
  • 309

3 Answers3

1

As it's suggested in Apress' "Entity Framework 4.0 Recipes", designing Many-To-Many Relationships without a payload not a very good practice.

Unfortunately, a project that starts 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.

iLemming
  • 34,477
  • 60
  • 195
  • 309
0

I don't see why. Add the fields to Dish_FoodIngredient and give it a primary key. Add that table to your model and thats' it. You need to do some refactoring in your code probably but don't blame EF for that.

Jeroen
  • 4,023
  • 2
  • 24
  • 40
-1

Double-click your Model.edmx file, then in the white space next to your objects, right-click and click "Update Model from Database...", then click Finish, and it'll refresh your tables.

Bob.
  • 3,894
  • 4
  • 44
  • 76
  • Read the question thoroughly... I'm not asking HOW to update the model – iLemming Jun 06 '11 at 18:38
  • "If I add some additional columns directly to Dish_FooIngridient table, it breaks nicety of automatic model generation, and then I have to fix associations manually in EF model designer and later use some cumbersome queries to operate with objects." So you want to add new columns to a table in your database, but not update your model to match your changes? Updating from EF will add all the changes including associations. – Bob. Jun 06 '11 at 18:41
  • I updated the question and added another picture, I hope it will explain the problem better – iLemming Jun 06 '11 at 18:49
  • Its breaks it from a many to many because you're adding a scalar, before it was just used to link two entities together, but now it contains a scalar that's why it generates three entities. You would have to choose one of the two tables and remove the middle if you want only two entities – Bob. Jun 06 '11 at 18:54
  • then how and where should I store Ingredient's Quantity? – iLemming Jun 06 '11 at 19:00