LINQ to SQL - Update an object using a different DataContext

Question

Hi Guys,

 

Maybe it is just me (I am used to DataSets which hold their own state...) but the whole Attached/Dettached objects in LINQ to SQL is a bit confusing. None of the examples I have read all over the net seemed to help me with this problem:

 

Consider the Products table (Northwind).

 

I have a DAL which is stateless. DataContexts are being recreated over and over again (For each logic opertaion...like I used to do with SQL connections)

 

I would like to simulate the following scenario:

 

1) GetProduct(int id)

Create DataContext

Query table

Dispose DataContext

Return result

 

2) Modify Product (lets say that this is done in my client)

 

3) UpdateProduct (Product prod)

Create DataContext

Attach the object

Submit Changes

Dispose DataContext

 

This is not working... I tried dettaching the updated object, I even added a timestamp column, but still it doesnt work...

 

How should I do it using LINQ??

 

 

 

Answer 1

Make sure you're using the Attach(object entity, bool asModified) overload, specifying true.  Otherwise you're attaching the entity as unmodified.  Also, if you're not using a timestamp, you'll have to manage ferrying the original values from client to server yourself, and use the Attach(object entity, object original) overload.

 

Answer 2

No...It doesn't work for me....

 

If I try to attach the object with the asModified overload (after I added a timestamp to the DB and readded the table to the dbml file) I get this error:

 

"An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext.  This is not supported."

 

I tried all the variations of using Attach (with all overloads) and it still doesnt work...

 

Can someone add a code snippet that works?

 

Thanks

 

 

 

 

 

Answer 3

I'm not a LINQ to SQL expert (yet), but as I understand it, you can't ever attach an entity to a different context if it has previously been part of a context.  You can make a copy of that entity and attach it to a new context (which is often the case in multi-tier scenarios where you serialize an entity to some other tier and then back and then you take the deserialized thing that came back and attach it to a context--but this is a different instance than what you originally started with).  My impression is that this is a pretty fundamental design decision around the way linq to sql works--how it keeps track of things that need implicitly loaded vs. have already been loaded, etc.

 

- Danny

 

Answer 4

 

Check this out

http://msmvps.com/blogs/omar/archive/2007/12/08/linq-to-sql-how-to-attach-object-to-a-different-data-context.aspx

Answer 5

It works - meaning it allows you to attach an entity which was queried using another data context to a different data context, BUT I the updates I applied to the object were not monitored (because it is detached) therefore - you have to re-attach the object and only after that to apply the changes - in my opinion this is a big limitation.

 

Answer 6

 

Yeah i agree with that!

I spent at least a day meaning 10 hours. Trying everything i could think of...

Updating using a linq model is probably the weekest thing i have seen in the linq to sql model so far. For some reason i really feel like i am missing something...WIll let you know if i find something. Please do the same.

Answer 7

The Attach methods are designed to be used strictly for multi-tier round-tripping where the objects you attach back are never the same instances as previously retrieved. This is a deviation from the normal pattern of usage which is to keep the DataContext alive for the entire unit of work that you are performing.

 

Since the objects don't ferry their own changes, you have to invent a transport or API that does this and then supply the change information to the DataContext before you attempt to submit your changes back to the database.  This could be as simple as having a web method that recieves an entity plus a few additional changed values, one that recieves two distinct entities (old and new) or one that recieves only the changed entity yet has a version field that can be used instead of the original values that would otherwise be needed to perform the appropriate optimistic concurrency checks.

 

To use LINQ to SQL correctly, you should maintain a stateful DAL, unless you are actually moving data across physical or process boundary tiers.

 

Answer 8

There is one thing that can be done...

 

If you query for the object and then, even before you dispose the dataContext you detach it from the data context, you can work with it in disconnected mode. Later on, you will be able to attach the object and apply changes using Attach as modified signature. It will work if you have a timestamp in that table. the problem here - the update query will be bloated (all columns will be set) and by working in disconnected mode - you lose the advantages of LINQ (deferred loading) and stay with an object representation of a table.

 

Answer 9

I think the real issue is that in a web application you have no choice but being disconnected unless someone can show me an alternative. For Linq to SQL to be used in a web application which many of us write CRUD must be simple and efficient (bloated SQL will not do). I have had no problem using it in n-Tier non-web apps but web apps are looking scary. I have experimented as follows using the info in this article http://msdn2.microsoft.com/en-us/library/bb546187.aspx and used the Timestamp method

 

I first created a simple Contacts table:

CREATE TABLE [dbo].[Contacts](
[ContactID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Version] [timestamp] NOT NULL,
CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED

I use the designer to create my Contacts Entity and then I populate my grid with Contacts and update one of the rows then press the update button that calls my ContactUpdate method in the DAL. It looks like:

public void UpdateContact(Contact changedContact)        //contains a changed Name property
{
     NorthwindDataContext db = new NorthwindDataContext();
     db.Contacts.Attach(changedContact,true);
     db.SubmitChanges();

}

on entry I have a new value for Name but I get an exception:
System.Data.Linq.ChangeConflictException: Row not found or changed.

 

Now I must be missing the obvious here as sure I changed the row or why would I be doing an update! The above

article points out other strategys but it is often not feasible to keep around a copy of the old object without of course

doing a Query and I hope that is not the answer. This is

a key operation if Linq has a hope of being used in a Disconnected mode and it must or else it will be relegated to

being used for simple RAD apps. I am surprised that MS does not provide a  good example .

 

Answer 10

Can you point us in the patterns and practice page that shows how to do a proper nteir app using linq ?

My thought process on this is that you business teirs should be handeling all the information and the presentation teirs should be passing teh objects to the bal and getting the from the bal

Answer 11

I cannot point you to the specific patterns and Practices but we have been writing n-Tier apps for a long time and the DAL is the ONLY layer that can do database access. We combine this with a Provider model for the DAL so we can be agile as new database access methods come online such as TableAdapters , N-Hibernate or Linq to SQL. The DAL only returns objects(entitys) to the caller so the DataContext only lives in the DAL and NEVER anywhere else. This also begs the question , is it really proper to return a Linq decorated object as it contains Linq specific attributes. For what it is worth I do not think so so we map to "pure" version of the object. This can be quite painful!

 

 

Having said this, the subject is really off topic as I think will really need a good answer on CRUD operations in the

DISCONNECTED world or else LINQ to SQL is useless for web apps

Answer 12

http://msdn2.microsoft.com/en-us/bb546187.aspx

Answer 13

I found the answer to my problem , thanks to Steve Michelotti's excellent post http://geekswithblogs.net/michelotti/archive/2007/12/30/118076.aspx. The Timestamp was not being persisted by the Grid and is returned as null. I just did a "dirty" fix to perserve it and the update works

fine .

 

Does anyone have any idea how Submitchanges uses the Timestamp.

Answer 14

 

Funny i was looking at th post as you posted this

Timestamp -> is a var name in this case and a sql type

http://geekswithblogs.net/michelotti/archive/2007/12/18/117823.aspx

same dude new post.. attach checks to see if someone else updated the same row before if not then updates else through exception

Sql Time stamp is auto updated evertime a insert or update is made to tha coresponding row.

 

Answer 15

Hi Matt,

I am interested in stateful DAL.
How do I maintain a stateful DAL in a web farm distributed cache structure?
If there is, that will same a lot of time working around this problem.

thanks.