Linq to SQL - Inserting entities with many to many relation

Consider the following tables (scree开发者_如何学编程nshot of dbml file editor in Visual Studio):

http://roosteronacid.com/dbml.png

The IngredientsRecipesRelation table is a many-to-many table, linking n ingredients to a single recipe.

How would you insert the following recipe...:

Recipe { Name = "Dough" }

With the following ingredients...:

Ingredient { Name = "water", Unit = "cups", Amount = 2.0 },
Ingredient { Name = "flour", Unit = "cups", Amount = 6.0 },
Ingredient { Name = "salt", Unit = "teaspoon", Amount = 2.0 }

... Into the database?

Create the recipes and ingredients, then for each ingredient, create a relation associated with the ingredient. Add each of these relations to the recipe and insert the recipe into the database.

var recipe = new Recipe { Name = "Dough" };

var ingredients = new []
{ 
   new Ingredient { Name = "water", Unit = "cups", Amount = 2.0 }, 
   new Ingredient { Name = "flour", Unit = "cups", Amount = 6.0 }, 
   new Ingredient { Name = "salt", Unit = "teaspoon", Amount = 2.0 }
};

foreach (var ingredient in ingredients)
{
   var relation = new IngredientsRecipesRelations();

   relation.Ingredient = ingredient;

   recipe.IngredientsRecipesRelations.Add(relation);
}

DataContext.Recipes.InsertOnSubmit(recipe);
DataContext.SubmitChanges();

Note that you could add a partial class implementations with methods to hide this behavior from the class that uses it. You'd want to make the associations internally scoped then expose some public methods to add/remove ingredients that work with the internal association as I've demonstrated above.

azamsharp is correct in that it does not support many to many, however this link goes some way to show how it can be done, essentially you will be hand crafting some of the code