EF4 update a value for all rows in a table without doing a select

I need to reset a boolean field in a specific table before I run an update. The table could have 1 million or so records a开发者_运维问答nd I'd prefer not to have to have to do a select before update as its taking too much time.

Basically what I need in code is to produce the following in TSQL

update tablename 
set flag = false 
where flag = true

I have some thing close to what I need here http://www.aneyfamily.com/terryandann/post/2008/04/Batch-Updates-and-Deletes-with-LINQ-to-SQL.aspx but have yet to implement it but was wondering if there is a more standard way.

To keep within the restrictions we have for this project, we cant use SPROCs or directly write TSQL in an ExecuteStoreCommand parameter on the context which I believe you can do.

I'm aware that what I need to do may not be directly supported in EF4 and we may need to look at a SPROC for the job [in the total absence of any other way] but I just need to explore fully all possibilities first. In an EF ideal world the call above to update the flag would be possible or alternatively it would be possible to get the entity with the id and the boolean flag only minus the associated entities and loop through the entity and set the flag and do a single SaveChanges call, but that may not be the way it works.

Any ideas,

Thanks in advance. Liam

I would go to stakeholder who introduced restirctions about not using SQL or SProc directly and present him these facts:

• Updates in ORM (like entity framework) work this way: you load object you perform modification you save object. That is the only valid way.
• Obviously in you case it would mean load 1M entities and execute 1M updates separately (EF has no command batching - each command runs in its own roundtrip to DB) - usually absolutely useless solution.
• The example you provided looks very interesting but it is for Linq-To-Sql. Not for Entity framework. Unless you implement it you can't be sure that it will work for EF, because infrastructure in EF is much more complex. So you can spent several man days by doing this without any result - this should be approved by stakeholder.
• Solution with SProc or direct SQL will take you few minutes and it will simply work.
• In both solution you will have to deal with another problem. If you already have materialized entities and you will run such command (via mentioned extension or via SQL) these changes will not be mirrored in already loaded entities - you will have to iterate them and set the flag.
• Both scenarios break unit of work because some data changes are executed before unit of work is completed.

It is all about using the right tool for the right requirement.

Btw. loading of realted tables can be avoided. It is just about the query you run. Do not use Include and do not access navigation properties (in case of lazy loading) and you will not load relation.

It is possible to select only Id (via projection), create dummy entity (set only id and and flag to true) and execute only updates of flag but it will still execute up to 1M updates.

using(var myContext = new MyContext(connectionString))
{
  var query = from o in myContext.MyEntities
              where o.Flag == false
              select o.Id;
  foreach (var id in query)
  {
    var entity = new MyEntity
      {
        Id = id,
        Flag = true
      };
    myContext.Attach(entity);
    myContext.ObjectStateManager.GetObjectStateEntry(entity).SetModifiedProperty("Flag");
  }

  myContext.SaveChanges();
}

Moreover it will only work in empty object context (or at least no entity from updated table can be attached to context). So in some scenarios running this before other updates will require two ObjectContext instances = manually sharing DbConnection or two database connections and in case of transactions = distributed transaction and another performance hit.

Make a new EF model, and only add the one Table you need to make the update on. This way, all of the joins don't occur. This will greatly speed up your processing.

ObjectContext.ExecuteStoreCommand ( _
    commandText As String, _
    ParamArray parameters As Object() _
    ) As Integer

http://msdn.microsoft.com/en-us/library/system.data.objects.objectcontext.executestorecommand.aspx

Edit
Sorry, did not read the post all the way.