Problem with Linq2Sql Many-to-Many relationship &

Question

i'm trying to do a simple linq 2 sql many-to-many, insert some data, operation.

here's the stock Northwind model representing a many to many:

alt text http://www.iaingalloway.com/images/linq-detail.jpg

Now what i'm trying to do is insert a new order and if the product doesn't exist, then insert that at the same time, within the same transaction. The error i'm getting is:

System.Data.Linq.DuplicateKeyException: Cannot add an entity with a
 key that is already in use.

So this is my (pseduo) code:

using (SqlContext db = new SqlContext())
{
    // Get existing or create a new instance.
    Order newOrder = GetOrder(order.Id) ?? new Order();
    // Left to right stuff.
    newOrder.Foo = order.Foo;

    // Now associate this new order to a product (which might not exist).
    if (!order.ProductList.IsNullOrEmpty())
    {
        // We have some products...

        IList<Order_Detail> orderDetailList = new List<Order_Detail>();
        foreach(Models.Product product in order.ProductList)
        {
            // Associate each product to the a new order_detail.
            orderDetailList.Add(new Order_Detail
                                    {
                                        Product = new SqlContext.Product
                                                      {
                                                          Foo = product.Foo
                                                      }
                                    });
        }

        // Now associate all the order_details to this order.
        newOrder.Order_Details.AddRange(orderDetailList);

        if (newOrder.Id <= 0)
            db.InsertOnSubmit(newOrder);

        db.SubmitChanges();   // <-- exception throw here.
    }
}

I'm assuming i need to save the products first before i try and save the order? I'm so confused :(

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