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!
Â






Boy, what a start to the new year! After having spent the last three weeks on the road, I’ve just arrived back at our new home in Dallas, Texas. On Jan 2nd I flew to New Orleans to take part in a company retreat for instructors at DevelopMentor. It was a lot of fun, and I got to meet others who teach for the same company I do. Then I flew to Los Angeles to load a truck with all our stuff from storage and haul it out to Texas, towing one of our cars while a friend drove the other car. In the course of loading the truck, one of the people helping me move managed to run into my left calf with a large hand dolly. Ever since then, I’ve been hobbling around on crutches and trying to treat my leg with ice, ace bandages, and elevation — which has made the rest of my trip especially challenging.
Moving from Los Angeles to Texas was a real adventure. My leg was severely injured and I was in a lot of pain. I couldn’t carry anything, and getting from one place to another was a chore. I could barely manage to climb into the cab of the truck, and spending 12 hours a day driving for three days put a real strain on it. By the time I got to Texas, my foot was swollen like a balloon and I had to go to the emergency room to have it looked at. My friend Peter Ruffner took his 86 year old father along for the 3 day drive from California to Texas, so it was quite a sight seeing his father and I using walkers to get to the rest room whenever we stopped.
After arriving with the truck and our two cars in Texas, we picked up my wife Zuzana and our son Kerrigan at the airport. They flew up to Dallas from College Station, where they had been staying with a friend. No sooner had we moved into the apartment than I hopped on a plane to fly back to Los Angeles for a teaching engagement. My leg was in bad shape, so I had to teach the class sitting in a chair with my leg packed in ice and propped up on the desk. Each day I had to get a ride to the office from a co-worker, even though the hotel was in walking distance. After that week, I flew back to Texas for the weekend, before having to take off again for another teaching assignment, this time in New York City!
My leg has finally started to feel a little better, but my foot is still swelling up from time to time. I’m hopeful it will heal so that I can get off the crutches and start walking on my own. Today we all went to Wal-Mart to buy household essentials. Thankfully, Wal-Mart has electric carts available for handicapped shoppers. I spent a couple hours tooling around the store, while Kerrigan rode in a special cart for children, which was equipped with a built-in TV monitor with running cartoon shows!





