Using dbcontext.savechanges in a foreach loop

Question

I have this method below that is writing data to two tables in the database. There is a collection I need to write to the database in the foreach section. Is it okay if I call saveChanges in each iteration of the loop or is there a better way of doing this?

         public string SaveInformationToDb(Customerproductdto objDataCollected, List<Productopportunity> objcheckedData)
                {
                    int  generatedLeadDescriptionId = 0;
                    string result = "Failure";

                    using (var dbcontext = new LEADSEntities())
                    {
                        using (var dbContextTransaction = dbcontext.Database.BeginTransaction())
                        {
                            var leadDescription = new LEAD_DESCRIPTION
                            {
                                DETAIL = objDataCollected.LeadDetails,
                                EstimatedRevenue = Convert.ToDecimal(objDataCollected.EstimatedRevenue),
                                CustomerContact = objDataCollected.CustomerContact,
                                CustomerPhone = objDataCollected.CustomerPhone,
                                CustomerEmail = objDataCollected.CustomerEmail
                            };
                            dbcontext.LEAD_DESCRIPTION.Add(leadDescription);
                            dbcontext.SaveChanges();
                            generatedLeadDescriptionId = leadDescription.ID;

                            //process data in the collection
                            foreach (var VARIABLE in objcheckedData)
                            {
                                var leadMetric = new LEAD_METRIC
                                {
                                    EMPLID = objDataCollected.EmployeeNumber,
                                    CustomerNumber = objDataCollected.CustomerNumber,
                                    ProductTypeId = GetLeadProductOpportunityId(VARIABLE.ProductName),
                                    LeadId = generatedLeadDescriptionId
                                };
                                dbcontext.LEAD_METRIC.Add(leadMetric);
                                dbcontext.SaveChanges();
                            }
                            result = "Success";
                            dbContextTransaction.Commit();
                        }               
                    }
                    return result;

                }

Answer 1

Easiest solution is to use another DBContext to insert entity which will return Id and use this Id in outer context

For example

    using (var context = new DatabaseContext())
    {
        ...
        using (var context1 = new DatabaseContext())
        {
            ...
               context1.SaveChanges();
        }                         
        //get id of inserted object from context1 and use is.   
      context.SaveChanges();
   }

Answer 2

The answer to your question of "Is it ok to call SaveChanges() in each iteration..." is yes. There are cases when you may want to and/or need to save an object to the database immediately after creating it, maybe you have a trigger in your database, maybe the next iteration of code depends on the previous object existing, maybe your collection is really large and saving all at once would cause a timeout. Calling SaveChanges() with each iteration is another trip to the database you have to hope works fine and could leave you with a partially saved collection is something goes really wrong.

The answer to your question of "...is there a better way to do it?" is it depends. In most cases, you want to add all of your objects to the context and let Entity Framework deal with getting everything saved correctly.

Here is what I would update your code to with the assumptions that 1. LeadDescriptionId is not setup as an identity property an 2. you don't have or can't create a navigation property between LeadDescription and LeadMetric

public string SaveInformationToDb(Customerproductdto objDataCollected, List<Productopportunity> objcheckedData)
{
    string result = "Failure";

    using (var dbcontext = new LEADSEntities())
    {
        var leadDescription = new LEAD_DESCRIPTION
        {
            DETAIL = objDataCollected.LeadDetails,
            EstimatedRevenue = Convert.ToDecimal(objDataCollected.EstimatedRevenue),
            CustomerContact = objDataCollected.CustomerContact,
            CustomerPhone = objDataCollected.CustomerPhone,
            CustomerEmail = objDataCollected.CustomerEmail
        };
        dbcontext.LEAD_DESCRIPTION.Add(leadDescription);
        dbcontext.SaveChanges();
        int generatedLeadDescriptionId = leadDescription.ID;

        //process data in the collection
        foreach (var VARIABLE in objcheckedData)
        {
            var leadMetric = new LEAD_METRIC
            {
                EMPLID = objDataCollected.EmployeeNumber,
                CustomerNumber = objDataCollected.CustomerNumber,
                ProductTypeId = GetLeadProductOpportunityId(VARIABLE.ProductName),
                LeadId = generatedLeadDescriptionId
            };
            dbcontext.LEAD_METRIC.Add(leadMetric);

        }
        dbcontext.SaveChanges();
        result = "Success";            
    }
    return result;
}

If LeadDescriptionId is an identity property and you can create a navigation property between LeadDescription and LeadMetric then something like this should work

public string SaveInformationToDb(Customerproductdto objDataCollected, List<Productopportunity> objcheckedData)
{
    string result = "Failure";

    using (var dbcontext = new LEADSEntities())
    {
        var leadDescription = new LEAD_DESCRIPTION
        {
            DETAIL = objDataCollected.LeadDetails,
            EstimatedRevenue = Convert.ToDecimal(objDataCollected.EstimatedRevenue),
            CustomerContact = objDataCollected.CustomerContact,
            CustomerPhone = objDataCollected.CustomerPhone,
            CustomerEmail = objDataCollected.CustomerEmail
        };
        dbcontext.LEAD_DESCRIPTION.Add(leadDescription);

        //process data in the collection
        foreach (var VARIABLE in objcheckedData)
        {
            var leadMetric = new LEAD_METRIC
            {
                EMPLID = objDataCollected.EmployeeNumber,
                CustomerNumber = objDataCollected.CustomerNumber,
                ProductTypeId = GetLeadProductOpportunityId(VARIABLE.ProductName),
                LeadId = generatedLeadDescriptionId
            };
            leadDescription.LEAD_METRIC.Add(leadMetric);
        }

        dbcontext.SaveChanges();
        result = "Success";            
    }
    return result;
}

Answer 3

I suppose you have navigation property like public virtual ICollection<LEAD_METRIC> Metrics in your LEAD_DESCRIPTION class. If no, you should add it and configure to be mapped on foreign key. Then you can do like below:

public string SaveInformationToDb(Customerproductdto objDataCollected, List<Productopportunity> objcheckedData)
{
    int  generatedLeadDescriptionId = 0;
    string result = "Failure";

    using (var dbcontext = new LEADSEntities())
    {
            var leadDescription = new LEAD_DESCRIPTION
            {
                DETAIL = objDataCollected.LeadDetails,
                EstimatedRevenue = Convert.ToDecimal(objDataCollected.EstimatedRevenue),
                CustomerContact = objDataCollected.CustomerContact,
                CustomerPhone = objDataCollected.CustomerPhone,
                CustomerEmail = objDataCollected.CustomerEmail
            };

            //process data in the collection
            foreach (var VARIABLE in objcheckedData)
            {
                var leadMetric = new LEAD_METRIC
                {
                    EMPLID = objDataCollected.EmployeeNumber,
                    CustomerNumber = objDataCollected.CustomerNumber,
                    ProductTypeId = GetLeadProductOpportunityId(VARIABLE.ProductName)
                };

                leadDescription.Metrics.Add(leadMetric);
            }
            result = "Success";
            dbcontext.LEAD_DESCRIPTION.Add(leadDescription);
            dbcontext.SaveChanges();
    }
    return result;
}