Re: LINQ for SQL Conflict Resolution

:-)  It may well come to that for me too. I was hoping to avoid having to do that because it'd be a fair bit of work, harder to maintain going forward, and would be a departure from how this particular DB is normally accessed.

Simon

On Wed, 1 Apr 2009 11:47:58 -0700, Helen Warn <[email protected]> wrote:

>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

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