Posts
208
Comments
1144
Trackbacks
51
Linq Table Attach()

The ability to use live Linq queries right in your UI makes for great demo's, but it doesn't bear a striking resemblance to a real-world, professional application which uses tiers.  In traditional n-tier applications, you want to have a strong "separation of concerns" and encapsulate your business layer, your data layer, and your UI layer distinctly.  One of the nice things about Linq is that the flexibility is huge.  If you want to do live queries in your UI, fine.  If you want to encapsulate Linq queries in your data layer, that's fine too.

Having said that, the biggest problem I faced when using the RTM for the first time was trying to update an object that had been created by a "different" data contact.  I continually ran into one of these dreaded exceptions:  "System.InvalidOperationException: An entity can only be attached as modified without original state if it declares a version member or does not have an update check policy."  The other one was: "An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext."

Microsoft has documentation here that is meant to describe how to properly implement this scenario.  The key to the update are these bullet points: 

LINQ to SQL supports updates in these scenarios involving optimistic concurrency:
- Optimistic concurrency based on timestamps or RowVersion numbers.
- Optimistic concurrency based on original values of a subset of entity properties.
- Optimistic concurrency based on the complete original and modified entities.

But they never really gave any concrete example of implementation.  So here is a quick example of how to avoid this.

OK, here is my (ridiculously simple) table:

CREATE TABLE Contacts(
ContactID int IDENTITY(1,1) NOT NULL,
FirstName varchar(50),
LastName varchar(50),
[Timestamp] [timestamp] NOT NULL,
CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED (ContactID ASC)
)

Next, drag out the table from the Server Explorer onto a dbml surface:

If right-click on the Timestamp column in the dbml above and select "Properties", you'll see this Properties window:

Notice the Auto Generated Value and Time Stamp properties are both set to true. This is key.

Now let's suppose I create a ContactManager class that is going to be my public API that will encapsulate all of my CRUD functionality.  (In fact, I can make my Linq data context classes all Internal so my UI truly does not know about them)

   1:  public static class ContactManager
   2:  {
   3:      public static Contact GetContact(int contactID)
   4:      {
   5:          using (ContactsDataContext dataContext = new ContactsDataContext())
   6:          {
   7:              return dataContext.Contacts.SingleOrDefault(c => c.ContactID == contactID);
   8:          }
   9:      }
  10:   
  11:      public static void SaveContact(Contact contact)
  12:      {
  13:          using (ContactsDataContext dataContext = new ContactsDataContext())
  14:          {
  15:              if (contact.ContactID == 0)
  16:              {
  17:                  dataContext.Contacts.InsertOnSubmit(contact);
  18:              }
  19:              else
  20:              {
  21:                  dataContext.Contacts.Attach(contact, true);
  22:              }
  23:              dataContext.SubmitChanges();
  24:          }
  25:      }
  26:  }

Notice that I'm disposing my data context each time so I truly can support a stateless n-tier service.  Also, notice I am calling the Attach() method (line 21 above) and giving the second parameter as "true" - meaning, attach as modified.  I have to call Attach() here because the original data context that created my object isn't around anymore.  I have to attach it as modified so that the framework will understand that my intent is to perform an update here.  Additionally, a look at the data context's Log property shows the SQL that was actually emitted during run-time execution:

   1:  UPDATE [dbo].[TempContacts]
   2:  SET [FirstName] = @p2, [LastName] = @p3
   3:  WHERE ([ContactID] = @p0) AND ([Timestamp] = @p1)

So, the timestamp is taken into account as well so that full Optimistic concurrency is supported.

posted on Monday, December 17, 2007 9:37 PM Print
Comments
Gravatar
# re: Linq Table Attach()
yudi
1/8/2008 12:00 PM
does your example work?

i'm having a problem, always get "Cannot attach an entity that already exists.". I use timestamp as you recomended.

using(mydatacontext datacontext = new mydatacontext()){
myobject ob = datacontext.obs.SingleOrDefault(r => r.ProfileID == Convert.ToInt32(id));
ob.FirstName = txtFirstName.Text;
try
{
datacontext.ResearcherProfiles.Attach(ob, true);
datacontext.SubmitChanges();
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
in general how come such as task like updating data made so difficult in LINQ.
Gravatar
# re: Linq Table Attach()
Steve
1/9/2008 8:06 AM
The problem that you're running into is that your example is not the same as the one I've got posted. I'm my example I'm saving an entity that was created with a different data context - hence the need to Attach(). In your example, you're saving an entity that was creating with the already existing data context that you're using. So actually, you have no need to call the Attach() method at all here. Just delete the line of code where you have the Attach() call and then run your code again - it should work.
Gravatar
# re: Linq Table Attach()
yudi
1/9/2008 9:45 AM
one question, what about passing the datacontext as parameter additional to the object you want to change? i tried that works fine even if use multi layer (ui, bl, dal). is that a good way to do (passing datacontext)?
thanks
Gravatar
# re: Linq Table Attach()
Darren
1/9/2008 9:45 AM
This is a great example - thanks for posting it. I do have one question, is there any way to update an object without optimistic concurrency (I know that's not a got idea, but we have a scenario where we know the data isn't updated elsewhere). It seem ridiculous to select the record in order to supply the original values!!!

Thanks.
Gravatar
# re: Linq Table Attach()
Steve
1/9/2008 1:41 PM
If you're trying to build a true n-tier application then I wouldn't recommend passing the data context along with your object. This basically violates the principal of encapsulating your architecture into tiers. Additionally, this wouldn't in a web application where everything is request/response.
Gravatar
# re: Linq Table Attach()
Steve
1/18/2008 8:12 PM
Darren - Yes it is possible to do this without optimistic concurrency. Revisiting the original exception: "System.InvalidOperationException: An entity can only be attached as modified without original state if it declares a version member or does not have an update check policy."

The key here is that you can get around it by not having an "update check policy." So...all you have to do is go to each field in the data context and change the "Update Check" property from "Always" to "Never". This will give you what you're after.
Gravatar
# re: Linq Table Attach()
john mcfetridge
1/19/2008 2:09 PM
Steve: good article but I tried implementing a simple ASP.NET grid app using a DAL with an objectDataSource so I have a disconnected DataContext. I 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 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)
{
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.

very puzzled as this is pretty well the same thing you did , I think
Gravatar
# re: Linq Table Attach()
Steve
1/21/2008 10:46 PM
john mcfetridge - Without seeing your asp.net code, my initial guess would be that you're not persisting the timestamp in your data grid. If you put a breakpoint in your ContactUpdate() method, what is the value shown for your Timestamp property? Also, you should check out what SQL is being sent to SQL Server by assigning a writer to your DataContext's Log property (and invaluable debugging technique in Linq). This will show the exact SQL it's trying to run which should pinpoint the reason why it's telling you Row Not Found.
Gravatar
# re: Linq Table Attach()
john mcfetridge
1/22/2008 12:57 PM
Steve:
u where bang on, the Timestamp is not being preserved across the ObjectDataSource . As soon as I persisted it then the update worked fine. I just did a "dirty" fix to make this work but will find a more elegant solution later . Do you have any idea how the SubmitChanges is using the Timestamp.

Thanks


Gravatar
# re: Linq Table Attach()
Steve
1/22/2008 11:06 PM
John - If you assign a writer to the DataContext's Log property, then you'll be able to see for yourself "how" the SubmitChanges() is using the timestamp. Specifically, what you'll see is that the WHERE clause that it is generated is using both your ID (i.e., your primary key) as well as the timestamp column (standard optimistic concurrency). So previously when you were having problems, the ID was fine but the timestamp wasn't and the WHERE clause that was being generated caused the row to not be found.
Gravatar
# re: Linq Table Attach()
Adam
1/23/2008 9:07 PM
Hi Steve,
I have created a ContactManager in my business layer and from the client via WCF, I am requesting a Contact using the GetContact method. I am then making a change to one of the contact object's properties and then I call SaveContact (via WCF again) and pass in the contact object.

The problem is that the Attach never works. I keep getting a NullReference exception on this line:

dataContext.Contacts.Attach(contact, true);

Any ideas?
Gravatar
# re: Linq Table Attach()
Steve
1/23/2008 10:20 PM
Adam - Did you verify with the debugger that the "contact" object you are sending in to the Attach() method is, in fact, not null? If it is null, then you're problem is in something even before you're getting to your Linq code.
Gravatar
# re: Linq Table Attach()
Adam
1/23/2008 10:37 PM
Hi Steve, the object itself isn't null. As when I retrieve it to the client, it's properties are accessible and then when I send it back to the DAL to write the changes, the object is also not null. I've tried cloning the object and sending original parameters around so I can use Attach(new, old) but that doesn't appear to work either.
Gravatar
# re: Linq Table Attach()
Adam
1/24/2008 12:55 AM
Hi Steve, I have resolved the issue. I had Serialization set to none in the dbml. I was manually marking the objects I needed with [DataContractAttribute] and their properties with [DataAttributes]. Essentially an object that was linked by FK wasn't serialized and hence, that was producing a null reference when trying to attach the object to the data context. Cheers.
Gravatar
# re: Linq Table Attach()
Rob
1/25/2008 12:37 PM
Hi Steve,

How does this work with tables with Foreign keys? It just comes up with the 'different datacontext' for me.

Thanks,

Rob
Gravatar
# re: Linq Table Attach()
johnthom
7/5/2008 6:38 PM
Thanks, this really helped!
Gravatar
# re: Linq Table Attach()
ecards
8/11/2008 5:10 AM
This is a good read on how things work under the hood.

However the practical problem is that the LinqSql designer does not support refresh.

So for example if I carefully modified many update policies, they would all be erased the minute I renamed a column in the db schema.

Regards,
Lee
Gravatar
# re: Linq Table Attach()
Steve
8/14/2008 1:42 PM
Correct. Quite frankly, this is one reason why using the designer is not always the best approach. Often I'll use it for the initial code generation but then manipulate the code myself on my own DataContext file.
Gravatar
# re: Linq Table Attach()
y2kstephen
8/14/2008 10:55 PM
when my table have foriegn keys it doesn't work anymore...
:(
Gravatar
# re: Linq Table Attach()
Kris
8/20/2008 6:54 AM
@ecards,

The missing "refresh" functionality in the Linq2SQL designer was annoying me a bit too, so I wrote an add-in for it. More details on the add-in at http://www.huagati.com/dbmltools/

@Steve,

Don't modify the code in the generated code. This is why all linq2sql classes are partial classes and all extension methods are partial methods - you can do all customizations in partial implementations in separate files.
Gravatar
# re: Linq Table Attach()
Dave
8/23/2008 6:22 PM
I have been trying to perform the attach method with no avail. I come to the same conclusion as Rick Strahl (CLUNQ). I have added a DateTime Propery and marked it is a TimeStamp in the designer. The following code from K Jackobson @ Solid Code illustrates what I am trying to do...With no success.

//mimic transport (serialization) to and from another tier of the application
transport = outPerson.ToXml();
inPerson = transport.FromXml<Person>();
//Force a change to the entity
inPerson.FirstName = "John";

//save the inbound entity back to the database
using(DataContext dc = new DataContext("connectioninfo")) {
// Code fails at the attach line without fail. (Cannot add an entity with a key that is already in use.)
dc.GetTable<Person>().Attach(inPerson);

//Never gets here
dc.Refresh(RefreshMode.KeepCurrentValues, inPerson);
dc.SubmitChanges();
}

Any help would be greatly appreciated,

Dave
Gravatar
# re: Linq Table Attach()
mehmet
9/27/2008 11:34 AM
very thanks to you...
Gravatar
# re: Linq Table Attach()
Rohit
1/29/2009 2:15 AM
TestDataContext dt = new TestDataContext();
Table<T> table = dt.GetTable<T>();
table.Attach(entity, true);
dt.SubmitChanges();
DataContext.SubmitChanges();

int this code comes a error just like as under:-


An entity can only be attached as modified without original state if it declares a version member or does not have an update check policy.
Gravatar
# re: Linq Table Attach()
Steve
1/29/2009 7:17 AM
@Rohit - Try setting the UpdateCheck property of your data context to never.
Gravatar
# re: Linq Table Attach()
M. Jahedbozorgan
4/14/2009 9:46 AM
Very nice article, thanks.
Gravatar
# re: Linq Table Attach()
Ngoc Anh
4/30/2009 3:11 PM
Very thanks !
Gravatar
# re: Linq Table Attach()
Max
6/4/2009 6:19 AM
Many thanks ! Great example !!
Gravatar
# re: Linq Table Attach()
Max
6/4/2009 6:39 AM
How-to implement rowversion (is the same of this example above)

http://msdn.microsoft.com/en-us/library/ms182776.aspx
Gravatar
# re: Linq Table Attach()
Steve
6/4/2009 3:44 PM
@Max - I've never used the row version to do this but you should be able to implement it by following the same pattern. In fact, check out this other link from my blog that shows how to use DateTime or GUID for this same purpose. You should be able to use rowversion in the same way: http://geekswithblogs.net/michelotti/archive/2007/12/18/117823.aspx
Gravatar
# re: Linq Table Attach()
Waleed Fahmy
6/19/2009 12:37 PM
Thanks, I have been struggeling with that for 2 days now!! This is the only example that I was able to get it to work. I almost gave up on using LINQ to SQL and was about to give up 2 weeks of learning and all the code that I did. Hope that I do not bump into more Microsoft surprises!!
Gravatar
# re: Linq Table Attach()
Steve
6/19/2009 2:09 PM
@Waleed Fahmy - Have a look at my other post over here: http://geekswithblogs.net/michelotti/archive/2007/12/25/117984.aspx
Gravatar
# re: Linq Table Attach()
Gyani Sada
10/9/2009 3:56 PM
Set UpdateCheck = never for every column in the table and the problem should be resolved. good luck.

<Column Name="ID" Type="System.Int32" DbType="Int" CanBeNull="true" UpdateCheck="Never"/>
Gravatar
# re: Linq Table Attach()
ankitc#
5/14/2010 9:46 AM
Thanks a lot ! As I commented the attach() statement in the table of already existing datacontext the code worked.
Gravatar
# re: Linq Table Attach()
Paolo Roncalli
7/14/2010 10:46 AM
Thank's, I've lost so many hours before finding your solution.
Gravatar
# re: Linq Table Attach()
roy
1/12/2011 6:59 AM
super example man! i've been searching for a solution for days now.

great work. keep it up.
Gravatar
# re: Linq Table Attach()
net grid
7/21/2012 9:15 AM
I have created a ContactManager in my business layer and from the client via WCF, I am requesting a Contact using the GetContact method. I am then making a change to one of the contact object's properties and then I call SaveContact (via WCF again) and pass in the contact object.

Post Comment

Title *
Name *
Email
Comment *  
Verification

View Steve Michelotti's profile on LinkedIn

profile for Steve Michelotti at Stack Overflow, Q&A for professional and enthusiast programmers




Google My Blog

Tag Cloud