Questions about updates with Entity Framework

Question

I am still in the process of getting to know all of the ins and outs of Entity Framework, and I currently find it helpful on a couple of bulk updates to use Entity Framework's ExecuteSqlCommand directly, as it is significantly faster when processing many many thousands of updates (especially when I only need to update a single field). My question is two-fold: first, is this a safe way to handle my updates? To my knowledge, the standard way of updating through EF would be something like

            if (user != null)
            {
                _Context.Entry(user).CurrentValues.SetValues(updatedUser);

                _Context.SaveChanges();
            }

or

            {
                _Context.Entry(updatedUser).State = EntityState.Modified;

                _Context.SaveChanges();
            }

but both of these methods bog down if the number of users to update exceeds ~5000. Am I missing something, or is my use of ExecuteSqlCommand acceptable?

Second, if it is safe to rely on ExecuteSqlCommand, can I hold on to a context whose sole purpose is this task, rather than re-creating it each time I need it? My understanding is that in general, contexts should never be maintained as they will slow down over time with use, but I'm wondering if this holds true for such a limited-use scenario as this as well.

If my fundamental approach to EF's use is in error, I would appreciate someone pointing that out as well. Thanks!

EF is pants with bulk operations, basically either use a sql statement or run change entities iteratively. Manually managing a context in the manner you describe is fraught with complexities, imho you should use the unit of work approach, i.e. create a context and use it for your 5k updates then destroy it, repeat as necessary. — Paul Zahra, Mar 17 '15 at 14:34

score 0 · Accepted Answer · edited May 23 '17 at 12:22

0

EF does not handle bulk operations well at all, There are workarounds like hand writing queries via ExecuteSqlCommand (like you mentioned) or there are 3rd party extensions like EntityFramework.Extended.

For your 2nd part, If all you are going to be using the context for is to call ExecuteSqlCommand it might be better to just new up a normal SqlConnection and SqlCommand on demand and use those (even though you are newing them up every time you call, .NET will be using "Connection Pooling" behind the scenes to reuse connections)

edited May 23 '17 at 12:22

Community

1
1

answered Mar 17 '15 at 14:26

Scott Chamberlain

124,994
33
282
431

Ah, that's very good to know about Connection Pooling. That makes it feel like a much better choice now. Thanks! – TuthmosisIII Mar 18 '15 at 19:06

Questions about updates with Entity Framework

1 Answers1