How to prepare Database to generate EF model from it

Question

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:

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?

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