Your competitors product, Mindscape Lightspeed (which I am evaluating) supports the ability to do batch updates by allowing you to change the identity key generation for new entities to something that can be computed at the ORM level, and does not need a round trip to the database to get the inserted identify keys back:
http://www.mindscapehq.com/documentation/lightspeed/Controlling-the-Database-Mapping/Identity-Generation
Is there any support for something similar to this in OpenAccess, so that we would be able to support batched updates and deletes in OpenAccess? Or should I just drop to a stored procedure or native SQL through the new ADO.NET API to do this?
9 Answers, 1 is accepted
OpenAccess performs bulk deletes whenever possible via a 'DELETE FROM tablename WHERE pkcol IN (...)' query. Regarding the bulk inserts - this is something we are looking into so that we can insert a bunch of new rows in a single strip to the database and not 1 insert statement for each new object,
If your database has AUOTINC columns there is really no way to bulk insert rows since the newly generated id needs to be obtained for each row added.
You could resort to a stored procedure to perform the bulk insert and then use a new context instance so that all these newly added objects are wired up appropriately.
Btw, I did have a look at the Mindscape documentation and they too suggest to avoid using 'autoinc' columns if you want to perform bulk inserts.
Best wishes,
Ady
the Telerik team
Register for the Q2 2011 What's New Webinar Week. Mark your calendar for the week starting July 18th and book your seat for a walk through of all the exciting stuff we will ship with the new release!
The solution to the problem is to have a Remove<TEntity> function that will accept a where clause for the entity in question, and then using that construct a SQL statement along the lines of:
delete
from
table
where
some_value =
'a'
&& some_other_value =
'b'
Then all you need to do is make sure ClearChanges() is called, so that the identity map is cleared for existing entities that might be loaded, but the delete would be a lot faster if you are deleting lots of entities.
It would also be nice to be able to do bulk updates using a where clause as well, without having to resort to SQL (once again a ClearChanges() would be needed).
As for bulk inserts, you cannot perform bulk inserts into the database if you care about the resulting primary keys for the entity and the primary key is an AUTOINC primary key, unless you implement something like a KeyTable (great idea though). However if you don't care about the resulting entities being tracked after the insert, then it is possible to batch them all up. We do that right now using custom code on top of the ADO.NET layer to build up bulk insert SQL, and I will probably just port that over to run on top of the new low level ADO.NET API. But it would be nice if this was handled in the API directly.
We will deliberate over your suggestions about the bulk delete and updates.
Regarding the KeyTable option for inserts, there is already a key generator mechanism - HIGHLOW (the default key generator mechanism) , that use a 'voa_keygen' table to generate identity values in a bunch and then uses these values for inserts. You can specify the key generator by selecting a domain class in the designer and pressing F4 to open the Propeties window. The 'Identity Mechanism' property lists various key generators available.
Do get back in case you need further assistance.
Greetings,
Ady
the Telerik team
Register for the Q2 2011 What's New Webinar Week. Mark your calendar for the week starting July 18th and book your seat for a walk through of all the exciting stuff we will ship with the new release!
Please consider adding this (unless this was added since the last post to this forum?)
Regarding "OpenAccess performs bulk deletes whenever possible via a 'DELETE FROM tablename WHERE pkcol IN (...)' query.", can you give an example when this would be used?
Thanks.
Batch operation and optimizing the insert and update statements are two different things. We will work on reducing the amount of C/S calls for similar inserts and update, we already do it for deletes.
To have a query language that includes data modification statements is a complete different story. We have no idea what is changed in the database and we have no chance to clean our caches.
Kind regards,
Jan Blessenohl
the Telerik team
Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's SQL Server Community Awards. We are competing in TWO categories and every vote counts! VOTE for Telerik NOW >>
Is there anything like the GetAll<T> in the OpenAccessContext that will clear a table like RemoveAll<T>.
Yours
Björn
You have to use SQL to achieve this. Please add a partial class to your generated context and add a method like:
public
partial
class
EntitiesModel
{
public
void
BatchDeleteAndCommit<T>()
{
string
tablename = Metadata.PersistentTypes.Where(x=>x.FullName ==
typeof
(T).FullName).Single().Table.Name;
this
.ExecuteNonQuery(
"delete from "
+ tablename);
this
.SaveChanges();
this
.GetScope().Database.Cache.EvictAll(
typeof
(T),
true
);
// 2nd level cache
}
}
All the best,
Jan Blessenohl
the Telerik team
Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's SQL Server Community Awards. We are competing in TWO categories and every vote counts! VOTE for Telerik NOW >>
Will the Evict also clean query cache that may have this data too?
Thanks.
Yes it will, whenever we evict an object of a type that is part of a query definition, the results are evicted.
All the best,
Jan Blessenohl
the Telerik team
Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's SQL Server Community Awards. We are competing in TWO categories and every vote counts! VOTE for Telerik NOW >>