Re: LINQ for SQL Conflict Resolution

What I have done to avoid some of these pitfalls is to write my own stored
procs for CUD operations, and then in the data model for the table, instead
of using the default option of "Use Runtime" I customize the Insert, Update,
and Delete operations to use my stored procs. This adds them to the methods
of the data context, so in code I use something like:

    public int DeletePicture(Guid picture_uid)
    {
      using (MyDataContext context = new MyDataContext())
      {
        return context.delPicture(picture_uid);
      }
    }

In the above, my stored proc returns an integer (actually @@rowcount). 

You may still not be out of the woods -- I found that I had to set some
properties of FKs, etc. to cascade, but it got me around problems I had when
I just let the runtime handle CUD operations.

Hope this helps,

Helen

-----Original Message-----
From: Discussion of advanced .NET topics.
[mailto:[email protected]] On Behalf Of Simon Robinson
Sent: Wednesday, April 01, 2009 11:37 AM
To: [email protected]
Subject: [ADVANCED-DOTNET] LINQ for SQL Conflict Resolution

I have a problem with a database update that is going through LINQ for SQL,
via a call to DataContext.SubmitChanges().

For reasons to do with auditing, we never delete records in this table
directly; if we wish to delete a record, we do so by updating it to set an
IS_DELETED bit field to 1. This is immediately picked up by an AFTER UPDATE
trigger which does various auditing actions and then deletes the record. 

The problem is this is throwing LINQ's concurrency processing: If I try to
mark a record for deletion, the record isn't deleted, and SubmitChanges()
throws the dreaded ChangeConflictException. The exception goes away if I
comment out the line in the trigger that deletes the row. 

Under the hood: It looks to me like SubmitChanges is sending two requests to
SQL server: An UPDATE to make the changes, immediately followed by a SELECT
to verify that there are no concurrency issues. I think the trigger is
getting in first, resulting in the SELECT returning no rows, hence the
exception. For some reason this also seems to result in the delete getting
rolled back, which is more serious (I'm not clear how that happens; it may
indicate my analysis of what's going on under the hood isn't quite
correct.).

So in short - is there any easy way to tell DataContext.SubmitChanges() not
to worry if the row it thinks it's updated disappears? Or any other
solution?

===================================
View archives and manage your subscription(s) at
http://peach.ease.lsoft.com/archives

===================================
View archives and manage your subscription(s) at http://peach.ease.lsoft.com/archives