Entity Framework multi table update in a transaction

Question

When user submits a form, the data should go to multiple table with referential integrity. Some data represent an entity and call saveChanges() to obtain the id from this call. Then we need to form another entity with the id returned(foreign key on this table) and insert a row. This is happening in transaction.

using (var context = new XyzContext())
{
    var product = new ProductModel();
    product.Description = "Test";
    product.percentage = "20";

    using (var transactionContext = context.Database.BeginTransaction())
    {
        context.Products.Add(product);                                                            
        ObjectContext saveContext = (context as IObjectContextAdapter).ObjectContext;                            
         int recordCount = saveContext.SaveChanges(SaveOptions.DetectChangesBeforeSave);

         if (recordCount > 0)
         {
             this.ID = product.ID;
             // Saving a audit record
             SaveHistory(userId, product.ID, ref saveContext);

             var size = new Container();
             size.Product_Id = product.ID;
             //size.Save(new List<string>() { "10", "20L", "30 Gallon" });      
             size.Save(context, new List<string>() { "10", "20L", "30 Gallon" });      
          }                                                 
          transactionContext.Commit();                           
       }                        
   }

public void Save(XyzContext context, List<string> input)
//public void Save(List<string> input)
{
    //using (var context = new Coda_Context())
    //{
        // Delete all the containers for this product_id                            
        var products = context.Container.Where(p => p.Product_Id == this.Product_Id).ToList();
        context.Product_Container.RemoveRange(products);
        context.SaveChanges();    

        // Add the passed-in containers for this product_id
        input.ForEach(i =>
        {
            var newSize = new ContainerModel()
            {
                Product_Id = this.Product_Id,
                Container_Size_Id = i
            };
            context.Container.Add(newSize);
        });
        context.saveChanges();

   // }  
 }

I have shown the two approaches in the code above. one is commented. It should insert/update one row to Product table and multiple rows to Container table. It is inserting multiple product rows with one approach and with new context, it throws timeout error.

Please advise.

Any help? Is the information provided not enough? – Programmer Sep 12 '18 at 20:08 — Programmer, Sep 12 '18 at 20:08

score 1 · Answer 1 · answered Sep 25 '18 at 18:46

You can perform this operation by executing one update query for each element in the list. I don't think it's possible to perform multiple update operations with one update query for all elements. More information here - Batch update/delete EF5

var ProductList=new List<string>();
using (var db=new MyContext())
{
    var container = db.Container.Where(c=>ProductList.Contains(c.ID)).ToList();
    Containers.ForEach(p =>
    {
        p.property1 = value1;
        p.property2 = value2;
    });
    db.SaveChanges();
}

Entity Framework multi table update in a transaction

1 Answers1