How to Make LINQ to SQL Check for Changes After Attach (or AttachAll)
(Jan 09 2009 - 09:04:49 AM
by Timothy Khouri
) - [print blog
For the past hour or so, I've been struggling with something that I thought would be very easy for LINQ to SQL to handle on it's own - that is - performing an "IsDirty" check of sorts. It turns out the solution *is* easy, but it's just not what I was thinking to do at first.
Here's the scenario: The application I'm working on is a semi-disconnected client/server system. What I mean by that is, the client (WPF application) will connect to the server (using WCF) to download some records. The client can edit and add items to the list, and then when they want to save, it sends the complete list back up to the server (to either update, or add the items).
Well, since some of the items were already in the database, I can't just call "InsertAllOnSubmit", because LINQ will yell at me for trying to insert duplicate items.
Also, since some of the items may not already be in the database, I can't just call "AttachAll", because then LINQ will yell at me again for 'adding the same key twice'.
Note: The "same key" being used is actually the ID "0". The reason for this is that the "ID" field will be auto-generated by the server. So, if you try to attach two or more entities that are new (therefore have ID 0), then it seems that you are creating a duplicate entry.
How to "add or update" entities in LINQ to SQL
The solution here was pretty simple. All items in the list that have an ID need to be 'attached', and all items that don't have an ID need to be 'inserted'. So, here's what my code looks like:
public void SaveUserSettings(UserSetting settings)
using (var dataContext = new MyDataContext())
dataContext.UserSettings.AttachAll(settings.Where(item => item.ID != 0));
dataContext.UserSettings.InsertAllOnSubmit(settings.Where(item => item.ID == 0));
This seemed to work great - new items were being added! But, there is a problem with the above code: existing items weren't being updated. "Why?" you might ask. The answer (now that I understand it better) is simple:
LINQ to SQL assumes that the object you are attaching is un-modified. True, there is an overloaded method "AttachAll(items, true)" that forces LINQ to view those objects as 'modified', but that method only works if your table has a TimeStamp field.
The error message that you'll get if you try to use the above overload is: "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." - You can set every field to check for updates "Never", but then you lose the ability to know if another user has change the database in the mean time.
So, how do I tell LINQ to SQL to see if there have been changes made to already-existing entities? By using the "Refresh" method:
That's it! Now I am explicitly telling LINQ to SQL to check if there were any updates to those objects and to keep the new values. As a result, the DataContext is now aware of any changes, and will update them accordingly when "SubmitChanges" is called.