Linq2Sql的问题多对多关系和插入新对象

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

我正在尝试做一个简单的linq 2 sql多对多,插入一些数据,操作。

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

这是代表多对多的股票Northwind模型:

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

替代文字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:

所以这是我的(pseduo)代码:

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 :(

在我尝试保存订单之前,我假设我需要先保存产品?我很困惑 :(

2 个解决方案

#1

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

这里有一件事是,您在Order_Detail.Product属性上创建了一个新产品。相反,您应该从数据库中获取产品并将其设置在属性上。

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.

我不确定order.ProductList内部是什么 - 如果这些产品是从数据库加载的,那么你应该直接将它们设置为Order_Detail.Product而不是新的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).

@jfar L2S确实支持多对多关系,你不能在你的订单上拥有一个属性产品(在这种情况下,这实际上是一件好事,因为OrderDetails有Quantity和其他属性)。

#2

Many to Many relationships aren't supported in Linq2Sql. :(

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

奇怪的是,您的数据库架构的图片与其中一篇文章相同......

#1

// Associate each product to the a new order_detail.
orderDetailList.Add(new Order_Detail
{
    Product = new SqlContext.Product
    {
        Foo = product.Foo
    }
});