Contact   •   Products   •   Search

Rick Strahl's Web Log

Wind, waves, code and everything in between...
ASP.NET • C# • HTML5 • JavaScript • AngularJs

LINQ to SQL and Disconnected Entities Follow-up


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.

Make Donation
Posted in LINQ  Visual Studio  ASP.NET  


Feedback for this Post

 
# re: LINQ to SQL and Disconnected Entities Follow-up
by dpirs August 15, 2007 @ 11:52am
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:21pm
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:16pm
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:40pm
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:04pm
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:39pm
@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:16am
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:11am
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:36pm
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:37pm
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:58am
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:54am
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:48am
@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:40pm
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:45am
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:42pm
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:30pm
"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:09pm
@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:01am
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:34pm
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:15am
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:59am
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:00pm
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:24am
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:24pm
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:27pm
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:09pm
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:33am
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:48am
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
# re: LINQ to SQL and Disconnected Entities Follow-up
by Alexander Sidorov March 20, 2008 @ 3:37pm
Is there any general article about disconnected part of LINQ to SQL?
I still can't understand why Attach() examples without TimeStamp do not work...
# re: LINQ to SQL and Disconnected Entities Follow-up
by Steve B April 10, 2008 @ 8:06pm
I have a solution that works for me and is clear as day. If somebody else has mentioned this I apologize. I havent tested it in complex scenarios but I know it works in basic scenarios and based on the way it works I can only assume that it, or something similar to it, will work for anything. I posted about it here. http://forums.microsoft.com/msdn/ShowPost.aspx?postid=2524396&siteid=1
# re: LINQ to SQL and Disconnected Entities Follow-up
by Rick Strahl April 11, 2008 @ 1:23am
@Steve - I took a look at your post but I'm not quite sure what you're doing. If I understand this correctly you're making your Pk marked as IsVersion? What else? I gave this a shot for kicks but it didn't work for me - still get the same attach error.

Also I would think using your PK and IsVersion in the same field isn't going to work well. If you update a record and the PK doesn't change. L2S isn't going to detect the record as updated.

You post is a little vagues, so can you clarify what else there is?

Just so we're on the same page, when you break your code down to its core you are doing this right:

            TimeTrakkerContext context = new TimeTrakkerContext();

            EntryEntity entry = context.EntryEntities.Where(ent => ent.Pk == 206).SingleOrDefault();
            entry.InvoicePk++;  // update a value

            context = null;  // clear out


            TimeTrakkerContext context5 = new TimeTrakkerContext();
            entry.Pk = entry.Pk;   //  force Pk IsVersion update

            context5.EntryEntities.Attach(entry, true);  // Attach still fails
            context5.SubmitChanges();
# re: LINQ to SQL and Disconnected Entities Follow-up
by Steve B April 11, 2008 @ 6:37am
Rick,
You seem to have a slightly different scenario than the one I posted about. In my post, the advantage that I have is that I am essentially using the actual .aspx page as a layer of abstraction. Relating your code to mine.... in my Page_Load I would do this...
TimeTrakkerContext context = new TimeTrakkerContext();
EntryEntity entry = context.EntryEntities.Where(ent => ent.Pk == 206).SingleOrDefault();

and then I would assign the entry values to controls on my .aspx page such as txtEntry.Text = entry.InvoicePk.ToString();

Now once the Page_Load is done I don't have to worry about clearing out the context because it's now out of scope and gone. I can edit the data on the .aspx page now and press a submit button. On submit I would have this...
EntryEntity entry = new EntryEntity();
entry.InvoicePk = int.Parse(txtEntry.Text);
TimeTrakkerContext context = new TimeTrakkerContext();
context.EntryEntities.Attach(entry, true);
context.SubmitChanges();


What you're doing in your code is just pulling in an Entity, changing a value and trying to resubmit it. I actually had some luck with that exact scenario by using an additional Detach() method that someone on another forum wrote about here http://msmvps.com/blogs/omar/archive/2007/12/08/linq-to-sql-how-to-attach-object-to-a-different-data-context.aspx

I don't think your concern about using the PK as IsVersion is a problem, at least not in my scenario. Because when you call Attach and pass in an entry that was never attached to anything as the first param and the boolean true as the second param you are telling the attach method that the entry Entity is a modified version of what already exists in the database. I'd have to double check the sql that gets written but I'm pretty sure LINQ is going to just do a comparison between the entryId in your entity and the entryId in the database and of course it will find a match. It will then compare the properties between your entry and the database entry and it doesnt care about the PK at that point because it is only used in the Where clause.
I hope this is more clear.

Steve B
# re: LINQ to SQL and Disconnected Entities Follow-up
by Rick Strahl April 11, 2008 @ 1:13pm
@Steve - Duh - I missed that you create a new entity. So you're creating a new entity, assigning a pre-existing PK and then assuming that to be fully updateable. Hmmm.. sure that works, but that assumes that you're actually updating everything. If you leave fields blank won't that wipe out existing fields when you call .Attach(entry,true) which marks all fields as udated effectively? Also have you tried this with related entities? .Attach works reasonably well with flat entities but once you have related data it often falls flat.

And using Attach() is expensive both in terms of the SQL generated as all fields are updated and the .NET processing that has to synch up the entities...

To be honest in Web applications and page scenarios LINQ to SQL's DataContext management isn't really a problem. There are lots of ways to deal with the lifetime of the context fairly effectively mainly because pages are transient and create new contexts for each load.

The above scenario can be addressed in many different ways. I tend to load the entity I'm updating first by its PK, then write the values from the POST back into the entity and then simply submitchanges. Logically that seems to make more sense to me. I use business objects to provide the entities and hold the context for me but the basic gist of it in raw LINQ to SQL code can be boiled down to this for an unbinding of values in say a Save button submit:

int pk = int.Parse(this.txtHiddenId.Text);
            
// *** Page level entity reference so we can bind against it
// *** Load with latest data from database
this.entry = customer.Context.CustomerEntities.SingleOrDefault(cust => cust.Pk == pk);

// *** Bind values from page back into entry entity
this.DataBinder.Unbind();

// *** Another field update
this.entry.Updated = DateTime.UtcNow;

// *** Related entity update
this.entry.User.LastEntryTime = DateTime.UtcNow;

// *** Save it
customer.Context.SubmitChanges();


FWIW, this model jives much better how LINQ to SQL wants to work than using entities that are passed to methods like Update and Save. I've talked about creating a business object wrapper around LINQ to SQL that provides high level business methods but still works in the context of how LINQ to SQL wants to work.

http://www.west-wind.com/WebLog/posts/160237.aspx

Thanks Steve... great discussion.
# re: LINQ to SQL and Disconnected Entities Follow-up
by Mohammad Azam May 09, 2008 @ 9:57am
Hi Rick,

For some reason I am still having problems with attaching the entities. I have already added the timestamp column in my table.
Here is my code:

var forum = new Forum();

DiscussionBoardDataContext dc1 = new DiscussionBoardDataContext();

forum = (from f in dc1.Forums
where f.ForumID == 23
select f).SingleOrDefault();

dc1 = null;


// now change the entity
forum.Title = "my new title of the forum";

using (DiscussionBoardDataContext dc2 = new DiscussionBoardDataContext())
{
dc2.Forums.Attach(forum,true);
dc2.SubmitChanges();
}
# re: LINQ to SQL and Disconnected Entities Follow-up
by Mohammad Azam May 09, 2008 @ 10:34am
Hi,

Seems like the problem is occurring because the Forum has many posts.
# re: LINQ to SQL and Disconnected Entities Follow-up
by Jason May 12, 2008 @ 7:18am
Hi,

Excelent post!
I have a big question.
I have a DataLayer using Linq, a Business Layer and in the UI I have a formview using the objectdatasource to edit the data.
The problem is in the Business layer I created a update method with the Entity as parameter.
When I click the Update method, the Entity object is empty. I just don't know how to use a context.

Thanks in advance,

Jason
# re: LINQ to SQL and Disconnected Entities Follow-up
by Daniel August 17, 2008 @ 11:56am
For what it's worth I've been using a 'playback' pattern to solve this across layers. Rather than attempt to clone state or use reflection to keep around the original entity, I update the entity on the client through an Action delegate containing the changes, and send the whole Action down to the data layer. Then on the data layer all I need to do is fetch the original entity and 'play' the delegate against it on the fresh data context and then submit my changes. Even though the changes came from the client, they are performed on the server. Sure, you pay a tax since you have to retrieve the entity twice (one when you fetched from the server, and again when you fetch the original to play the update), but it's much less than cloning or reflection.

Post is here: http://www.dimebrain.com/2007/12/using-linq-to-s.html
# re: LINQ to SQL and Disconnected Entities Follow-up
by kiran November 14, 2008 @ 1:36pm
Hi Rick

Thanks for the post. I tried the timestamp approach but I could not get it working.

Here is my scenario:

I'm using a webservice as middle-tier and I'm using custom data transfer objects between tiers. I did include time stamp columns in userlink,request table and set the UpdateCheck flag to Always
        public bool InsertUpdateTest(RequestDTO reqDTO)
        {
            MYDBDataContext myDataContext = null;

            bool isSuccess = true;

            try
            {
                myDataContext = new MYDBDataContext(connStr);

                 my_request draft = null;

                 if (reqDTO.reqId > 0)
                 {
  draft = (from r in myDataContext.my_requests where   
        r.req_id == reqDTO.reqId  select r).Single();
                 }
                 else
                     draft = new my_request();// linq entity

            // one to many relationship    

                foreach (UserDTO user in reqDTO.users)
                {
                    my_userlink ml = new my_ userlink(); // linq entity
                    if (user.id > 0)
                    {
                        ml.user_id = user.id;
                        ml.originallySelected = true;
                        draft.my_userlinks.Add(ml);
                    }
                }

                if (reqDTO.reqId > 0)
                {
                    myDataContext.my_requests.Attach(draft,true);
                    myDataContext.SubmitChanges();
                }
                else
                {
                    myDataContext.my_requests.InsertOnSubmit(draft);
                    myDataContext.SubmitChanges();
                }

                
            }
            catch (Exception ex)
            {
                isSuccess = false;
                myCommon.InsertAppLog("NewRequest", 
                                 "Error",ex.ToString(), 1)            
             }
            finally
            {
                myDataContext.Dispose();
            }

            return isSuccess;

        }


Please let me know if I'm missing something.

thanks
kiran
# re: LINQ to SQL and Disconnected Entities Follow-up
by Tom February 09, 2009 @ 11:29am
This is a fantastic post, and highlights one of my biggest disappointments with LINQ to SQL.

At this point, the post is now a year and a half old... has anything changed?
# re: LINQ to SQL and Disconnected Entities Follow-up
by andy April 19, 2009 @ 8:25pm
Hey Rick, great post by the way, super useful.

Quick question. I'm trying out your last option, which is to create another datacontext for when your tables don't have the timestamp field.

this works:

datacontext dc = new datacontext();
product p = dc.products.single(p=> p.id == 1);
dc = null;

product pToUpdate = new product(){.id = p.id, .name = p.name};

//do stuff within the same HTTP call

datacontext dc2 = new datacontext();
datacontext dc3 = new datacontext();

product pOriginal = dc3.products.single(p=> p.id == 1);

dc2.products.Attach(pToUpdate,pOriginal);
dc2.SubmitChanges();


but this doesn't:

datacontext dc = new datacontext();
product p = dc.products.single(p=> p.id == 1);
dc = null;

//do stuff within the same HTTP call

datacontext dc2 = new datacontext();
datacontext dc3 = new datacontext();

product pOriginal = dc3.products.single(p=> p.id == 1);

dc2.products.Attach(p,pOriginal);
dc2.SubmitChanges();


any idea why? i get the following 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.


cheers man
# re: LINQ to SQL and Disconnected Entities Follow-up
by Rick O'Shay May 25, 2009 @ 9:24am
Is the LINQ to SQL ORM fundamentally different than the battle-tested ORMs that have been in widespread use for a decade or more? It does not appear to be (they had many freely available models complete with source to borrow from) so why would "unmanaged" or "dettached" objects be different?

The context tracks state changes and if you have changes across requests then place the context in the conversation (e.g., an HTTP session context object). Alternatively fetch the object and update based on your own comparisons of the incoming "detached" object. This really does appear to justify a RTFM solution.
# re: LINQ to SQL and Disconnected Entities Follow-up
by Nigel June 15, 2009 @ 4:40pm
Honestly, how hard is it to force an update via the primary key of an object for disconnected entities, with a bool to check for concurrency, a lot of the time most people aren't concerned with concurrency.

Also timestamp field is to be deprecated from mssql in 2008, so wouldn't suggest using this type of DB datatype in the future.

What I don't understand is that the classes generated are made to serialize over remoting like WCF but when you return an object from the client and try to update the object you will get these errors.

Simplest way I've decided is to write your own update method in a wrapper/facade class that talks to linq for doing all the updates, gets, etc.. and connecting to the database directly using the datacontext's ExecuteCommand method..

e.g. for example...

public int UpdateUser(User user)
{
using (TransactionScope ts = new TransactionScope())
{

DB.ExecuteCommand("UPDATE Users Set userEmailLogin = {0}, UserPassword={1} WHERE UserId={2}", user.UserEmailLogin, user.UserPassword, user.UserId);
DB.ExecuteCommand("UPDATE UserAddress Set Address1 = {0}, Address2 = {1}, PostCode={2}, UserId={3} WHERE AddressId={4}", user.Address.Address1, user.Address.Address2, user.Address.PostCode, user.UserId, user.Address.AddressId);
ts.Complete();
}
}


I found this article which has an interesting work around. which I havn't tried yet..

http://geekswithblogs.net/michelotti/archive/2007/12/30/118076.aspx

Would be interested to see how poeple go with this.
# Using LINQ to SQL across layers : an alternative to reflection or cloning | dimebrain
by 汗水房 August 25, 2009 @ 9:50am
我只选择其中的几句重点翻译一下。1,对官方回复的解释是,DataContext对象不应该在实例化后长久的使用。……另外,从我所能判断的,LINQtoSQL事实上也并不是一个健全的ORM。
# re: LINQ to SQL and Disconnected Entities Follow-up
by Jean-Pierre Fouche October 23, 2009 @ 3:15am
If you are writing DB.ExecuteCommand update statements, what is the point of using Linq? We are back to SQL!
# re: LINQ to SQL and Disconnected Entities Follow-up
by Rick Strahl October 23, 2009 @ 4:03am
@jean pierre - Er, it's all SQL. Let's not forget that LINQ to SQL is just a SQL generator. In certain situations string sql queries are the only way to do certain things (especially with LINQ to SQL) or at least to do things WAY more efficiently than L2S does natively. For example, try to delete or update a group of items in batch based on a query expression - sure you can load up the entities and delete them all then save them all back, but that's hardly efficient. And there are many situations - especially in the generic tool level business layer - where dynamic queries are much more suitable than LINQ based queries.

No doubt application level code should use the model as much as possible but I feel a heck of a lot better knowing that if I have to I can fall back on string based queries to do what I need to get the data. L2S (and any model only framework) has gotten me into spots where it was painful to do something with LINQ/Model and it's easier to do it 'by hand'.
# I thought you could use LINQtoSQL disconnected?
by Adam January 05, 2010 @ 7:11am
Hi Rick!

I thought LINQtoSQL supported disconnected objects?

e.g.

Customer customer;
Order order;
 
using (var context = new SomeDataContext()) {
 
    // Just get the first, doesn't matter when playing!
    customer = context.Customers.First();
}
 
order = new Order();
order.Reference = "OrderRef";
order.Customer = customer;
 
using (var context = new SomeDataContext()) {
 
    // Doesn't work if you use InsertOnSubmit(), or any other Attach() overload.
    context.Orders.Attach(order);
    context.SubmitChanges();
}


Then you've got an order without it copying the customer! In all fairness you might need a TimeStamp column though. Let me know if it works for you?

Regards

Adam.
# Updates using LINQ to SQL WCF ASP.NET
by NepomucenoBR March 30, 2010 @ 8:01pm
Updates using LINQ to SQL WCF ASP.NET
# re: LINQ to SQL and Disconnected Entities Follow-up
by LeRoy DeNooyer August 17, 2010 @ 4:24pm
I used reflection and an extention method to disconnect an entity. To use, call:

//...
dbContext.Remove(entity)
//...

public static class Extensions
{
public static void RemoveItem(this DataContext context, Object compare)
{
// use reflection to get changed items from data context
object services = context.GetType().BaseType.GetField("services",
BindingFlags.NonPublic |
BindingFlags.Instance |
BindingFlags.GetField).GetValue(context);

object tracker = services.GetType().GetField("tracker",
BindingFlags.NonPublic |
BindingFlags.Instance |
BindingFlags.GetField).GetValue(services);
System.Collections.IDictionary trackerItems =
(System.Collections.IDictionary)tracker.GetType().GetField("items",
BindingFlags.NonPublic |
BindingFlags.Instance |
BindingFlags.GetField).GetValue(tracker);

// iterate through each item in context, adding
// only those that are of type TItem to the changedItems dictionary
object remove = null;
foreach (System.Collections.DictionaryEntry entry in trackerItems)
{
object original = entry.Value.GetType().GetField("original",
BindingFlags.NonPublic |
BindingFlags.Instance |
BindingFlags.GetField).GetValue(entry.Value);

if (entry.Key == compare || original == compare)
{
remove = entry.Key;
}
}
trackerItems.Remove(remove);
}
}
# re: LINQ to SQL and Disconnected Entities Follow-up
by Chris Webb October 07, 2010 @ 4:59am
Realise this thread's a bit old, but I created a completely generic functional implementation of this that uses reflection and specifically checks for the ColumnAttribute att on each DB property of the LinqSql Entity ->

        public T Save(T item)
        {
            // Does it exist?
            T existingItem = Table.FirstOrDefault(i => i == item);
 
            if (existingItem == null) // Insert (new)
            {
                Table.InsertOnSubmit(item);
            }
            else // Update (exists)
            {
                // Copy all changed [ColumnAttribute] property values to existing object in Linq Context
                item.GetType().GetProperties()
                    .Where(p => p.GetCustomAttributes(typeof(ColumnAttribute), false).Any())
                    .ToList()
                    .ForEach(p => p.SetValue(existingItem, p.GetValue(item, null), null));
            }
 
            Context.SubmitChanges();
            FinalizeQuery();
 
            return item;
        }
 


West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2014