Entity Framework bulk insert with updating rows from another table

Question

I'm using Microsoft SQL Server and Entity Framework. I have N (for example 10 000) items to insert. Before inserting each item I need to insert or update existing group. It doesn't work well because of low performance. It's because I'm generating too many queries. Each time in loop I'm looking for group by querying Groups table by three (already indexed) parameters.

I was thinking about querying first all groups by using WHERE IN query (Groups.Where(g => owners.Contains(g.OwnerId) && .. ), but as I remember such queries are limited by number of parameters.

Maybe I should write a stored procedure?

Here is my example code. I'm using IUnitOfWork pattern for wrapping the EF DbContext:

public async Task InsertAsync(IItem item)
{
    var existingGroup = await this.unitOfWork.Groups.GetByAsync(item.OwnerId, item.Type, item.TypeId);

    if (existingGroup == null)
    {
        existingGroup = this.unitOfWork.Groups.CreateNew();
        existingGroup.State = GroupState.New;
        existingGroup.Type = item.Code;
        existingGroup.TypeId = item.TypeId;
        existingGroup.OwnerId = item.OwnerId;
        existingGroup.UpdatedAt = item.CreatedAt;

        this.unitOfWork.Groups.Insert(existingGroup);
    }
    else
    {
        existingGroup.UpdatedAt = item.CreatedAt;
        existingGroup.State = GroupState.New;

        this.unitOfWork.Groups.Update(existingGroup);
    }

    this.unitOfWork.Items.Insert(item);
}

foreach(var item in items)
{
    InsertAsync(item);
}

await this.unitOfWork.SaveChangesAsync();

Answer 1

There are three key elements to improve performance when bulk inserting:

• Set AutoDetectChangesEnabled and ValidateOnSaveEnabled to false:

_db.Configuration.AutoDetectChangesEnabled = false; _db.Configuration.ValidateOnSaveEnabled = false;

• Break up your inserts into segments, wich use the same DbContext, then recreate it. How large the segment should be varies from use-case to use-case, I made best performance at around 100 Elements before recreating the Context. This is due to the observing of the elements in the DbContext. Also make sure not to recreate the context for every insert. (See Slauma's answer here Fastest Way of Inserting in Entity Framework)

• When checking other tables, make sure to use IQueryable where possible and to work only where necessary with ToList() or FirstOrDefault(). Since ToList() and FirstOrDefault() loads the objects. (See Richard Szalay's answer here What's the difference between IQueryable and IEnumerable)

These tricks helped me out the most when bulk inserting in a scenario as you described. There are also other possibilities. For example SP's, and the BulkInsert function.