Several people have been flogging me for my LINQ and disconnected Entity post, so I thought I should follow up on the issues I've been having. First let me restate Entity issue I raised a couple of days ago, but let me present it in a simpler context with the Northwind database, so you can try it and experiment with this stuff yourself and those who keep telling me to RTFM can post a concrete reply <g>.

The issue is this: I want to basically create an instance of an entity through LINQ then disconnect from LINQ's data context. Basically remove the object from change tracking. Then I want to reattach it later to the context and have it appropriately update the database. Now in the original example I used my own sample data and no matter what I tried it didn't work. Period. I could not get updates to work (and in fact it still doesn't work with my data) but I did get it to work with Northwind. But the experience is still not what I would call intuitive.

Ok, so the following code is an aproximation of a very simple disconnected operation. I load an entity and disconnect the data context and then reattach it to a new data context:

NorthwindDataContext context = new NorthwindDataContext();
 
// *** Grab a single customer object
Customer cust = context.Customers.Single(c => c.CustomerID == "ALFKI");
  
// *** Simulate end of context throw away changes etc.
context = null;
 
// ... do other stuff - Web Service, deserialize whatever to reload entity
// and modify the data in the entity
cust.Address = "Obere Str. 57 " + DateTime.Now.ToShortTimeString();
 
// *** We now have a disconnected entity - simulate new context
NorthwindDataContext context2 = new NorthwindDataContext();
 
context2.Customers.Attach(cust,true);
 
context2.SubmitChanges();

Now, if I do this out of the box with raw Northwind database the above code fails. As was pointed out by several people you can't do the above because LINQ is essentially a connected model (not in terms of connections, but in terms of data context instancing) because LINQ to SQL requires the change state. As it sits above the code will fail with:

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.

So, as mentioned in the last post if there's no timestamp member or other 'versioning' mechanism provided there's apparently no way for LINQ to determine whether that data has changed.

Now to get this to work I can do the following:

  • Add a TimeStamp field to the Customers table in the database
  • Mark the TimeStamp field as Time Stamp

Once the timestamp is in place, .Attach(cust,true) works. Take a look at the SQL generated:

exec sp_executesql N'UPDATE [dbo].[Customers]
SET [CompanyName] = @p2, [ContactName] = @p3, [ContactTitle] = @p4, [Address] = @p5, [City] = @p6, 
[Region] = @p7, [PostalCode] = @p8, [Country] = @p9, [Phone] = @p10, [Fax] = @p11 WHERE ([CustomerID] = @p0) AND ([timestamp] = @p1) SELECT [t1].[timestamp] FROM [dbo].[Customers] AS [t1] WHERE ((@@ROWCOUNT) > 0) AND ([t1].[CustomerID] = @p12) ',N'@p0 nchar(5),@p1 varbinary(8),@p2 nvarchar(19),@p3 nvarchar(12),@p4 nvarchar(20),@p5 nvarchar(22),
@p6 nvarchar(6),@p7 nvarchar(4000),@p8 nvarchar(5),@p9 nvarchar(7),@p10 nvarchar(11),@p11 nvarchar(11),
@p12 nchar(5)'
,@p0=N'ALFKI',@p1=0x00000000000007F8,@p2=N'Alfreds Futterkiste',@p3=N'Maria Anders',
@p4=N'Sales Representative',@p5=N'Obere Str. 57 11:02 PM',@p6=N'Berlin',@p7=NULL,@p8=N'12209',
@p9=N'Germany',@p10=N'030-0074321',@p11=N'030-0076545',@p12=N'ALFKI'

So at this point you can see LINQ is updating every field which is to be expected given that it has no change information. Note that I have to use Attach(cust,true) to get this to work where true indicates that there are changes in the entity. If you pass just the entity the entity is attached only but the changes that might exist are invisible - only explicit changes you make after Attach will update.

Ok, that works, but it requires a TimeStamp field for every table. So a timestamp requirement may not be realistic. Are there other options?

There's another overload that supports passing .Attach() with the second object instance that is supposed to hold the original state. Now this makes some sense - you can basically tell LINQ to attach and then compare the object state against an existing instance and based on that update the change state.

So what I unsuccessfully tried in my previous post is code like the following:

// *** We now have a disconnected entity
NorthwindDataContext context2 = new NorthwindDataContext();
 
Customer cust2 = context2.Customers.Single(c => c.CustomerID == "ALFKI");
context2.Customers.Attach(cust,cust2);
 
context2.SubmitChanges(); 

I load up a second instance from the context and use that as a comparison. But this code fails with:

Cannot add an entity with a key that is already in use.

The problem here is that the context can only track one instance of this object. Because the instance already exists from the Single load (based on the PK I presume), the Attach() fails. The Attach tried to synch finds that there's already another object in the context with the same PK and it fails.

So how are you supposed to get a current instance to compare against? You can do this only by either hanging on to an older instance or - more likely - by using a separate data context:

// *** Grab a single customer object
Customer cust = context.Customers.Single(c => c.CustomerID == "ALFKI");
 
// *** You could also load a second instance and store that somewhere for Attach() state
//Customer cust2 = context.Customers.Single(c => c.CustomerID == "ALFKI");
 
// *** Simulate end of context throw away changes etc.
context = null;
 
// ... do other stuff - Web Service, deserialize whatever to reload entity
cust.Address = "Obere Str. 57 " + DateTime.Now.ToShortTimeString();
 
// *** We now have a disconnected entity
NorthwindDataContext context2 = new NorthwindDataContext();
 
NorthwindDataContext context3 = new NorthwindDataContext();
Customer cust2 = context3.Customers.Single(c => c.CustomerID == "ALFKI");
context3 = null;
 
context2.Customers.Attach(cust,cust2);
 
context2.SubmitChanges(); 

And that works and performs the update.But man is that UGLY. It takes a second data context, a separate SQL statement to retrieve existing state and the Sql for the update this is pretty massive on top of it:

exec sp_executesql N'UPDATE [dbo].[Customers]
SET [Address] = @p10
WHERE ([CustomerID] = @p0) AND ([CompanyName] = @p1) AND ([ContactName] = @p2) AND ([ContactTitle] = @p3) AND 
([Address] = @p4) AND ([City] = @p5) AND ([Region] IS NULL) AND ([PostalCode] = @p6) AND ([Country] = @p7) AND
([Phone] = @p8) AND ([Fax] = @p9)'
,
N'@p0 nchar(5),@p1 nvarchar(19),@p2 nvarchar(12),@p3 nvarchar(20),@p4 nvarchar(22),@p5 nvarchar(6),@p6 nvarchar(5),
@p7 nvarchar(7),@p8 nvarchar(11),@p9 nvarchar(11),@p10 nvarchar(22)'
,@p0=N'ALFKI',@p1=N'Alfreds Futterkiste',
@p2=N'Maria Anders',@p3=N'Sales Representative',@p4=N'Obere Str. 57 11:26 PM',@p5=N'Berlin',@p6=N'12209',
@p7=N'Germany',@p8=N'030-0074321',@p9=N'030-0076545',@p10=N'Obere Str. 57 11:27 PM'

It includes a WHERE clause that compares every field which is pretty much the case even when you don't do 'detached/attached' updates.

So my question is why is this necessary? First off note that we are already sending ALL the data to the server for the WHERE clause. So the concurrency check is already occurring anway so no worries there. Why not just update all fields at this point? If there are differences they would be detected by the WHERE clause in the first place. This whole update mechanism and Attach seems completely redundant in light of the SQL already going to the server.

Another oddity here: If you use the Attach(cust,cust2) if the table has a TimeStamp field, SubmitChanges also fails with:

Value of member 'TimeStamp' of an object of type 'Customer' changed.
A member that is computed or generated by the database cannot be changed.

I suspect that's a bug in the way Attach updates the entity from the 'original state' and is inadvertantly updating the TimeStamp field. This happens inside of the LINQ code - the Update never hits the SQL backend.

So there you have it. It works - but if you ask me the process is about as clear as mud.

As an aside - the massive DataAdapter 1.x like SQL generated above applies only to these detached updates. It looks like if you do direct change tracking on on the object (ie. no detach/reattach) the Sql looks a bit better. This is an update WITH a timestamp field:

exec sp_executesql N'UPDATE [dbo].[Customers]
SET [Address] = @p2
WHERE ([CustomerID] = @p0) AND ([TimeStamp] = @p1)

SELECT [t1].[TimeStamp]
FROM [dbo].[Customers] AS [t1]
WHERE ((@@ROWCOUNT) > 0) AND ([t1].[CustomerID] = @p3)
',N'@p0 nchar(5),@p1 varbinary(8),@p2 nvarchar(22),@p3 nchar(5)',@p0=N'ALFKI',@p1=0x0000000000000BF5,@p2=N'Obere Str. 57 12:03 AM',@p3=N'ALFKI'

updating and checking only those values that have changed plus the timestamp and the PK.

What's interesting is that if you take the timestamp field out there's actually a lot less SQL sent over the wire:

exec sp_executesql N'UPDATE [dbo].[Customers]
SET [Address] = @p1
WHERE [CustomerID] = @p0',N'@p0 nchar(5),@p1 nvarchar(22)',@p0=N'ALFKI',@p1=N'Obere Str. 57 12:09 AM'

Go figure.