Hint or configuration tweak or re-ordering of code so my INSERTs happen after DELETEs?

Howdy.

I have a situation where I am running against a SQL 2005 database with a unique key constraint on one of our tables.

We are using LightSpeed, and in some scenarios we delete a row from this table AND insert a new row in the same LS UnitOfWork/transactionscope. I.e., and to be more specific, we are deleting Cost entities from the database and inserting new costs. Deleting the old cost and adding a new cost are separate operations driven by the user's whims via the UI (but, it's a common occurrence).

Unfortunately it seems that when we want to SaveChanges(), both an insert and a delete are queued up--this isn't the unfortunate part. The unfortunate part is that the SQL INSERT runs after the SQL DELETE statement, violating our unique key constraint, which throws an error, which prevents our user from saving.

So, assuming I can't eliminate the SQL unique key constraint, and assuming I don't want to work around the problem by doing the DELETEs in another transaction/UnitOfWork, is there a way I can provide hints to the TransactionScope to run  the DELETEs first?

If not, I already have a (~ugly-ish) workaround. Thanks, Peter

Interestingly it looks like NHibernate has the same behavior: http://stackoverflow.com/questions/706673/forcing-nhibernate-to-cascade-delete-before-inserts

This isn't currently possible.  It's already on the wish list, but I'll bump the priority -- but I'm afraid it's still not likely to be soon.

I think some databases allow you to suspend constraint checking for the course of a transaction, so you *might* be able to kludge around it with that (use LightSpeedContext.DataProviderObjectFactory.CreateCommand and IUnitOfWork.PrepareCommand to execute raw SQL commands).  Otherwise I fear you'll have to use your workaround.

Thanks for the answer. My workaround does involve some prepared commands and it's all good for now. Thanks, Peter