2

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;

                }
2
  • You can use saveChanges() at the end and it will save everything Commented Dec 23, 2016 at 19:26
  • 1
    The only issue is that he relies on ID value which is database generated, so this code should be reworked to use navigation properties instead of ID. I described details in my answer. Commented Dec 23, 2016 at 19:28

3 Answers 3

1

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;
}
Sign up to request clarification or add additional context in comments.

3 Comments

Lead_Description is a table and not a class
You instantiate it in your C# code new LEAD_DESCRIPTION so it's obviously a class as well and it is mapped to a table with a same name in a database
This was generated by entityframework. public virtual DbSet<LEAD_DESCRIPTION> LEAD_DESCRIPTION { get; set; }
1

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;
}

Comments

-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();
   }

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.