Can Entity Framework generate an UPDATE ... WHERE statement in SQL?

Question

Building a data access layer for an internal enterprise MVC application with Entity Framework and the repository pattern. In some cases, I need to update, say, 100k rows in a table with 100 million or so rows in a SQL Server 2008 R2 database.

I am using EF6, targeting an existing legacy database we are refactoring. I am new to Entity Framework, but have much experience with SQL Server.

No matter how I seem to structure my update statement, when I run a profiler, I see 60k individual updates by ID. This can take up to 5 minutes to run. However, I have say a "batch number" that is indexed for a bunch of these records. Is there any way to UPDATE these records with a single where clause generated from EF? My solution has been to just write a simple sp that I then have EF call.

Answer 1

Entity Framework in its current version does not support bulk operations in the sense that you are wanting it to. Each update or delete will be one individual transaction per record. To avoid this, you can run SQL directly from Entity Framework if you do not want to go through the hassle of creating a stored procedure (though, they are pretty easy to get up and going)

using (var context = new MyDbContext())
{
    context.ExecuteStoreCommand("Update MyTable Set Value = {0} Where SomeColumn = {1}", 
                                updateValue, queryValue);
    context.ExecuteStoreCommand("Delete From MyTable Where value = {0}", 
                                myValueToQueryBy);
}

Note the use of the parameter by using syntax that looks very similar to a string.Format(). Please see the MSDN for more information.