LINQ to SQL and Disconnected Entities Follow-up

来源：互联网发布：sql数据库培训学校编辑：程序博客网时间：2024/06/07 07:08

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] = @p11WHERE ([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] = @p10WHERE ([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] = @p2WHERE ([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] = @p1WHERE [CustomerID] = @p0',N'@p0 nchar(5),@p1 nvarchar(22)',@p0=N'ALFKI',@p1=N'Obere Str. 57 12:09 AM'

Go figure.

Posted in LINQ Orcas ASP.NET

Feedback for this Weblog Entry

re: LINQ to SQL and Disconnected Entities Follow-up
by dpirs August 15, 2007 @ 11:52 am

Hi,
here is how I do it.

I use ObjectDataSource on the presentation layer configured as described here http://davidhayden.com/blog/dave/archive/2005/11/16/2570.aspx (under CompareAllValues). In that way you will get an older instance together with a new one in BLL and Attach will work correctly.

You will have to create a class for DataObjectTypeName but you will do it anyway because of other 'problems' that you have (returning a 'var' form BLL). Or for smaller projects you can use the entity classes constructed by Visual Studio ORM.

Solution is not perfect, but I like it :-).

Regards,

Daniel

re: LINQ to SQL and Disconnected Entities Follow-up
by PBZ August 15, 2007 @ 4:21 pm

This better be a bug or an omission that will be fixed before RTM. If I can't easily predict what it will do, and do funky stuff like what you're shown then I'll never use it, and I imagine lots of other people won't either. Call me crazy but a little bit of type safety for SQL, in places where you don't have to bypass LINQ to SQL, is not worth me not being to tell what it's doing behind the scenes... just my opinion.

re: LINQ to SQL and Disconnected Entities Follow-up
by Steve August 15, 2007 @ 5:16 pm

You should take a look at NHibernate and Active Record :)

This is MS's first go at ORM, perhaps version 2 will be usable, that is typically their track record.

re: LINQ to SQL and Disconnected Entities Follow-up
by bzburns August 15, 2007 @ 7:40 pm

I completely agree with PBZ on this issue. Linq To Sql (as it currently stands) seems to be similar to DataAdapters in that results can be downright unpredictable and when dealing with data of any value, there is simply too much risk involved.

Adding a new timestamp attribute to every table? Are you kidding me?

I'd also like to hear the reasoning behind that creepy update statement. It's a neon sign blinking "I'm unstable."

re: LINQ to SQL and Disconnected Entities Follow-up
by Steve from Pleasant Hill August 15, 2007 @ 10:04 pm

Hate to get on the "hate wagon" here, but as I said in another comment, does the "nail" LINQ is trying to hammer really exist? Do we need this done in this way?

Disconnected updates are very useful. In the past have written code (albeit it was 1.1) that queries a user's own data, keeps it in a dataset in Session. The user can update, add, delete rows -- and when they are done the the dataset is reconnected and updates in the datatable are pushed to the server, rather than hitting the back end for every change as it happens (akin to the old ADO "batch update" recordset method).

This works fine because only 1 user can update these rows. A Timestamp is one way to test for "freshness" when updating rows that anyone can touch. Having a Timestamp field in (almost) every table is quite the norm in many situations.

I may be off base, but LINQ seems to tie data activity syntax more closely to the development language for the sake of working with data thru objects (?).

re: LINQ to SQL and Disconnected Entities Follow-up
by Rick Strahl August 15, 2007 @ 11:39 pm

@bzburns - I think it's important to understand no matter what type of tool you use to automatically generate SQL for you it like will never generate the same SQL you would like it to - in most cases generating sub-optimal code in order to be generic and safe. This is pretty much true of any ORM tool and business object toolkit. The thing that's scary about LINQ to SQL is that you don't get any sort of override. You can't - using LINQ TO SQL - provide your own SQL expressions to optimize or even create your own queries by hand and submit them. LINQ to SQL simply lacks the facilities. So the only alternative to tweaking SQL is to completely work around LINQ to SQL and go the raw ADO.NET route.

@Steve - I think there's definitely a place for a solid ORM solution to address CRUD data access. I've been using a home grown business framework and I haven't written any CRUD code in years and it reduces the amount of code I have to write significantly without a perf hit. CRUD data access is typically small amounts of data so optimization at this level is not as crucial as for queries. If it's done right - yeah I think there's definitely a spot for this sort of framework. Several ORM solutions out there today are close to this ideal already but the missing piece has always been an effect query layer and that's where the LINQ comes in and where a lot of opportunity lies. I bet one of the third party solutions will nail this before Microsoft does.

re: LINQ to SQL and Disconnected Entities Follow-up
by Mat Hobbs August 16, 2007 @ 12:16 am

I raised this kind of point on what was called the ADO.NET vNext forum a while back:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=656821&SiteID=1
It is worrying to see that the disconnected scenario is not properly addressed as it is critical for such a framework to be taken seriously.

re: LINQ to SQL and Disconnected Entities Follow-up
by ian pender August 17, 2007 @ 1:11 am

Spot on.

Thanks Rick. You've just summed up 1 of 2 issues (many-to-many mappings are rather odd. And don't even try and add a disconnected entity to a many-to-many relationship) that hold LINQ back.

I had a play, started creating a dummy app and was stumped. There's no way round it with the current LINQ implementation.

Incidentally, do we know the cost of holding DataContext in the app. i.e. the connected model ? I'm assuming no connections are held open. It would at least be nice to know the cost incurred.

Please keep it up. If there's a way round it I'd be intrigued, otherise LINQ will need a huge disclaimer - ConnectedLINQ perhaps. Seems like a high cost, especially when compared to the other frameworks in this space.

re: LINQ to SQL and Disconnected Entities Follow-up
by Steve August 18, 2007 @ 1:36 pm

True Rick,

However, LINQ is extensible, as it should be - which has allowed for 'NHibernate to LINQ' already . ( http://ayende.com/Blog/archive/2007/03/17/Implementing-Linq-for-NHibernate-A-How-To-Guide--Part.aspx )

So, if you want a mature ORM solution with LINQ, NHibernate is still the preferred choice, IMO.

In addition, there are query languages used in ActiveRecord/NHibernate that have been optimized and still provide powerful query syntax. http://www.hibernate.org/hib_docs/reference/en/html/queryhql.html

Again, I understand what you are wanting, I agree, I would want it as well, I'm not trying to be a 'stick in the mud' - but the fact is if you are really needing a tried and true ORM solution with a proven track record, I believe NHibernate is a better option until LINQ over SQL matures.

My concern is that the issues brought up here were brought up quite some time ago, given responses to by Matt in MS forums, but I haven't really seen it delivered yet.

Last thing, I'm glad to see MS finally showing ORM as a legitimate solution, I hope it helps other ORM's - as I develop using a Domain model architect, and this will be further proof that 'even MS uses it' - lol (sad but true)!

re: LINQ to SQL and Disconnected Entities Follow-up
by Rick Strahl August 18, 2007 @ 5:37 pm

Steve - agreed. Really I don't have a beef with LINQ per se - it's LINQ to SQL that's the emperor without clothes IMHO... the power of LINQ as a language construct is tremendously helpful and flexible in ways that we probably haven't even begun to discover. It's sad that it looks like the power of LINQ with a DAL is probably going to be shown off by a third party and not Microsoft. Ironic ain't it?

I have no argument with third party solutions - in fact I'm looking at various things at the moment as I'm relooking to build my internal tools once 3.5 releases. I've built my own data layer way back in .NET 1.0 when I was still struggling with .NET. It worked out surprisingly well and the toolset has matured over the years, but there's always this nagging feeling that it's a hack - as it is. It's not consistent - as most home grown solutions are. It works very well for me, but frankly I wouldn't want to force my model onto developers as a generic solution. As was pointed out many times before building a solid ORM based DAL is a complex thing. The key is balancing complexity, flexibility, performance and usability.

I have no argument with third party tools and I expect them actually to be more mature and in many ways more solid that what Microsoft cooks up. However, I sure would like to see a solid solution in the box so at least you have a good baseline to start from even without having to go the 3rd party route.

As somebody who builds tools that redistribute and must include data access code as part of apps it drastically complicates matters to rely on anything third party as part of a solution. I guess it's not going to happen - not in this first release.

From the outside without looking at LINQ to SQL seriously and trying to apply it - it looks very promising. But the roadblocks you run in almost right out of the starting gate seem very jarring - like did Microsoft not see these scenarios coming? Seriously if they missed the disconnected scenario WTF were they thinking? Have we really degraded to apply .NET technology just to fit the drag and drop model and proper application design be screwed?

re: LINQ to SQL and Disconnected Entities Follow-up
by Oleg Yevteyev August 20, 2007 @ 11:58 am

It might be not elegant, but I found a way to update a disconnected linq entity without using the Attach method, which is not quite helpful yet.

public virtual void UpdateOne(T entity)        {                       Table<T> tbl = dbc.GetTable<T>();            var q = from a in tbl where a == entity select a;            T original = q.FirstOrDefault();                        Utils.CopyObject(entity, original);                        dbc.SubmitChanges();        }

where dbc is a DatabaseContext
and Utils.CopyObject is:

public static void CopyObject<T>(T from, T to)        {            Type tto = typeof(T);            PropertyInfo[] pFrom = tto.GetProperties();                        foreach (PropertyInfo p in pFrom)            {                PropertyInfo tmp = tto.GetProperty(p.Name);                if (tmp == null) continue;                tmp.SetValue(to, p.GetValue(from, null), null);            }        }

The key point is

 var q = from a in tbl where a == entity select a;

it works in generic form to my surprise - simply using primary key to extract records.
Of course, the ideal code would be

public virtual void UpdateOne(T entity)        {                       Table<T> tbl = dbc.GetTable<T>();            dbc.Attach(entity,true);            dbc.SubmitChanges();        }

But it does not work in the current DLinq implementation.

re: LINQ to SQL and Disconnected Entities Follow-up
by Steve August 21, 2007 @ 7:54 am

But wouldn't the following code below?

public void Update(Customer customer)
{
NorthwindDataContext context = new NorthwindDataContext();
context.Attach(customer);
context.Refresh(RefreshMode.KeepCurrentValues, customer);
context.SubmitChanges();
}

re: LINQ to SQL and Disconnected Entities Follow-up
by Rick Strahl August 24, 2007 @ 1:48 am

@Oleg - yes in light of what I've seen I've thought about Reflection myself <g>. But the whole point of an entity layer is that it should handle this for you and hopefully without the overhead of Reflection.

FWIW, using a TimeStamp field allows writing an update routine in a much simpler fashion (as found on Rob Conery's blog - http://blog.wekeroad.com/2007/08/22/linqtosql-momma-said-knock-you-out):

public bool Save(tt_customer Customer){    using ( TimeTrakkerContext context = new TimeTrakkerContext() )    {        try        {            if (Customer._version == null)                context.tt_customers.Add(Customer);            else                context.tt_customers.Attach(Customer, true);            context.SubmitChanges();        }        catch (Exception ex)        {            this.SetError(ex);            return false;        }    }        return true;}

@Steve - yes looks like using .Refresh() after an attach also works...

re: LINQ to SQL and Disconnected Entities Follow-up
by Garry August 27, 2007 @ 5:40 pm

I’m seeing some really strange behavior when attaching entities to a context.

This is based on a simple LINQ to Sql class that has Products and ProductCategories (with timestamps added).

NorthwindDataContext db = new NorthwindDataContext();Product product = db.Products.Single(p => p.ProductID == 1);Console.WriteLine(product.CategoryID);//Console.WriteLine(product.Category.CategoryName);product.ProductName = product.ProductName + DateTime.Now.ToString();NorthwindDataContext db2 = new NorthwindDataContext();db2.Products.Attach(product, true);db2.SubmitChanges();

.

Running the above code, everything works as excepted.

However, if you uncomment the following line: Console.WriteLine(product.Category.CategoryName) which lazy loads the Category entity, some unexpected things happen. Calling SubmitChanges now causes a new category record to be created in the database and the product record is updated with the new category id.

It doesn’t seem like simply calling into the Category property should change the entity update behavior, but unfortunately it does. As it stands, this has serious implications.

re: LINQ to SQL and Disconnected Entities Follow-up
by Jim Wooley September 07, 2007 @ 10:45 am

You claim that you can not override the update implementation and handle it yourself. Sure you can, if you implement the partial UpdateT, InsertT, DeleteT, you can do whatever you want when the DataContext evaluates the item to be changed. The typical case here is using these methods to supply stored procedures as the access mechanism rather than using the runtime behavior.

I suspect some of the unexpected behavior you are seeing with the native update without the time stamp is due to not resetting the UpdateCheck flag on your objects after removing the time stamp. The problem you will run into when not using the timestamp is the fact that when you attach your object back to the context using the .Attach method or manually as Oleg did, you are now comparing the returned values with the current ones in the database. You are not checking for concurrency against the values that were in the database when the record was originally fetched and disconnected. To do this, you need to either 1) Use a timestamp, 2) Have the database generate a hash of the original values, store that and regenerate it from the current database values when you return, or 3) Have your object persist a copy of itself with the original state which you then use to generate your concurrency check back to the database.

Working with LINQ to SQL disconnected entails many of the same issues that working with disconnected ADO does. You will need to worry about many of the same things as you return. You won't need to worry about wiring up your database code manually.

LINQ to AWDNUG
by Mabsterama September 12, 2007 @ 3:42 pm

September's meeting of the Albury/Wodonga .NET User Group was all about new features in Visual Studio

re: LINQ to SQL and Disconnected Entities Follow-up
by Andrew Tobin September 12, 2007 @ 7:30 pm

"You can't - using LINQ TO SQL - provide your own SQL expressions to optimize or even create your own queries by hand and submit them."

Really? Tell me if I'm barking up the wrong tree then, but reading Scott Guthrie's Blog: http://weblogs.asp.net/scottgu/archive/2007/08/27/linq-to-sql-part-8-executing-custom-sql-expressions.aspx

We get this, if you want a select query:

IEnumerable<Person> people = ExecuteQuery<Person>(@"select ID, FirstName, LastName from people");

And that would give you a list of people from the database.

Similarly to update you could do in a partial class:

IEnumerable<Person> people = ExecuteQuery<Person>(@"update people set LastName = {1}  where ID = {0}", p.ID, p.LastName);

What you could also do is look at the System.Data.Linq.ChangeSet.

Using that you can look at was is modified and instead of calling dc.SubmitChanges(); you'd call your own method.

SQLDataContext dc = new SQLDataContext();System.Data.Linq.ChangeSet cs = dc.GetChangeSet();foreach(var p in cs.ModifiedEntries){Person person = p as Person;Console.WriteLine("{0} - {1} {2}", person.ID, person.FirstName, person.LastName);}

In the ChangeSet it has all the entries that are ChangeSet.AddedEntries, ChangeSet.RemovedEntries and ChangeSet.ModifiedEntries.

So you can go through each group, or make a List from each group and process it however you want.

Just a thought.

re: LINQ to SQL and Disconnected Entities Follow-up
by Rick Strahl September 12, 2007 @ 8:09 pm

@Andrew - you can always use ExecuteQuery() as long as you can properly project it into a type that exists. IOW whatever SQL result you create has to match the type that you pass in as the generic type parameter.

ExecuteQuery() will let us work around most issues, but of course it also defeats most of the strong typing benefits of LINQ to SQL.

What I was referring to in the comment above was the actual expressions that are supported in querying or ordering/grouping. Say you want to expose a custom function for calculation or even something that LINQ doesn't support. At that point you HAVE TO pretty much revert to using ExecuteQuery OR creating expressions yourself (which is decidedly complex and cryptic).

I have a couple of other posts that specifically talk about the dynamic code scenarios. My conclusion there is basically that there are scenarios where ExecuteQuery will be required and that I'm glad that there's an 'escape hatch' to get to the lower level if necessary.

re: LINQ to SQL and Disconnected Entities Follow-up
by Thomas Schissler September 17, 2007 @ 6:01 am

As I read, the DataContext is not a lightweight object and therefore it should not be instanciated too often. What I tried and what worked well form me was to instanciation a DataContext and keep it in a session object. So you can reuse it to write the data back to the database. This works quite fine but it does not scale very good. So I agree with you, that there is some work left to be done on the LINQ. But anyway you cannot compare it to nHibernate. I think LINQ is a much better approach and this will be the future for not too complex scenarios. The complexer scenarios will be covered by ADO.Net vNext which will give you some enhanced features, but the big advantage of link is it's simple usage. And I found on other O/R Mapper which you can use that simple and that quick.

re: LINQ to SQL and Disconnected Entities Follow-up
by Rick Strahl September 17, 2007 @ 12:34 pm

As to heavy or not, I suppose that depends on perspective. What I did in my business object layer is support both connected and disconnected operation. If you call say the Save() method with no parameter it assumes you're connected and saves changes. If you pass in an entity it assumes you're detached and if so creates a new context to do the update. That way you get to choose on the situation.

However, persisting datacontext across requests in ASP.NET is surely a really bad idea for many reasons. Scalability, Session restarts lots of issues there.

re: LINQ to SQL and Disconnected Entities Follow-up
by Rocky Moore October 23, 2007 @ 3:15 am

Maybe it is just me, but I wonder if the problem is not the datacontext, but maybe the entities. This might be a bit of a wild idea, but what about saving an copy of the entity within the entity which contains the original values. State of what has changed would be automatically tracked within the object (still providing the change events). Although this method would have a bigger footprint, it would be nice to pass around entity objects in a disconnected method and then be able to track just what changes has occured to a given entity. You may wish use those "change stats" in code not even releated to the database end.

re: LINQ to SQL and Disconnected Entities Follow-up
by Rocky Moore November 03, 2007 @ 4:59 am

I just got through testing out adding an instance variable and property to the generated entities and then capturing the original state on the OnLoaded() method along with supplying a Clone method to generate a detached entity instance.

Seems to work great, it has a copy of the original state and is completely detached. When I attach later on, I use the method that allows original state such as .attach(myEntity, myEntity.Original).

Just hope I did not miss anything :)

re: LINQ to SQL and Disconnected Entities Follow-up
by Patrick Greene November 21, 2007 @ 5:00 pm

Not sure if you've tried this or not, but you can add

UpdateCheck=UpdateCheck.Never

to each Column attribute and then update disconnected entities.

re: LINQ to SQL and Disconnected Entities Follow-up
by Khayman December 05, 2007 @ 7:24 am

Too bad they didn't address this issue properly. I can see great potential in LINQ in general but there are too many loose ends such as disconnected entities, and another thing that REALY REALY bothers me is that there's no way (at least I haven't found one) of converting a LINQ qurey result into a DataTable or DataView. In our organization we have many Custom/User controls which recieve a DataTable as a data source and do manipulations on it, as of now if we want to meve on and use LINQ they are completeley useless.

I'm guessing there will be some kind of service pack or a new FrameWork coming out sooner than we all can imagine. I've been reading around for the past couple of days and the web is exploding with coders who are unsatisfied with LINQ.

re: LINQ to SQL and Disconnected Entities Follow-up
by Jon Sagara January 27, 2008 @ 5:24 pm

Hi Rick,

Have you come up with a good solution for this disconnected entities problem yet?

I just got started with a LINQ to SQL project yesterday, and I was cruising along until this morning when I tried to implement update logic. I have been stumped all day.

Basically, what I'm trying to do is this:

1) Get entity from data context.
2) Update the entity.
3) Store the changes back in the database using a different data context.

Frustrating as heck.

Anyway, thanks for the writeup.

- Jon

re: LINQ to SQL and Disconnected Entities Follow-up
by JasonJ February 08, 2008 @ 2:27 pm

This isn't perfect either, but it seems to solve the problem:

http://www.rockymoore.com/TheCoder/archive/2007/11/03/LINQ_Disconnected_Attach_no_Change_tracking_fix.aspx

Basically, this guy creates a property on each partial that stores a copy of the original values in the onload event of the entity. So when you attach, you can just use something like:

Datacontext.attach(myobject, myobject.originalme)

re: LINQ to SQL and Disconnected Entities Follow-up
by Chris February 23, 2008 @ 11:09 pm

Hi,
I streamlined Rocky Moore's solution a bit, with a easy to use base class using genreics and a bit of reflection.
See my blog about it here.
http://condron.wordpress.com/2008/02/24/detached-data-in-dlinq/
-Chris

    public class detachableEntity<T> where T : detachableEntity<T>, new()    {        public void OnDataLoaded()        {            original = Clone();        }        public T original { get; set; }        public T Copy()        {            return Copy((T)this);        }        public static T Copy(T Old)        {            T newItem = Clone(Old);            newItem.OnDataLoaded(); // set the original state for the new object to the currect state             return newItem;        }        public T Clone()        {            return Clone((T)this);        }        public static T Clone(T item)        {            if (item == null)                return null;            T newItem = new T();            // copy all subclass properties.            foreach (PropertyInfo prop in item.GetType().GetProperties())            {                PropertyInfo prop2 = item.GetType().GetProperty(prop.Name);                prop2.SetValue(newItem, prop.GetValue(item, null), null);            }            //the two items now share the same orginal state object, fix this by             //cloning the orginal state object on the item that to create a new original state            //object for the new item            if (item.original != null)                newItem.original = item.original.Clone();            return newItem;        }    }

re: LINQ to SQL and Disconnected Entities Follow-up
by RicoP February 28, 2008 @ 12:33 am

Why keep working with that reflection solution while Steves comment, the refresh solution is also working, or do I miss something?

public void Update(Customer customer)
{
NorthwindDataContext context = new NorthwindDataContext();
context.Attach(customer);
context.Refresh(RefreshMode.KeepCurrentValues, customer);
context.SubmitChanges();
}

re: LINQ to SQL and Disconnected Entities Follow-up
by Fred March 05, 2008 @ 9:48 am

Hi
I used to have a DBFacade that would handle my update
But now i'm getting an 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.'

here is part of my DBFacade

public static bool Update<T>(T obj) where T : class, IUpdatable
{
if (obj == null)
return false;

T originalObj = null;
//get original values
using (DBDataContext ctxOriginal = new DBDataContext())
{
originalObj = ctxOriginal.GetTable<T>().Single(o => o.ID.Equals(obj.ID));
if (originalObj == null)
{
// log
return false;
}
}
using (DBDataContext ctxUpdating = new DBDataContext())
{
ctxUpdating.GetTable<T>().Attach(obj, originalObj);
ctxUpdating.SubmitChanges();
return true;
}
return false;
}

and i call it like this

JobPosting p = null;
if (CurrentContext.JobPostingID > -1)
p = JobPosting.GetPosting(CurrentContext.JobPostingID);
else
p = new JobPosting();

p.Title = txtTitle.Text;
p.ContactPerson = txtContact.Text;
p.Department = txtDepartment.Text;
p.JobCode = txtJobCode.Text;
p.CategoryID = int.Parse(ddlCategory.SelectedValue);
p.CountryID = int.Parse(ddlCountry.SelectedValue);
p.StateID = int.Parse(ddlState.SelectedValue);
p.City = txtCity.Text;
p.ZipCode = txtZipCode.Text;
p.EducationLevelID = int.Parse(ddlEducation.SelectedValue);
p.JobTypeID = int.Parse(ddlType.SelectedValue);
p.MinSalary = decimal.Parse(txtMinSalary.Text);
p.MaxSalary = decimal.Parse(txtMaxSalary.Text);
p.Description = txtDescription.Text;
p.CompanyID = Profile.Employer.CompanyID;
p.PostedBy = Profile.UserName;

DBFacade.Save<JobPosting>(p);

that's when i get the 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.'

also i have entityref in my object , i get errors if i try to use the 'clone' technique

any idea why it does not work

by the way i get the error also when trying the ctx.Refresh... technique

LINQ to SQL and Disconnected Entities Follow-up

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

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