Why is the Entity Framework so slowly adding multiple elements to a single SaveChanges ()?

Question

This is a continuation of the previous question , in which I tried to find out the main reason why my code is slow. I think I narrowed it down to the minimum example below. I have a basic database structure as follows:

public class Foo
{
    public int Id { get; set; }
    public string Bar { get; set; }
}

public class FooContext : DbContext
{
    public DbSet<Foo> Foos { get; set; }
}

Now, if I had a list of objects Fooand wanted to add them to the database, the proposed method would be to use AddRange(). But I noticed that it takes a lot of time, and it is not much affected by the number of items in the collection, even with a small number, for example, 200. So I wrote it manually, and alt, it works faster!

class Program
{
    static void Main(string[] args)
    {
        var foos = Enumerable.Range(0, 200).Select(index => new Foo { Bar = index.ToString() });

        // Make sure the timing doesn't include the first connection
        using (var context = new FooContext())
        {
            context.Database.Connection.Open();
        }

        var s1 = Stopwatch.StartNew();
        using (var context = new FooContext())
        {
            context.Foos.AddRange(foos);
            context.SaveChanges();
        }
        s1.Stop();

        var s2 = Stopwatch.StartNew();
        using (var context = new FooContext())
        {
            // Ignore the lack of sanitization, this is for demonstration purposes
            var query = string.Join(";\n", foos.Select(f => "INSERT INTO Foos ([Bar]) VALUES (" + f.Bar + ")"));
            context.Database.ExecuteSqlCommand(query);
        }
        s2.Stop();

        Console.WriteLine("Normal way: {0}", s1.Elapsed);
        Console.WriteLine("Hard way  : {0}", s2.Elapsed);
        Console.ReadKey();
    }
}

, Entity Framework , SQL , . , ?

Answer 1

, : http://www.codinghelmet.com/?path=howto/bulk-insert

:

, , SQL:

. , - , ​​ ( , ). , 200 , 400 sql .

, EF . , 200 . .

: " EF , ". , EF :

• : .
• . - , , , . , EF . , , .

, , , , SqlBulkCopy. .

, , , , , , EF alot, .

Answer 2

, Entity Framework , , , SQL-?

, Entity Framework , . , , , . , , , , .

Answer 3

, SaveChangesAsync()?

I searched everywhere to find a way to disable primary key synchronization, but could not find it for EF 6 or less.

The EF core passes the true value from DBContext.SaveChanges () to what, in my opinion, causes this synchronization. Another congestion allows callers to pass false as a control parameter.