Problem with Linq2Sql Many-to-Many relationship & Inserting new objects.

Answer 1

Many to Many relationships aren't supported in Linq2Sql. :(

There are a couple of workarounds:

http://www.iaingalloway.com/many-to-many-relationships-in-linq-to-sql

http://blogs.msdn.com/mitsu/archive/2008/03/19/how-to-implement-a-many-to-many-relationship-using-linq-to-sql-part-ii-add-remove-support.aspx

Weird that the picture of your db schema is the same as one of the articles...

Answer 2

// Associate each product to the a new order_detail.
orderDetailList.Add(new Order_Detail
{
    Product = new SqlContext.Product
    {
        Foo = product.Foo
    }
});

One thing that is wrong here, is that you create a new product to set on your Order_Detail.Product property. Instead , you should take the product that's comming from the database and set it on the property.

I'm not sure what order.ProductList has inside - if these products are loaded from the database then you should set them directly to your Order_Detail.Product instead of doing new SqlContext.Product.

@jfar L2S does support many-to-many relationships , you just can't have a property Products on your Order ( in this case this is actually a good thing because OrderDetails has Quantity and other properties).