Adding & Removing Associations - Entity Framework

Question

I'm trying to get to grips with EF this week and I'm going ok so far but I've just hit my first major snag. I have a table of items and a table of categories. Each item can be 'tagged' with many categories so I created a link table. Two columns, one the primary ID of the item, the other the primary ID of the category. I added some data manually to the DB and I can query it all fine through EF in my code.

Now I want to 'tag' a new item with one of the existing categories. I have the category ID to add and the ID of the Item. I load both as entities using linq and then try the following.

      int categoryToAddId = Convert.ToInt32(ddlCategoriesRemaining.SelectedValue);
  var categoryToAdd = db.CollectionCategorySet.First(x => x.ID == categoryToAddId);
  currentCollectionItem.Categories.Add(categoryToAdd);
  db.SaveChanges();

But I get "Unable to update the EntitySet 'collectionItemCategories' because it has a DefiningQuery and no element exists in the element to support the current operation."

Have I missed something? Is this not the right way to do it? I try the same thing for removing and no luck there either.

Answer 1

After you have created your Item object, you need to set the Item object to the Category object on the Item's Categories property. If you are adding a new Item object, do something like this:

Using (YourContext ctx = new YourContext())
{
   //Create new Item object
   Item oItem = new Item();
   //Generate new Guid for Item object (sample)
   oItem.ID = new Guid();
   //Assign a new Title for Item object (sample)
   oItem.Title = "Some Title";
   //Get the CategoryID to apply to the new Item from a DropDownList
   int categoryToAddId = Convert.ToInt32(ddlCategoriesRemaining.SelectedValue);
   //Instantiate a Category object where Category equals categoryToAddId
   var oCategory = db.CategorySet.First(x => x.ID == categoryToAddId);
   //Set Item object's Categories property to the Category object
   oItem.Categories = oCategory;
   //Add new Item object to db context for saving
   ctx.AddtoItemSet(oItem);
   //Save to Database
   ctx.SaveChanges();
}

Answer 2

Have you put foreign keys on both columns in your link table to the item and the category or defined the relationship as many to many in the Mapping Details?

Answer 3

I think I have managed to answer this one myself. After alot of digging around it turns out that the Entity Framework (as it comes in VS2008 SP1) doesn't actually support many to many relationships very well. The framework does create a list of objects from another object through the relationship which is very nice but when it comes to adding and removing the relationships this can't be done very easily. You need to write your own stored procedures to do this and then register them with Entity Framework using the Function Import route.

There is also a further problem with this route in that function imports that don't return anything such as adding a many to many relationship don't get added to the object context. So when your writing code you can't just use them as you would expect.

For now I'm going to simply stick to executing these procedures in the old fashioned way using executenonquery(). Apparently better support for this is supposed to arrive in VS2010.

If anyone feels I have got my facts wrong please feel free to put me right.