EF4.1 Code First - How to Assign/Remove from Many-To-Many?

I have a Many to Many relationship between Products and ProductGroups.

Using EF4.1 Code First, I'm getting strange and incosistent results when adding/removing Products to/from a ProductGroup.

My view works perfectly; it retunrs a GroupId and a List productIds. The problem is in the controller where I loop through the list of productIds and assign/remove to a ProductGroup.

Here's an extract from my code to remove Products from a ProductGroup:

    ProductGroup productGroup = _Repository.GetProductGroup(groupId);
    using (var db = GetDbContext())
    {

        foreach (var pId in productIds)
        {
            Product p = _Repository.GetProduct(Conve开发者_运维问答rt.ToInt32(pId));

            productGroup.Products.Remove(p);
            db.Entry(productGroup).State = System.Data.EntityState.Modified;

            p.ProductGroups.Remove(productGroup);
            db.Entry(p).State = System.Data.EntityState.Modified;

            db.SaveChanges();                       
        }
    }

Basically, I have have to affect both the ProductGroup and individual Products to get any result... and then the results are mixed. For example, when inspecting the DB table (ProductGroupProducts) only some records will get removed, but I can't figure out the pattern of which are and which aren't.

I have similar issues when assigning a Products to a ProductGroup. The code is almost identical with the obvious .Add() instead of .Remove().

What am I missing here? Anybody know of a better, and hopefully more consistent, way of doing this?

Many thanks in advance!

Radu

What is GetDbContext()? If this creates a new context then db is obviously another context than the context you are using in _Repository. (If it returns the same context as _Repository is using then the using block is weird because it disposes the context at the end and therefore destroys also the context in _Repository).

You must attach the productGroup and p to the context where you are doing the modifications in:

ProductGroup productGroup = _Repository.GetProductGroup(groupId);
using (var db = GetDbContext())
{
    db.ProductGroups.Attach(productGroup);
    foreach (var pId in productIds)
    {
        Product p = _Repository.GetProduct(Convert.ToInt32(pId));
        db.Products.Attach(p);

        productGroup.Products.Remove(p);
    }
    db.SaveChanges();                       
}

I've removed p.ProductGroups.Remove(productGroup) because I think EF will do that automatically when you remove the product from the group (but I'm not sure; you can watch the collections in the debugger to see.)

If GetDbContext() indeed creates a new context rethink the design. You should only have one context for such operations (reading and updating).

Edit

This is possibly easier:

ProductGroup productGroup = _Repository.GetProductGroup(groupId);
using (var db = GetDbContext())
{
    db.ProductGroups.Attach(productGroup);
    foreach (var pId in productIds)
    {
        var p = productGroup.Products
            .SingleOrDefault(p1 => p1.ID == Convert.ToInt32(pId))

        if (p != null)
            productGroup.Products.Remove(p);
    }
    db.SaveChanges();                       
}

It saves you the database query for the product. I'm assuming that you either using lazy loading or that _Repository.GetProductGroup has an Include for the products collection.