Chang Order of Insert, Update, Delete in LINQ to SQL?

Oct 15, 2009 at 10:25 PM

I first want to thank you for the terrific work on this project.

I have used SQL Entity Base in a project and everything has worked quite beautifully.  In debugging however we have come upon a corner case.  I hope you can help!

Here is a simple example assuming LINQ > WCF > Smart Client

1) A user looks up an Order (which contains order details)

2) the user deletes an existing OrderDetail(the database has a unique constraint on OrderDetail for OrderId, ItemId)

3) the user then inserts a new OrderDetail for the same item (which matches the previously deleted items unique constraint OrderId, ItemId)

4) the user saves their changes

We get an error thrown from the database about the unique constraint violation because LINQ to SQL executes the Insert BEFORE the Delete.  To fix this, all I need to do is change the order of the statements, but alas there is no way to do this in LINQ to SQL.  

So now I need to modify our save method to first attach and delete the entities that are marked as deleted, and then continue with the updates and inserts (perhaps all inside a transaction).  However it appears, that if I attach the deleted orderdetail to the datacontext and submit changes, the entire entity tree is added to the DataContext.  Here is example code I used to prove my point.  Note that I am just getting the first delete and the first insert for this example.

 

OrderDetail toDelete = modifiedOrder.OrderDetails.FirstOrDefault(i => i.LINQEntityState == EntityState.Deleted);
if (toDelete != null)
{
    db.OrderDetails.Attach(toDelete);
    db.OrderDetails.DeleteOnSubmit(toDelete);
    db.SubmitChanges();
}

OrderDetail toInsert = modifiedOrder.OrderDetails.FirstOrDefault(i => i.LINQEntityState == EntityState.New);
if (toInsert != null)
{
    db.OrderDetails.InsertOnSubmit(toInsert); <= EXCEPTION: Entity is already attached
    db.SubmitChanges();
}

 

 

So when I follow up with my InsertOnSubmit, an exception is raised that the Order Entity is already attached.  And yet, I haven't explicitly attached it yet.   Do you have any ideas on how I can proceed?  Do I need to de-construct the entities and remove their parent/child references prior to attaching them?  Any other thoughts on how to solve this problem?

Oct 16, 2009 at 12:16 AM
Sounds like you need a transaction.

That is just a guess, however.

>
Oct 16, 2009 at 12:25 AM

Thanks.  The use of a transaction ensures that both the deletes and the inserts succeed together.  Indeed important. However, that does not affect the Exception that is thrown by the DataContext when I attempt to InsertOnSubmit an order detail following the DeleteOnSubmit of a separate OrderDetail.   Would greatly appreciate any other thoughts.

Oct 16, 2009 at 12:42 AM
When I need to do something like that, (and I have needed to do it), I
just write a custom mySomeEntityManager.DeleteWithCascade(Guid
rootEntittyPkId) and then I call other Managers in the required order.
Manager is AKA Repository Pattern. That way, Managers do the work.
Entities just hold data. Etc. See my code sample uploaded in the
Patches section to see an example. HTH.

-- Mark Kamoski

>
Coordinator
Oct 18, 2009 at 4:14 AM

Hi,

I'm a bit late to the converstation, but I what you could do is (and this is a guess, but it should work):

1) Open a data context  

<font size="2" color="#0000ff"><font size="2" color="#0000ff">

using

</font></font>
<font size="2" color="#0000ff">

 

</font>

(NorthWindDataContext db = new NorthWindDataContext())

{

2) use the ToEntityTree() to get all objects that need deleting (i.e. where EntityState == Deleted)

3) attach these manually to the data context with DeleteOnSubmit() (i.e. don't use the EntityBase to do this)

4) Submit Changes

5) Close the data context

}

6) Open another data context

using (NorthWindDataContext db = new NorthWindDataContext())

 

{

7) SynchroniseWithDataContext() as per usually, it will attach everything, including deleted objects

8) Submit Changes as usual

9) Close data context

}

This shoud work, because the first time it goes and deletes all records, then goes and does everything else - the deletes the second time should be fine also, from memory because it won't complain if it's already deleted.

Cheers

Matt

Oct 19, 2009 at 12:50 AM

Matt, thanks for the reply.  You're exactly right.  I ended up adding an overload to SynchroniseWithDataContext() which accepts two DataContexts: deleteDataContext and updateDataContext.  Then it does exactly as you described, first executing the deletes (I update them to NotTracked), and then passing the update context to the standard SynchroniseWithDataContext().   It ain't as pretty as doing it all through a single data context, but for now it most certainly works.  The only other drawback to this approach is that using a transaction on the call to the new SynchroniseWithDataContext requires a DTC coordinated transaction since it uses multiple DataContexts.  The beauty of your class is that ToEntityTree() method!  Without that solving this would have been really ugly.  Thanks again.  Kevin