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

        context.Products.AddObject(product);

        //Step4 - change EntityState of each category to unchanged

        foreach (var category in product.Categories)

        {

            context.ObjectStateManager

                .ChangeObjectState(category, EntityState.Unchanged);

        }

        //Step5 - Save changes to data store

        context.SaveChanges();

    }

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