Re: LINQ for SQL Conflict Resolution

Yeah, not fun. If it's any help, you don't have to do it for all the CUD
operations, just the Delete, and let the runtime take care of the rest.

I'm using LINQ to SQL in a Silverlight project, and I find myself basically
maintaining my client objects without it, only using LINQ 2 SQL in the WCF
Services to get the data. I also removed a lot of the FKs from the data
model because I was maintaining the client objects in my Silverlight app,
and with all the FKs the client objects became too bulky. I can get away
with this because the client classes are not very complex. A lot of the
classes generated by SQLMetal do not compile in Silverlight -- and on it
goes. Sigh. I briefly considered switching to the Entity Framework, but
decided to stick with LINQ 2 SQL because it has a smaller footprint on the
client side. I will probably reconsider down the road.

Helen

-----Original Message-----
From: Discussion of advanced .NET topics.
[mailto:[email protected]] On Behalf Of Simon Robinson
Sent: Monday, April 06, 2009 2:56 AM
To: [email protected]
Subject: Re: [ADVANCED-DOTNET] 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

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