This question is locked. New answers and comments are not allowed.
Is there any way to perform a bulk update on entities in OpenAccess, without having to result to direct SQL? Can something like this be done in OQL, so it would maintain the state of any entities that might already be loaded?
What I would like to be able to do is make changes to a set of items with a bulk update, similar to the following direct SQL command:
db.ExecuteNonQuery(@"
update " + Tables.Products + @"
set locations_id = @1
where locations_id = @2", newLocationID, oldLocationID);
This command is very efficient, because it updates all the items with the old location to the new location in one quick SQL update command. If we have to do this at the ORM level, currently it seems the only way is to iterate through all the products linked to the old location (which includes loading them into the entity working set), updating the location to point to the new location, and then saving the changes to disk. The end result is a whole lot more SQL going on and the performance is a lot slower than doing the direct SQL above.
If we did drop to direct SQL to do this, which is one option, how can we flush the context so that it will read from the database next time?
What I would like to be able to do is make changes to a set of items with a bulk update, similar to the following direct SQL command:
db.ExecuteNonQuery(@"
update " + Tables.Products + @"
set locations_id = @1
where locations_id = @2", newLocationID, oldLocationID);
This command is very efficient, because it updates all the items with the old location to the new location in one quick SQL update command. If we have to do this at the ORM level, currently it seems the only way is to iterate through all the products linked to the old location (which includes loading them into the entity working set), updating the location to point to the new location, and then saving the changes to disk. The end result is a whole lot more SQL going on and the performance is a lot slower than doing the direct SQL above.
If we did drop to direct SQL to do this, which is one option, how can we flush the context so that it will read from the database next time?