If you are attending the Microsoft TechEd Conference taking place June 2-6, 2008 in Orlando, Florida, if even if you’re not, this is the place where you can get slides, code samples and hands-on labs for my presentation on “Real-World LINQ to SQL”:

RW-L2S Slides
RW-L2S Hands-on Labs

To complete the labs, you’ll also need a slightly modified version of the AdventureWorks sample database. Click the previous link to download the database (about 3 MB compressed), then attach it to an instance of SQL Server (Standard or Express), and add it as a data connection in the Visual Studio Server Explorer. To run the client, you’ll also need to install the free Xceed WPF Data Grid.

There is a common misconception that LINQ to SQL is intended to be used only for RAD applications or simple proof-of-concepts, but is not cut out for real-world line of business applications. However, the reality is that L2S has a great deal of support for real-world scenarios, and you shouldn’t be afraid to use it in a production environment. I like to divide L2S’s real-world features into two categories: 1) service-orientation and 2) production-readiness. Here is a summary of what L2S has to offer in each of these areas:

• Service-Oriented LINQ to SQL
  • Entity Serialization
  • Attaching Disconnected Entities

     

• Production-Ready LINQ to SQL
  • Concurrency Management
  • Stored Procedures for CRUD Operations
  • Extensibility Points for Business Logic
  • Transaction Support

While most L2S examples have LINQ to SQL on the client talking to a back end SQL Server database, there is fairly good support for utilizing LINQ to SQL with service-oriented application architectures (also referred to as n-tier), where you might have a WCF service that acts as a Data Access Layer (DAL) and encapsulates communication with the database, providing Plain Old C# Objects (POCO) to the client. In this scenario the client has no knowledge of LINQ to SQL, or any other persistence technology, and is said to be persistent ignorant (hopefully). The designer-generated data context that you get when adding a “LINQ to SQL Classes” item to your service project has a Serialization property which you can either set to None (the default) or Unidirectional (there is a corresponding parameter for the SqlMetal command-line tool). What this means is that entities will be marked with a [DataContract] attribute, making them serializable, and association properties representing one-to-many relations will be marked with a [DataMember] attribute.

What unidirectional serialization means is that relations that go in the opposite direction, many-to-one, are not given a [DataMember] attribute. So, for example, if you have an OrderDetail table with a ProductID that acts as a foreign key to a Product table, the OrderDetail entity has a Product property representing the many-to-one relation. In a 2-tier application with L2S on the client, all you have to do is traverse the Product property of OrderDetail to get the ProductName or other field from the Product entity. However, with unidirectional serialization, the Product property of OrderDetail is not assigned a [DataMember] attribute and therefore is not part of the entity that is serialized and sent to the client from the WCF service.

The way to overcome this limitation is to create a partial class for OrderDetail that includes a ProductName property with a [DataMember] attribute. In the property getter all you have to do is reference the ProductName property of the OrderDetail’s Product property. There’s no need for a property setter, since the underlying Product property is populated when the OrderDetail is loaded from the database. You just have to make sure to eager-load Product with OrderDetail by creating a DataLoadOptions object, calling LoadWith to specify the Product property, and the setting it to the LoadOptions of the data context. The hands-on labs for my TechEd presentation contains step-by-step instructions for doing this, along with “before” and “after” code samples.

Because we want to use POCO objects on the client, there needs to be some mechanism for tracking object changes on the client and then communicating those changes to the service. For a high-level object, such as Order, we can have separate service operations for CreateOrder, UpdateOrder and DeleteOrder. However, we would ideally like to handle creating, updating and deleting OrderDetails when we update a specific order, and we would only like to pass into the service those order details that have been modified. To pull this off, we need an agreed-upon data schema to represent object state. For our purposes an enum will do fine. My example has a TrackingInfo enum marked with [DataContract] that has values forUnchanged, Created, Updated, Deleted, each of which are marked with a [EnumMember] attribute.

On the client there needs to be a smart change-tracking collection that sets a TrackingState property on order details and has a GetChanges method that returns only modified items. Because my samples use a WPF client, I have a ChangeTrackingCollection<T> that extends ObservableCollection<T> (it’s just as easy to extend BindingList<T> for Windows Forms clients). It overrides both InsertItem and RemoveItem, adding removed items to a private Collection<T> before calling base.RemoveItem. It also constrains T to implement INotifyPropertyChanged so that it can mark an item as Updated when a property value has changed. This works because by default entities that are generated when adding a WCF service reference to a client application all implement INotifyPropertyChanged, in order to support two-way data binding. On the service-side, we simply inspect the TrackingState property of order details and invoke the appropriate API on the data context for attaching, inserting or deleting entities. Again, the hands-on lab will take you though the steps for achieving this result, and I will further expand on this aspect in a future blog post or article.

This is how you can incorporate LINQ to SQL into a service-oriented application architecture. Now you need to make your application production-ready. Thankfully, LINQ to SQL has excellent support in this area, including support for concurrency management, stored procedures, data validation and business logic, and transactions. All of these are well documented, but the examples are based on a 2-tier application architecture. There are additional considerations when using these features with a service-oriented application architecture, and this is what I take you through in the hands-on labs. So check it out, and feel free to post comments on this blog, or to email me directly at tonys@develop.com. Happy LINQ-ing!