This question is locked. New answers and comments are not allowed.
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?
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?