Re: LINQ for SQL Conflict Resolution

I am curious (can continue off list), it sounds like you are just
using your ORM for projections only then writing procedural
transaction scripts around it?

This is an unusual way to be attempting to use an ORM and it feels to
me like it would be lacking most of the benefit of an ORM.

Could you provide more details as to what you are doing?

Greg

On Wed, Apr 1, 2009 at 2:47 PM, 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
>



-- 
It is the mark of an educated mind to be able to entertain a thought
without accepting it.

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