Linq到SQL ForeignKeyReferenceAlreadyHasValueException [英] Linq to SQL ForeignKeyReferenceAlreadyHasValueException

问题描述

我看了几个线程，但没有找到适合我目的的解决方案 (至少我不知道如何去实现它)

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)

我有一个WCF服务，该服务使用Linq to SQL来检索，更新和删除SQL Server上的对象.

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

我创建了一个简单的关系数据库，其中包含一个 客户与订单之间存在一对多的关系， Order和OrderDetails之间存在1对多的关系，

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,

现在我的订单"具有用于CustomerID的外键，而OrderDetails具有用于订单ID的外键.

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

但是OrderDetails在产品"表中还包含产品ID的FK.

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

基本上我现在想做的是使用OrderID修改OrderDetails并使用ProductID添加另一种产品.

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

尽管我不断收到ForeignKeyReferenceAlreadyHasValueException，但我遇到了问题

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

我写的这篇文章我完全知道是错误的，但是当时我还不知道(我对SQL完全陌生，对SQL还是Linq完全陌生).

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

也许有人可以显示，如果问得不多，请解释一下如何使用现有的OrderID(FK)将新的OrderDetail记录正确插入到我的OrderDetails表中，以便编辑并添加/从现有订单中删除产品"

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"

预先感谢您的帮助

约翰

推荐答案

好的，所以您会收到此错误，

OK, so you are getting this error,

http://msdn.microsoft.com/zh-CN/library/system.data.linq.foreignkeyreferencealreadyhasvalueexception.aspx

ForeignKeyReferenceAlreadyHasValueException

ForeignKeyReferenceAlreadyHasValueException

有关链接的内容，

表示当实体已加载时尝试更改外键时发生的错误.

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

我认为您需要做的是加载您正在谈论的Order，它将有与之相关的OrderDetails列表.如果要删除这些引用之一，则需要从OrderDetails列表中删除OrderDetail.

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.

我认为您需要这样做，

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

尝试不使用InsertOnSubmit，但仍保留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.

这篇关于Linq到SQL ForeignKeyReferenceAlreadyHasValueException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！