Linq to SQL ForeignKeyReferenceAlreadyHasValueException

Question

I had a look at several threads but I am not seeing the solution to fit my purpose (at least I don't understand how I could go about implementing it)

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

I have created a simple Relational database which has a 1 to many relationship between Customer and Order, 1 to many relationship between Order and OrderDetails,

now My Orders has a Foreign key for CustomerID, and OrderDetails has a Foreign kwy for Order ID.

however OrderDetails also contains a FK for ProductID in a Products Table.

Basically what I am trying to do right now is amend OrderDetails using the OrderID and adding another product using ProductID.

I am having problems with that though as I keep receiving the ForeignKeyReferenceAlreadyHasValueException

I have written this which I know is completely wrong but at the time I wasn't aware (I am completely new to SQL, Linq to SQL etc) that I cant 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 had to map out the entity or something along those lines using a common line of code such as this

            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();

could someone perhaps show and if its not too much to ask explain a little as to how I can correctly go about inserting a New OrderDetail Record into my OrderDetails table using an existing OrderID (FK) so as to "Edit and add/remove a Product to an Existing Order"

Thanks in advance for your help

John

Answer 1

OK, so you are getting this error,

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

ForeignKeyReferenceAlreadyHasValueException

And the link says this about it,

Represents errors that occur when an attempt is made to change a foreign key when the entity is already loaded.

I think what you need to do is load the Order that you are talking about, and it will have a list of OrderDetails associated with it. If you want to remove one of those references you need to remove the 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 still keep the SubmitChanges. I suggest that because you are already adding the record by setting this,

order.OrderDetails.Add(orderDetail);

So you probably don't need to insert it again.