Geek Answers Handbook

Problem with Linq2Sql Many-to-Many Relationship and Adding New Objects

I am trying to make a simple linq 2 sql many-to-many, insert some data, work.

Here's a Northwind model that represents many for 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 does not exist, then insert it at the same time within the same transaction. The error I am 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 assume that I need to save the products first before I try to save the order? I'm so confused: (

+4
source share
2 answers
 // 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 not right here is that you are creating a new product to install in your Order_Detail.Product attribute. Instead, you should take the product that comes from the database and install it in the property.

I'm not sure what order .ProductList has inside - if these products are loaded from the database, you should install them directly in your Order_Detail.Product instead of making a new SqlContext.Product.

@jfar L2S maintains a many-to-many relationship, you simply cannot have the “Products” property in your order (in this case it’s actually good, because OrderDetails has a quantity and other properties).

+2
source

Many to Many associations are not supported in Linq2Sql. :(

There are several ways:

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

It is strange that the image of your db schema matches one of the articles ...

-1
source

More articles:


All Articles