High performance delete?

[Kendall Bennett]

I am in the process of trying to see if I can port some Entity Framework code to OpenAccess, so I can see how the performance stacks up. However one thing I cannot yet work out how to do, is do efficient deletes in OpenAccess. The database we are using is MySQL with MYISAM tables, so they don't support server side cascading deletes, and I we don't have stored procedures for this. Rather in the code we delete related entities as necessary using native SQL, by making calls to the Entity Framework functions to issue a native SQL call to the database.

Is there a way to do something similar with OpenAccess? For instance in one of my classes I have the following:

            uow.ExecuteSqlCommand("delete from " + Tables.Address_Book + " where customers_id = {0}", customerID);

ExecuteSqlCommand in my UnitOfWork class ends up calling ExecuteStoreCommand() on the ObjectContext from Entity Framework.

I see there is support for OQL in OpenAccess, but I am not sure if DELETE is an operation supported in OQL? If not, I assume I need to drop to native SQL, so what is the way to write that in OpenAccess?

Also, once I have issued my native SQL commands, what do I then need to do in order to make sure any entities that might have been loaded in the context are flushed, since they are now deleted from the database? Is there a way to flush anything cached in the OpenAccess context so it will pick up the new data from the database for the next query?

[Jan Blessenohl]

Hi Kendall Bennett,
We are at the moment working on a new direct SQL API based on the context. It will be available in the Q2 release. Maybe you want to wait for that.

To cleanup the first level cache, the easiest way is to just call SaveChanges() we take care of reloading the data. To clean the second level cache you can add a method to your context that calls:

base.GetScope().Database.Cache.EvictAll(typeof(Person),true)

Kind regards,
Jan Blessenohl
the Telerik team

[Kendall Bennett]

For the most part I don't need to materialize entities at all, I just need to be able to issue delete operations (although I am thinking of writing MySQL triggers to do this instead to simulate cascading deletes). Is there no support at all for issuing a direct SQL command right now in OpenAccess?

It sounds like the Q2 release is going to have a lot of things I need, so I might just wait for that.

[Kendall Bennett]

Have you considered adding some kind of way to do this natively in the API, rather than having to drop the SQL for this?

[Jan Blessenohl]

Hello Kendall Bennett,
The deletes are already very optimized, we combine them to an 'in' statement to reduce the server round trips. Nevertheless, because our in memory state management is important, you need to load all data before you can delete it. Imagine our IInstanceCallbacks interface where you can fire related deletes based on the objects state or the tracking API where you can hook into the delete process to write log or audit trail information. All this would not work anymore.

Best wishes,
Jan Blessenohl
the Telerik team