The right way to insert multiple records to a table using LINQ to Entities

Question

As many of us have done, I set up a simple loop to add multiple records from a databse. A prototypical example would be something like this:

Method I:

// A list of product prices
List<int> prices = new List<int> { 1, 2, 3 };

NorthwindEntities NWEntities = new NorthwindEntities();

foreach (int price in prices)
{
   Product newProduct = new Product();
   newProduct.Price = price;
   NWEntities.Products.AddObject(newProduct);
}

NWEntities.SaveChanges();

When I first set up the loop, however, I intuitively wrote:

Method II:

Product newProduct = new Product();
   
foreach (int price in prices)
{
   newProduct.Price = price;
   NWEntities.Products.Add(newProduct);
}

After doing a little reading, several individuals mentioned that if the Method II is used only one record would be added to the table. This seems counter intuitive. It's the Add() function which load a new insertion, and, I would think, creates an object after each call with the data passed in. Declaring my Product object outside the loop would seem to better utilize resources, as the only overhead consumed in each call would be the re-assignment of the object instance property, and not the re-construction of the object instance itself.

Can anyone please clarify? I could not find another post that deals with this question directly. If one is out there please point to it.

Answer 1

Simply move the instantiation of the new Product inside the loop. Your code as it is written will add a single instance multiple times which does not produce what you are after...you need a separate instance of each product...the Add method does not make a copy, it attaches the object to the context and marks it for insertion.

foreach (int price in prices)
{
   Product newProduct = new Product();
   newProduct.Price = price;
   NWEntities.Products.Add(newProduct);
}

To see what is happening a bit more explicity consider the following:

class Program
{
    static void Main(string[] args)
    {
        Console.WriteLine("Try to reuse same Instance:");
        using (var ctx = new AdventureWorksEntities())
        {
            List<int> ids = new List<int> {1, 2, 3}; 
            Product p1 = new Product();
            Product reference = p1;
            Product p2;
            Console.WriteLine("Start Count: {0}", ctx.Products.Count());
            foreach (var id in ids)
            {
                p1.ProductID = id;
                p2 = ctx.Products.Add(p1);
                Console.WriteLine("p1 = p2 ? {0}", p1 == p2);
                Console.WriteLine("p2 = reference? {0}", p2 == reference);
                Console.WriteLine("State: {0}", ctx.Entry(p1).State);
                var changes = ctx.ChangeTracker.Entries<Product>();
                Console.WriteLine("Change Count: {0}", changes.Count());
            }
        }
        Console.WriteLine();
        Console.WriteLine("Distinct Instances:");
        using (var ctx = new AdventureWorksEntities())
        {
            List<int> ids = new List<int> { 1, 2, 3 };
            Product p2;
            foreach (var id in ids)
            {
                var p1 = new Product {ProductID = id};
                p2 = ctx.Products.Add(p1);
                Console.WriteLine("p1 = p2 ? {0}", p1 == p2);
                Console.WriteLine("State: {0}", ctx.Entry(p1).State);
                var changes = ctx.ChangeTracker.Entries<Product>();
                Console.WriteLine("Change Count: {0}", changes.Count());
            }
        }

        Console.ReadLine();
    }
}

In the first loop you are reusing the same product instance, but when you add it to the context you are just using the same reference each time. You can see that the change count remains at 1 regardless of how many times the loop is executed. Of course only the last values would be saved if you were to call ctx.SaveChanges().

In the second version, the change count is correctly incremented each time and you would be calling SaveChanges would save all of distinct entities as you would expect.

Answer 2

+1 For Terryt's answer. You need to stick with method one or something similar.

In Entity framework 6 version, there is a new method to add a set of data in a single statement. This is the AddRange Method.

I would like to add that I find the AddRange method elegant when you want to add entities based on an existing list (or IEnumerable).

In your case it could be done somewhat like this:

NWEntities.Products.AddRange(
    Prices.Select(priceitem =>
    new Product{price = priceitem})
)

Semantically this should be similar to your method 1. One Product object is instanciated per price in the price list. There is however one difference, it is done anonymously, so there are no explicit defined reference variables pointing to the new object.

If performance is important then this question might give you some further information: Fastest Way of Inserting in Entity Framework

Hope this give you some help.

Answer 3

We wouldn't need the help of loop. We can do this by linq. As by the below code, names are have to add into Employee table from the nameList with the bit field IsDeleted.

db.Employee.AddRange(
   nameList.Select(name =>
      new Employee
      {
           Name = name,
           IsDeleted = false
      })
   );

Answer 4

I had a similar problem. In my issue, I had this code:

        var cratelist = db.TruckContainerLoads.Where(x => x.TruckID == truckid).Select(x => x.ContainerID);
        if (!cratelist.Any())
        {
            return;
        }
        foreach (var crateid in cratelist) {
            TruckContainerLoad crInstance = new TruckContainerLoad();
            crInstance.ContainerID = crateid;
            try
            {
                db.TruckContainerLoads.Add(crInstance);
                db.SaveChanges();
            }
            catch
            {
                return;
            }
        }

My query only added the first record in my foreach. The issue was that I needed to call my db.SaveChanges() outside of the foreach loop, after adding multiple records. For me, the answer to my issue was actually in the question. So, I'm upvoting the question.