Linq to SQL ForeignKeyReferenceAlreadyHasValueException

Question

I looked through several threads, but I don’t see the solution fit my purpose (at least I don’t understand how I could implement this)

I have a WCF service that uses Linq to SQL to retrieve, update, and delete objects on my SQL Server.

I created a simple relational database that has 1 to many relationships between Customer and Customer, 1 for many relationships between Order and OrderDetails,

My Orders now has a foreign key for CustomerID, and OrderDetails has a Foreign kwy value for the order ID.

however, OrderDetails also contains an FK for ProductID in the product table.

Basically what I'm trying to do right now is to change OrderDetails with OrderID and add another product using ProductID.

I have problems with this though, as I keep getting ForeignKeyReferenceAlreadyHasValueException

I wrote this, which, as I know, is completely wrong, but at a time when I did not know (I am completely new to SQL, Linq to SQL, etc.) that I could not do this.

OrderDetail item = new OrderDetail(); item.OrderID = orderItem.OrderID; item.ProductID = orderItem.ProductID; item.ProductQuantity = orderItem.ProductQuantity; jacksDB.OrderDetails.InsertOnSubmit(item); jacksDB.SubmitChanges(); 

I read that I needed to display an entity or something in that direction using a common line of code, such as

  var order = jacksDB.Orders.Single(o => o.OrderID == orderItem.OrderID); var orderDetail = order.OrderDetails.Single(o => o.OrderID == orderItem.OrderID); orderDetail.ProductID = orderItem.ProductID; orderDetail.ProductQuantity = orderItem.ProductQuantity; orderDetail.Discount = orderItem.Discount; jacksDB.OrderDetails.InsertOnSubmit(orderDetail); jacksDB.SubmitChanges(); 

Maybe someone can show, and if you don’t ask too much, explain a little how I can correctly insert a new OrderDetail entry into my OrderDetails table using the existing OrderID (FK) to "Edit and Add / Remove a Product to an Existing Order"

Thank you in advance for your help.

John

Answer 1

OK, so you get this error,

http://msdn.microsoft.com/en-us/library/system.data.linq.foreignkeyreferencealreadyhasvalueexception.aspx

ForeignKeyReferenceAlreadyHasValueException

And the link says it,

Represents errors that occur when trying to change a foreign key when an object is already loaded.

I think that you will need to load the order you are talking about, and it will have a list of OrderDetails related to it. If you want to remove one of these links, you need to remove OrderDetail from the OrderDetails list.

I think you need to do something like this,

 using (DataClasses1DataContext context = new DataClasses1DataContext()) { Customer customer = context.Customers.Where(x => x.CustomerID == 1).Single(); Order order = new Order(); // set some order fields here customer.Orders.Add(order); OrderDetail orderDetail = new OrderDetail(); order.OrderDetails.Add(orderDetail); orderDetail.Product = context.Products.Where(x => x.ProductID == 2).Single(); orderDetail.ProductID = orderDetail.Product.ProductID; context.SubmitChanges(); } 

Try it without InsertOnSubmit, but keep SubmitChanges. I suggest that since you are already adding a record by setting this,

 order.OrderDetails.Add(orderDetail); 

So you probably no longer need to embed it.