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

Question

Creating a data access layer for an internal MVC application with Entity Framework and repository template. In some cases, I need to update, say, 100,000 rows in a table with 100 million rows in a SQL Server 2008 R2 database.

I use EF6, focusing on an existing obsolete database, which we refactor. I am new to Entity Framework but have a lot of experience with SQL Server.

No matter how I seem to structure my update instructions, when I run the profiler, I see 60k individual updates by ID. This may take up to 5 minutes. However, I say "batch number", which is indexed for a bunch of these records. Is there a way to UPDATE these records with a single where clause generated from EF? My solution was to simply write a simple sp, which then received an EF call.

Answer 1

Entity Framework in the current version does not support bulk operations in the sense in which you want it. Each update or deletion will be one separate write transaction. To avoid this, you can run SQL directly from the Entity Framework if you do not want you to encounter the problem of creating a stored procedure (although they get up and go pretty easily)

 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 with syntax that is very similar to string.Format() . See MSDN for more information.