CODEWITHSUNDEEP
c#entity-frameworkef-code-first
Rudi
Rudi

Reputation: 3228

EF: Avoiding multiple update statements

Code like this:

var compIds = from p in packinglist.List
              select p.ComponentId;
var components = from c in context.Components
                 where compIds.Contains(c.Id)
                 select c;
 foreach (var item in components)
 {
     item.CurrentSiteId = packinglist.DestinationId;
 }
 context.SaveChanges();

Ends up issuing lots of SQL Statements like

update [dbo].[Components] set [CurrentSiteId] = @0 where ([Id] = @1)

Is there a way to instruct EF (Code First) to issue the following statement:

update [dbo].[Components] set [CurrentSiteId] = @0 where ([Id] in (....))

Or should I look into using the one of the SQLQuery methods available, or a seperate tool like Dapper or massive or ...?

Upvotes: 4

Views: 610

Answers (2)

Tridus
Tridus

Reputation: 5081

The simplest answer for this is just to write that query and use DbContext.SQLQuery() to run it. As mentioned, there's no way to do this in EF itself.

Upvotes: 0

James Hill
James Hill

Reputation: 61793

There is not currently a way to perform bulk updates in EF 4 out of the box. There are some very long, complicated work arounds that end up generating SQL though. I suggest using a stored procedure or T-SQL. Here's a quick T-SQL snippet that I've used in the past:

using (var context = new YourEntities())
{
    context.ExecuteStoreCommand(
                     @"UPDATE Components SET CurrentSiteId = 1 WHERE ID IN(1,2,3,4)");
}

Upvotes: 2

Related Questions