Adding and Removing Associations - Entity Framework

Question

This week I am trying to deal with EF, and so far everything is going well, but I just hit my first major mistake. I have a table of elements and a table of categories. Each element can be marked with many categories, so I created a link table. Two columns, one is the primary identifier of the element, the other is the primary identifier of the category. I added some data manually to the database, and I can query all this through EF in my code.

Now I want to "tag" a new element in one of the existing categories. I have a category identifier to add and an element identifier. I load as objects 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 "Failed to update EntitySet" collectionItemCategories "because it has DefiningQuery and there is no element in the element to support the current operation."

Did I miss something? Is this not so? I am trying to do the same for deletion and you are out of luck.

Answer 1

I think I managed to answer this question myself. After digging around a lot, it turns out that the Entity Framework (as it happens in VS2008 SP1) does not actually support many many relationships very well. A structure creates a list of objects from another object through relationships that are very enjoyable, but when it comes to adding and removing relationships, this cannot be done very easily. To do this, you need to write your own stored procedures, and then register them in the Entity Framework using the Import Functions route.

There is also an additional problem with this route in that the import of functions that do not return anything, for example, adding many of the many relationships, are not added to the object context. Therefore, when you write code, you cannot just use them, as you would expect.

Currently, I'm going to just stick to these procedures in the old fashioned way using executenonquery (). Apparently, the best support for this should come in VS2010.

If someone feels that my facts are wrong, feel free to correct me.

Answer 2

After you create the Item object, you need to set the Item object for the Category object in the Item Categories property. If you add 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 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 3

Did you put foreign keys in both columns in your table of links to the item and category, or did you define the relationship for many in Cartographic Data?