Entity framework 6 code first Many to many insert slow

Question

I am trying to find a way to improve insert performances with the following code (please, read my questions after the code block):

//Domain classes
[Table("Products")]
public class Product
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Sku { get; set; }

    [ForeignKey("Orders")]
    public virtual ICollection<Order> Orders { get; set; }
    public Product()
    {
        Orders = new List<Order>();
    }
}

[Table("Orders")]
public class Order
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Title { get; set; }
    public decimal Total { get; set; }

    [ForeignKey("Products")]
    public virtual ICollection<Product> Products { get; set; }

    public Order()
    {
        Products = new List<Product>();
    }
}

//Data access

public class MyDataContext : DbContext
{
    public MyDataContext()
        : base("MyDataContext")
    {
        Configuration.LazyLoadingEnabled = true;
        Configuration.ProxyCreationEnabled = true;
        Database.SetInitializer(new CreateDatabaseIfNotExists<MyDataContext>());
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>().ToTable("Products");
        modelBuilder.Entity<Order>().ToTable("Orders");
    }
}

//Service layer
public interface IServices<T, K>
{
    T Create(T item);
    T Read(K key);
    IEnumerable<T> ReadAll(Expression<Func<IEnumerable<T>, IEnumerable<T>>> pre);
    T Update(T item);
    void Delete(K key);
    void Save();
    void Dispose();

    void BatchSave(IEnumerable<T> list);
    void BatchUpdate(IEnumerable<T> list, Action<UpdateSpecification<T>> spec);
}
public class BaseServices<T, K> : IDisposable, IServices<T, K> where T : class
{
    protected MyDataContext Context;
    public BaseServices()
    {
        Context = new MyDataContext();
    }
    public T Create(T item)
    {
        T created;
        created = Context.Set<T>().Add(item);
        return created;
    }

    public void Delete(K key)
    {
        var item = Read(key);
        if (item == null)
            return;
        Context.Set<T>().Attach(item);
        Context.Set<T>().Remove(item);
    }

    public T Read(K key)
    {
        T read;
        read = Context.Set<T>().Find(key);
        return read;
    }

    public IEnumerable<T> ReadAll(Expression<Func<IEnumerable<T>, IEnumerable<T>>> pre)
    {
        IEnumerable<T> read;
        read = Context.Set<T>().ToList();
        read = pre.Compile().Invoke(read);
        return read;
    }

    public T Update(T item)
    {
        Context.Set<T>().Attach(item);
        Context.Entry<T>(item).CurrentValues.SetValues(item);
        Context.Entry<T>(item).State = System.Data.Entity.EntityState.Modified;

        return item;
    }

    public void Save()
    {
        Context.SaveChanges();
    }
}

public interface IOrderServices : IServices<Order, int>
{
    //custom logic goes here
}
public interface IProductServices : IServices<Product, int>
{
    //custom logic goes here
}

//Web project's controller
public ActionResult TestCreateProducts()
    {
        //Create 100 new rest products
        for (int i = 0; i < 100; i++)
        {
            _productServices.Create(new Product
            {
                Sku = i.ToString()
            });
        }
        _productServices.Save();

        var products = _productServices.ReadAll(r => r); //get a list of saved products to add them to orders

        var random = new Random();
        var orders = new List<Order>();
        var count = 0;

    //Create 3000 orders
        for (int i = 1; i <= 3000; i++)
        {
            //Generate a random list of products to attach to the current order
            var productIds = new List<int>();
            var x = random.Next(1, products.Count() - 1);
            for (int j = 0; j < x; j++)
            {
                productIds.Add(random.Next(products.Min(r => r.Id), products.Max(r => r.Id)));
            }

            //Create the order
            var order = new Order
            {
                Title = "Order" + i,
                Total = i,
                Products = products.Where(p => productIds.Contains(p.Id))
            };
            orders.Add(order);
        }
        _orderServices.CreateRange(orders);
        _orderServices.Save();
        return RedirectToAction("Index");
    }

This code works fine but is very VERY slow when the SaveChanges is executed.

Behind the scene, the annotations on the domain objects creates all the relationships needed: a OrderProducts table with the proper foreign keys are automatically created and the inserts are being done by EF properly.

I've tried many things with bulk inserts using EntityFramework.Utilities, SqlBulkCopy, etc... but none worked. Is there a way to achieve this? Understand this is only for testing purposes and my goal is to optimize the best I can any operations in our softwares using EF.

Thanks!

Answer 1

Just before you do your inserts disable your context's AutoDetectChangesEnabled (by setting it to false). Do your inserts and then set the AutoDetectChangesEnabled back to true e.g.;

        try
        {
            MyContext.Configuration.AutoDetectChangesEnabled = false;
            // do your inserts updates etc..
        }
        finally
        {
            MyContext.Configuration.AutoDetectChangesEnabled = true;
        }

You can find more information on what this is doing here

Answer 2

I see two reasons why your code is slow.

Add vs. AddRange

You add entity one by one using the Create method.

You should always use AddRange over Add. The Add method will try to DetectChanges every time the add method is invoked while AddRange only once.

You should add a "CreateRange" method in your code.

public IEnumerable<T> CreateRange(IEnumerable<T> list)
{
    return Context.Set<T>().AddRange(list);
}


var products = new List<Product>();
//Create 100 new rest products
for (int i = 0; i < 100; i++)
{
    products.Add(new Product { Sku = i.ToString() });

}
_productServices.CreateRange(list);
_productServices.Save();

Disabling / Enabling the property AutoDetectChanges also work as @mark_h proposed, however personally I don't like this kind of solution.

Database Round Trip

A database round trip is required for every record to add, modify or delete. So if you insert 3,000 records, then 3,000 database round trip will be required which is VERY slow.

You already tried EntityFramework.BulkInsert or SqlBulkCopy, which is great. I recommend you first to try them again using the "AddRange" fix to see the newly performance.

Here is a biased comparison of library supporting BulkInsert for EF: Entity Framework - Bulk Insert Library Reviews & Comparisons

Disclaimer: I'm the owner of the project Entity Framework Extensions

This library allows you to BulkSaveChanges, BulkInsert, BulkUpdate, BulkDelete and BulkMerge within your Database.

It supports all inheritances and associations.

// Easy to use
public void Save()
{
    // Context.SaveChanges();
    Context.BulkSaveChanges();
}

// Easy to customize
public void Save()
{
    // Context.SaveChanges();
    Context.BulkSaveChanges(bulk => bulk.BatchSize = 100);
}

EDIT: Added answer to sub question

An entity object cannot be referenced by multiple instances of IEntityChangeTracker

The issue happens because you use two different DbContext. One for the product and one for order.

You may find a better answer than mine in a different thread like this answer.

The Add method successfully attach the product, subsequent call of the same product doesn't throw an error because it's the same product.

The AddRange method, however, attach the product multiple time since it's not come from the same context, so when Detect Changes is called, he doesn't know how to handle it.

One way to fix it is by re-using the same context

var _productServices = new BaseServices<Product, int>();
var _orderServices = new BaseServices<Order, int>(_productServices.Context);

While it may not be elegant, the performance will be improved.