Monday, March 5, 2012

Entity Framework - INSERT operation with many to many relations

Consider following example (Figure 1) of many-to-many relationship between Category and Product tables. ProductCategory is a mapping table to maintain many to many relations between tables. This table does not have any payload columns (table has only two foreign keys).

When these tables are added in EDMX, entity framework does not create entity type for ProductCategories. Category tables maintains list of related products and product table maintains list of related categories, which allows handling many to many relations between both tables. See figure 2.

At moment, both Category and Product table has data shown in below figure 3.

Now consider example of adding new product (Product 5) which is associated with Category 1, Category 3 and Category 5. So let’s see how new product can be added using entity framework and associate it with existing categories.

    //Step1 - Creating new product

    var product = new Product();

    product.Name = "Product 5";

    //Step2 - Adding category object with primary key values for existing categories,

    //to associate them with new product

    product.Categories.Add(new Category() { CategoryId = 1 });

    product.Categories.Add(new Category() { CategoryId = 3 });

    product.Categories.Add(new Category() { CategoryId = 5 });

    using (var context = new ProductsEntities())


        //Step3 - Add new product to context


        //Step4 - change EntityState of each category to unchanged

        foreach (var category in product.Categories)



                .ChangeObjectState(category, EntityState.Unchanged);


        //Step5 - Save changes to data store



How this works:

·        Step1 is a normal step which creates new Product object and sets its all properties.

·        Step2 create list of associated Categories for this product. Here aim is not to create brand new categories but associate existing categories with new product. Instances of category class are added to product.Categories with CategoryId (Primary key) values which forms foreign key relation in ProductCategory table.

·        Step3 is again a normal step to add new product in entity context to mark this as added object. Remember when AddObject() method is called, it changes a state of entity (product) and all objects in its object tree to Added. After this step if we directly run step5, it will try to INSERT three brand new categories with key 1, 3 and 5 in Category table and will throw primary key violation error since those keys already existing in Category table. If such categories does not existing in Category table, it will create new Categories first and then create new product with categories associated with it.

·        Step4 – At this point all categories in product.Categoires have EntityState added, which we do not want. We just want to associate existing categories with product. We are not making any change to such categories. So this step explicitly sets their EntityState to unchanged.

·        Step5 – It saves new product and its foreign keys in ProductCategogy table


Anonymous said...

Good Post!

Rodrigo GutiƩrrez said...

I've been looking for guidance on this kind of many to many issues for a while....thanks

Rodrigo GutiƩrrez said...

I get this error: AcceptChanges cannot continue because the object's key values conflict with another object in the ObjectStateManager. Make sure that the key values are unique before calling AcceptChanges.

Instead of products and categories, I am dealing with students and courses and I need to add new students to existing courses using a Pure Junction Table. Advice??

wclub3 said...

I need to clarify that your solution works just fine. It's just that I was adding extra code to find if a cotegory already existed in the DB and that code was bringing the category back into Added state.
Thanks again for your solution.

Prashant said...

Thanks for sharing !!! I am going to bookmark this.

vietboox said...

Thank you for your post. This helps me very much!

Sashenka Hettiarachchi said...

Thanks alot for this post...

Anonymous said...

Thanks for this very helpful post. I do have a question about an update operation. I need update the product which used to be associated with category 1, 3, and 5 so that is now associated with category 2 and 4. Can you post an update that shows how to make this work? I tested this in a slightly different scenario and when I mark all the category objects as unmodified I never see any database updates to the association table at all so it leaves the product associated with the wrong categories.

Anonymous said...

You saved my day.

Anonymous said...

Wow 3 days and I found you ... Thanks a bunch ...

Anonymous said...

Is there an updated version of this for entityframeworktutorial 6.1?

Anonymous said...

Damn. After a couple of days searching the web and asking for help your post is the one that got me going.

Do note that nowadays you can just use 'Add' instead of 'AddObject'.


Anonymous said...

Also you do not need to touch the ObjectStateManager anymore. It works automagicially!

EF 6.1