none
Code First Many To Many Update not working RRS feed

  • Question

  • Dear All,

    I am trying to perform a simple task but its turning out to be a hair pulling experience.

    I am using EF 4.1 Code first and have a simple many to many scenario involving two Entities (Product and Category). I have a join table (ProdcutCategories) which contains primary keys of both the tables. Classic many to many scenario. Now I want to update the product and update its categories. Here is what I have

     public class Product 
        {
            public Product()
            {
                Categories = new List<Category>();           
            }

            public virtual IList<Category> Categories { get; set; }
            public Int64 ID { get; set; }
            public String Title { get; set; }
            public String Name { get; set; }    

           public Double Price { get; set; }    

        }

    public class Category 
        {
            public Category()
            {
                Products = new List<Product>();
            }

            public virtual IList<Product> Products { get; set; }
            public Int64 ID { get; set; }
            public String Title { get; set; }
            public String Name { get; set; }
            public String Description { get; set; }
        }

    Here is my configuration 

    public CategoryConfig()
            {
                ToTable("Categories");
                HasKey(c => c.ID);            
                HasMany(c => c.Products).WithMany(p => p.Categories).Map(m =>
                                                                             {
                                                                                 m.MapLeftKey("CategoryID");
                                                                                 m.MapRightKey("ProductID");
                                                                                 m.ToTable("ProductCategories");
                                                                             });
            }

         [TestMethod]
            public void UpdateProduct()
            {
                var product = new Entities.Product {ID = 26, Name = "Test", Title= "Test", Price=500};


                var productService = new ProductService();
                var category1 = new Category() { ID = 1};
                var category2 = new Category() { ID = 2 };
                product.Categories.Add(category1);
                product.Categories.Add(category2);


                Boolean updated = productService.Update(product);
                Assert.IsTrue(updated);
            }

    public Boolean Update(Product entity)
    {
                _Context.Entry<Product>(entity).State = EntityState.Modified;
                return _Context.SaveChanges() >=  1;
    }

    What I am expecting is that the product should now be associated with two categories and there should be two entries in ProductCategories table, but it is not working and no record is inserted in the ProductCategories table.

    Any help will be extremely appretiated.

    Afraz Ali


    • Edited by Afraz Ali Monday, February 27, 2012 3:31 PM error
    Monday, February 27, 2012 3:30 PM

Answers

  • H Afraz;

    To your statement, "Finally I have tried your code but it tries to add a new Category in the category table, which is not what I want.", the following is the code you posted please note that you are adding two new Category entities and not existing entities to the new Product entity.

    var productService = new ProductService();
    var category1 = new Category() { ID = 1};
    var category2 = new Category() { ID = 2 };
    product.Categories.Add(category1);
    product.Categories.Add(category2);

    If you want to add exiting Category's entities to the new Product entity you need first to query the DB for the entities you wish to relate to Product entity. For example :

    // Get the Category to add to Product 
    var cats = _Context.Category.Where( c => c.ID == 1 || c.ID == 2).ToList();
    // Add the Category to the Product
    product.Categories.AddRange(cats);
    // Do the update
    Boolean updated = productService.Update(product);

    To your question, "Also I want to update the product data not add another product in the product table.", the same thing here. Do not create a new Product you need to query the Product table for the product to be updated and then update its fields somewhat like above.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Afraz Ali Wednesday, March 7, 2012 10:24 AM
    Tuesday, February 28, 2012 4:09 PM
  • Hi Afraz Ali,

    Welcome to MSDN Forum.

    As @Fernando mentioned, if you want to do this, you can query the three entities out, then add the two categories to the product entity's category collection. After that, calling SaveChanges() method to save the data into database. Below is the code, please refer to it.

    using (myContext context = new myContext())
                {
                    Category cate1 = (from c in context.cates where c.ID == 1 select c).First();
                    Category cate2 = (from c in context.cates where c.ID == 2 select c).First();
                    Product pro = (from p in context.pros where p.ID == 1 select p).First();
                    pro.Categories.Add(cate1);
                    pro.Categories.Add(cate2);
                    context.SaveChanges();
                    Console.Write("OK");
                    Console.Read();
    
                }

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us


    Monday, March 5, 2012 8:09 AM
    Moderator

All replies

  • H Afraz;

    Please see the comments in the code snippet below.

    [TestMethod]
    public void UpdateProduct()
    {
       // In the database the ID field is and Identity filed and is supplied by the db
       // var product = new Entities.Product {ID = 26, Name = "Test", Title= "Test", Price=500};
       var product = new Entities.Product {Name = "Test", Title= "Test", Price=500};
       
       // Do not know what this is
       var productService = new ProductService();
       // In the database the ID field is and Identity filed and is supplied by the db
       // so initialize some of the other fields
       // var category1 = new Category() { ID = 1};
       // var category2 = new Category() { ID = 2 };
       var category1 = new Category() { Name = "1"};
       var category2 = new Category() { Name = "2" };
       product.Categories.Add(category1);
       product.Categories.Add(category2);
    
    
       Boolean updated = productService.Update(product);
       Assert.IsTrue(updated);
    }
    
    public Boolean Update(Product entity)
    {
        // The entity needs to be added to the DbContext and its EntityState will be Added
        // _Context.Entry<Product>(entity).State = EntityState.Modified;
        _Context.Product.Add(entity);
        return _Context.SaveChanges() >=  1;
    }
    


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, February 27, 2012 6:29 PM
  • Hi Fernando,

    Thanks a lot for your reply. Just to clarify your questions.

    Yes ID field is the primary key in both the entities. Secondly ProductService class is just my Service Layer class that in this case is simply calling my DAL.

    Finally I have tried your code but it tries to add a new Category in the category table, which is not what I want. You see in my case categories with ID 1 & 2 are already present in the DB and I only want to add these two IDs in the many to many table i.e ProductCategories. Also I want to update the product data not add another product in the product table.

    Hope I make sense

    Best Regards,

    Afraz

    Tuesday, February 28, 2012 6:14 AM
  • H Afraz;

    To your statement, "Finally I have tried your code but it tries to add a new Category in the category table, which is not what I want.", the following is the code you posted please note that you are adding two new Category entities and not existing entities to the new Product entity.

    var productService = new ProductService();
    var category1 = new Category() { ID = 1};
    var category2 = new Category() { ID = 2 };
    product.Categories.Add(category1);
    product.Categories.Add(category2);

    If you want to add exiting Category's entities to the new Product entity you need first to query the DB for the entities you wish to relate to Product entity. For example :

    // Get the Category to add to Product 
    var cats = _Context.Category.Where( c => c.ID == 1 || c.ID == 2).ToList();
    // Add the Category to the Product
    product.Categories.AddRange(cats);
    // Do the update
    Boolean updated = productService.Update(product);

    To your question, "Also I want to update the product data not add another product in the product table.", the same thing here. Do not create a new Product you need to query the Product table for the product to be updated and then update its fields somewhat like above.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Afraz Ali Wednesday, March 7, 2012 10:24 AM
    Tuesday, February 28, 2012 4:09 PM
  • Hi Fernando,

    Thanks for your reply. Actually I am not adding the categories to the Product in the repository. I am only adding them and sending them to the repository class which then tries to execute the following function.

    public override Boolean Update(Product entity)
            {
                var categoreis = _Context.Set<Category>().Where(c => c.ID == 1 || c.ID == 2).ToList();
                entity.Categories.AddRange(categoreis);
                _Context.Entry<Product>(entity).State = EntityState.Modified;
                return _Context.SaveChanges() >=  1;
            }

    Here suppose we are trying to add hard coded categories with IDs 1 & 2 for the product. Then I believe this code should update the ProductCategory Many to Many table. But it does not. Do you see anything wrong with the above code.

    Best Regards,

    Afraz


    • Edited by Afraz Ali Wednesday, February 29, 2012 10:11 AM
    Wednesday, February 29, 2012 10:06 AM
  • H Afraz;

    Any time you make a change to an entity that is part of the ObjectConext you do not need to execute a line of code to change its state to Modified as the following line of code does.

    _Context.Entry<Product>(entity).State = EntityState.Modified;

    If you are stating that it is not working can you please zip the project up and upload to the web where I can download it or if you can not do that if you can create a sample project that reproduces the problem where I can test the code. You can use Windows Live Skydrive to upload the file.

       


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Wednesday, February 29, 2012 12:35 PM
  • Hi Fernando,

    Thanks a lot for your reply. I have uploaded the code on Skydrive but can't find your email id to share code with. Kindly tell me from where (profile) can I get your email to share code with you.

    Best Regards

    Wednesday, February 29, 2012 3:10 PM
  •  

    All you need to do is place the zip file into a public folder and when you save the file to the public folder you will be given a URL that you can share. Post that link here.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Wednesday, February 29, 2012 8:44 PM
  • Hi Fernanado,

    Thanks for your reply. I have uploaded the code here

    https://skydrive.live.com/redir.aspx?cid=7cca892891f94f3a&resid=7CCA892891F94F3A!187&parid=root

    Please note that I have already created the product and I only want to update that product and add existing categories to its many to many table. I don't want to add new products or categories. So in my case I will only be updating the Product table and ProductCategories table.

    The code you will be interested in is in ProductRepositoryTest.cs and ProductRepository.cs files

    Best Regards,

    Afraz Ali


    • Edited by Afraz Ali Thursday, March 1, 2012 4:48 AM
    Thursday, March 1, 2012 4:47 AM
  • Sorry I just don;t understand why it is not working. Maybe when the moderator drops in he will be able to shed some light on the subject.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Friday, March 2, 2012 4:29 PM
  • Hi Fernando,

    Thanks for your reply. Have you gone through the code? I am beginning to feel that maybe this scenario is not supported by EF. Kindly guide me as to where else can I post this problem?

    Best Regards,

    Afraz Ali

    Saturday, March 3, 2012 7:03 AM
  • H Afraz;

    If I was working on this project I would query the db for the three entities in this case and then add the two Category entities to the Product entity and update all needed fields then do a SaveChanges.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Saturday, March 3, 2012 1:53 PM
  • Hi Fernando,

    If you could kindly post me code to show how we can implement this scenerio, I will be extremely thankful. I would really like to not delete and then add new entries to the categories table if that is possible.

    Best Regards,

    Afraz 

    Saturday, March 3, 2012 3:31 PM
  • Hi Afraz Ali,

    Welcome to MSDN Forum.

    As @Fernando mentioned, if you want to do this, you can query the three entities out, then add the two categories to the product entity's category collection. After that, calling SaveChanges() method to save the data into database. Below is the code, please refer to it.

    using (myContext context = new myContext())
                {
                    Category cate1 = (from c in context.cates where c.ID == 1 select c).First();
                    Category cate2 = (from c in context.cates where c.ID == 2 select c).First();
                    Product pro = (from p in context.pros where p.ID == 1 select p).First();
                    pro.Categories.Add(cate1);
                    pro.Categories.Add(cate2);
                    context.SaveChanges();
                    Console.Write("OK");
                    Console.Read();
    
                }

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us


    Monday, March 5, 2012 8:09 AM
    Moderator
  • Hi Allen Li,

    Thanks a lot for your reply. I guess I miss understood what @Fernando was mentioning. I have tried your code and it works perfectly also I have revisited @Fernando reply and now it makes sense to me as to what he was trying to say. Thanks a lot for you post.

    @Fernando Thanks for your reply also. I guess I missed your point due to my misunderstanding.

    Best Regards

    Afraz Ali 

    Wednesday, March 7, 2012 10:23 AM
  •  

    Not a problem, glad to help.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Wednesday, March 7, 2012 2:54 PM