The ability to use live Linq queries right in your UI makes for great demo's, but it doesn't bear a striking resemblance to a real-world, professional application which uses tiers. In traditional n-tier applications, you want to have a strong "separation of concerns" and encapsulate your business layer, your data layer, and your UI layer distinctly. One of the nice things about Linq is that the flexibility is huge. If you want to do live queries in your UI, fine. If you want to encapsulate Linq queries in your data layer, that's fine too.
Having said that, the biggest problem I faced when using the RTM for the first time was trying to update an object that had been created by a "different" data contact. I continually ran into one of these dreaded exceptions: "System.InvalidOperationException: An entity can only be attached as modified without original state if it declares a version member or does not have an update check policy." The other one was: "An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext."
Microsoft has documentation here that is meant to describe how to properly implement this scenario. The key to the update are these bullet points:
LINQ to SQL supports updates in these scenarios involving optimistic concurrency:
- Optimistic concurrency based on timestamps or RowVersion numbers.
- Optimistic concurrency based on original values of a subset of entity properties.
- Optimistic concurrency based on the complete original and modified entities.
But they never really gave any concrete example of implementation. So here is a quick example of how to avoid this.
OK, here is my (ridiculously simple) table:
CREATE TABLE Contacts(
ContactID int IDENTITY(1,1) NOT NULL,
FirstName varchar(50),
LastName varchar(50),
[Timestamp] [timestamp] NOT NULL,
CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED (ContactID ASC)
)
Next, drag out the table from the Server Explorer onto a dbml surface:
If right-click on the Timestamp column in the dbml above and select "Properties", you'll see this Properties window:
Notice the Auto Generated Value and Time Stamp properties are both set to true. This is key.
Now let's suppose I create a ContactManager class that is going to be my public API that will encapsulate all of my CRUD functionality. (In fact, I can make my Linq data context classes all Internal so my UI truly does not know about them)
1: public static class ContactManager
2: {
3: public static Contact GetContact(int contactID)
4: {
5: using (ContactsDataContext dataContext = new ContactsDataContext())
6: {
7: return dataContext.Contacts.SingleOrDefault(c => c.ContactID == contactID);
8: }
9: }
10:
11: public static void SaveContact(Contact contact)
12: {
13: using (ContactsDataContext dataContext = new ContactsDataContext())
14: {
15: if (contact.ContactID == 0)
16: {
17: dataContext.Contacts.InsertOnSubmit(contact);
18: }
19: else
20: {
21: dataContext.Contacts.Attach(contact, true);
22: }
23: dataContext.SubmitChanges();
24: }
25: }
26: }
Notice that I'm disposing my data context each time so I truly can support a stateless n-tier service. Also, notice I am calling the Attach() method (line 21 above) and giving the second parameter as "true" - meaning, attach as modified. I have to call Attach() here because the original data context that created my object isn't around anymore. I have to attach it as modified so that the framework will understand that my intent is to perform an update here. Additionally, a look at the data context's Log property shows the SQL that was actually emitted during run-time execution:
1: UPDATE [dbo].[TempContacts]
2: SET [FirstName] = @p2, [LastName] = @p3
3: WHERE ([ContactID] = @p0) AND ([Timestamp] = @p1)
So, the timestamp is taken into account as well so that full Optimistic concurrency is supported.