How to prepare Database to generate EF model from it

I have some mi开发者_Go百科sunderstanding how my objects should be represented in a relational db structure.

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

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

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:

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?

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.

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.

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.