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.

EntityFramework does not perform batched operations. Each operation will be one operation on the database per record (as you have discovered) You can either write and execute your own SQL statement through EntityFramework or call a stored procedure and pass the appropriate parameters through EntityFramework. — Tommy
– Tommy, Commented Dec 4, 2015 at 18:40
In my experience I would go with a SP for such 'large' operations. — Chief Wiggum
– Chief Wiggum, Commented Dec 4, 2015 at 18:40
The current EF v6 versions don't support batching up operations - but the EF team is hard at work on EF v7 and it will eventually support batch operations. For now - you either need to resort to your own solutions, or have a look at third-party solutions like EntityFramework.Extended — marc_s
– marc_s, Commented Dec 4, 2015 at 19:06

marc_s · Accepted Answer · 2015-12-04 20:28:08Z

4

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.

edited Dec 4, 2015 at 20:28

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Dec 4, 2015 at 18:42

Tommy

39.9k10 gold badges93 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jkerak Over a year ago

Thank you. My instincts make me shy away from SQL text in my .cs files, but I understand the concept. I'm basically making it our practice going forward when we use entity framework to always utilize a stored procedure when updating multiple rows on the known "big" tables. I have a generic data repository pattern, and for "TableA", I add a new method to call the stored procedure to the "TableARepository" class.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related