EF4 updates the value for all rows in the table without making a selection

Question

I need to reset a boolean field in a specific table before starting the update. There can be 1 million records in a table, and I would prefer that you do not have to make a choice before updating, because it takes too much time.

Basically what I need in the code is to create the following in TSQL:

update tablename set flag = false where flag = true 

I have something 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 not yet implemented it, but wondered if there is a more standard way.

To preserve the limitations that we have for this project, we cannot use SPROC or directly write TSQL in the ExecuteStoreCommand parameter in the context that I believe you can do.

I know that what I need to do cannot be directly supported in EF4, and we may need to look at SPROC to work [in the general absence of any other way], but I just need to fully explore all the features in first of all. In an ideal EF world, calling above to update a flag would be possible or, alternatively, you could get an object with id and a logical flag only minus related objects and go through the entity and set the flag and make one SaveChanges, but it may not be the way it works.

Any ideas

Thanks in advance. Liam

Answer 1

I would like to contact the stakeholders who submitted restitution that they do not directly use SQL or SProc, and present the following facts to him:

• Updates in ORM (for example, an entity infrastructure) work this way: you load the object that you are executing, you save the object. This is the only correct way.
• Obviously, in your case, this will mean downloading 1M objects and performing 1M updates separately (EF does not have batch processing of commands - each command runs in its own opposite direction to DB) - usually an absolutely useless solution.
• The example you provided looks very interesting, but for Linq-To-Sql. Not for Entity structure. If you do not implement it, you cannot be sure that it will work for EF, because the infrastructure in EF is much more complicated. Thus, you can spend several person days doing this without any results - this must be approved by the interested party.
• A solution with SProc or direct SQL will take you a few minutes and it will just work.
• In both solutions you will have to deal with another problem. If you already have materialized objects, and you will run such a command (through the specified extension or through SQL), these changes will not be reflected in the already loaded entities - you will have to iterate them and set the flag.
• Both scenarios break down the unit of work because some data changes are performed before the unit of work is completed.

It's all about how to use the right tool for the right requirement.

Btw. you can avoid loading real tables. This is just a query that you run. Do not use Include and do not access navigation properties (in case of lazy loading), and you will not load relations.

You can select only Id (via projection), create a dummy object (set only id and and flag to true) and perform only flag updates, but it will still perform 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(); } 

In addition, it will only work in an empty object context (or at least not a single object from the updated table can be bound to the context). Thus, in some scenarios that run before other updates, you will need two instances of ObjectContext instance = manually DbConnection or two database connections, and in the case of transactions = distributed transaction and another performance hit.

Answer 2

Create a new EF model and add only one table in which you need to make an update. Thus, all compounds are not found. This will greatly speed up your processing.

Answer 3

 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, I did not read the message completely.