Bulk updates in OpenAccess?

[Kendall Bennett]

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?

[Serge]

Hi Kendall Bennett,

 Unfortunately there is no way other than direct SQL to do such a thing. Even if there were some way OpenAccess would still not be able to know whether changes have been made to the database. We are currently working on a ADO like API that will allow you to execute sql using OpenAccess that will be released with Q2. 

However in order to get fresh data to the context you need to perform a context.ClearChanges() which will erase the context level cache and reload everything from the database. 

I hope this is helpful.

Best wishes,
Serge
the Telerik team

[Kendall Bennett]

Ok great, I look forward to testing this when Q2 is released.

[Kendall Bennett]

Q2 is out and I love the new ADO.NET API! It is going to be perfect for my needs.

[Serge]

Hello Kendall Bennett,

 We are glad you like it, please don't hold anything back and let us know if you face any trouble or see room for improvement. 

All the best,
Serge
the Telerik team

[Kendall Bennett]

Have you considered implementing a bulk updates API similar to that provided by Mindscape Lightspeed? They have support for bulk updates and bulk deletes at the API level, that would be nice to have in OpenAccess.

[Serge]

Hi Kendall Bennett,

 While it seems like a bit nicer API LightSpeed's is not much different than deleting the objects with our ADO.NET API, both bypass caching and any full-text indexes. We will take this into account in our next planning however it does not seem very likely that we will implement such an API.

Nevertheless I would like to thank you for taking the time to suggest a new feature.

Greetings,
Serge
the Telerik team