Re: LINQ for SQL Conflict Resolution

Maybe the pain here is good ... If its hurts to have a (no) bad
architecture people are more likely to have a better architecture.

Greg

On Mon, Apr 6, 2009 at 11:07 AM, Frans Bouma <[email protected]> wrote:
> I wonder why topic started hasn't consider an AFTER DELETE trigger instead
> for deletes? the code would become much simpler and still the deleted data
> is available for auditing purposes (in the trigger, similar to what it is
> now).
>
> Of course I'm not aware of what the auditing stuff contains so it might be
> more complex than that.
>
>> 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.
>
>        As silverlight is so stripped down, I don't think EF classes compile
> on silverlight either, you need DTO's and thus have to babysit the changes
> made and play them back on the server.
>
>        You could perhaps use some mechanism inside the DTO which tracks
> changes and which would make it easier to update the real entity on the
> server, but that would require a different code generator (as sqlmetal
> doesn't use templates).
>
>                FB
>
>>
>> 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
>
> ===================================
> 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