LINQ to SQL N-Tier Smart Client - Part 1 Building the Middle-Tier

In my previous posts on LINQ to SQL I showed how to build LINQ to SQL classes and set up the data binding in your Windows applications. If you missed them:

Related Data Binding and ComboBoxes with LINQ to SQL

Creating Lookup Lists with LINQ to SQL

One-To-Many (Master-Detail) Forms with LINQ to SQL

Simple Validation with LINQ to SQL Classes

These articles focus on the binding and validation and use a connected model; meaning that the DataContext is alive and available tracking the changes we make to the collections of LINQ to SQL objects. Out of the box, LINQ to SQL is really easy to get working with a connected client-server architecture. As long as your objects are attached to the DataContext you get all the nice features like change tracking and automatic lazy loading of related collections. However, unlike the DataSet, once you disconnect from the data source you lose all automatic change tracking. So why use LINQ to SQL objects and not distributed DataSets?

The Great Debate - DataSets or objects?

It depends on your particular scenarios which approach you would take, and you are not limited to using just one approach in your application depending on your scenario. I always try to keep these guidelines in mind when I'm trying to decide which approach to take:

- DataSets are easy to define, serialize, and can now be easily separated from the data access logic in the TableAdapters. (Take a look at how to do this in VS 2008 in this video)

-DataSets have built-in change tracking, batched editing, viewing and filtering (also supports LINQ), and handles complex relationships automatically

-DataSets are easy to use with advanced DataBinding scenarios

-DataSets are easy to implement simple field and row validation but become challenging when complex behavior needs to be coded, they cannot inherit from a business class so complex business logic and data become separated

-DataSets should not be used if your clients are not all .NET clients because this type is a very complex type specific to .NET

-LINQ to SQL classes are plain old CLR objects (POCO) that only implement a couple property changed notification interfaces

-LINQ to SQL classes allow you full control over the validation, behavior, business logic, and can inherit from any class you provide so you can create a base business class

-LINQ to SQL object collections can be serialized as arrays to non-.NET clients

-LINQ to SQL objects, once detached from the DataContext, do not track changes or support any advanced DataBinding features out of the box, those must be implemented by you and the code is often non-trivial

Personally I like both approaches, it just depends on the scenario. Typically if I have a batch .NET data-entry form that I need to service with only a few field or column validations necessary, DataSets are my first choice, especially if there are more than a couple related tables. They are also a good choice for reports and simple view-only forms. If I have more complex rules and behavior or I need full control over my object hierarchy then POCO business objects are the better choice.

Going N-Tier with LINQ to SQL

If you're ready to jump off the cliff there's some really good information in the MSDN Library on how to set up LINQ to SQL in distributed applications. There are a variety of ways to handle LINQ to SQL in these scenarios as the library shows. In this post I'm going to walk through the most common scenarios to get this to work with a remote .NET Windows smart client (WPF coming soon!).  This post will focus on the data access and service layer and I'll follow up with the client in the next post.

We're going to continue with the application that we built in the previous articles which was just a single Windows forms client built into one EXE. What we need to do is modify the design by adding a data access layer that communicates with our database as well as a service layer that the client will communicate with. Since our client and our server are both written in .NET I am also going to reuse the LINQ to SQL objects that contain our validation so we can keep those rules in one place. Please note that this is not SOA (Service Oriented Architecture). If you need to go that route then I highly recommend this book.

So here's a diagram of what we're going to implement.

The first thing to do is add a Class Library project called OMSDataLayer to contain the data access layer and the generated LINQ to SQL classes (our business objects) along with the partial class code and base business object class. Unfortunately the O/R designer doesn't let you separate the generated classes from the DataContext object (database access) like the DataSet designer does so if you really want to be a stickler and you have a large project you're developing you should create your own classes and then you can decorate them with the right attributes to get them to work with LINQ to SQL or use an external mapping file.

Next add a reference to System.Data.Linq to the class library and then move all the associated .dbml files as well as the BaseBusiness and ValidationException classes into the project. Then add a project reference to this OMSDataLayer to the client project. At this point if I want the client to compile and work exactly like before I can add a project level imports to the OMSDataLayer on the client, Import the OMSDataLayer namespace, and run it like before (we just have to go through and change the namespace on our objects on our form).

Finally, add a new WCF Service project to the solution and add the OMSDataLayer as a project reference. Now we've got the structure of our solution set up.

Contract First

Now it's time to think about the interaction our client will need to have with our service. It's always a good idea to think about how the endpoints in your distributed applications need to communicate with each other before you start developing them. For this application we only need interfaces for retrieval of the Orders and Products and saving and deleting Orders and OrderDetails. So I defined the following operations:

 Imports OMSDataLayer

<ServiceContract()> _
Public Interface IOMSService

    <OperationContract()> _
    Function GetOrdersByCustomerID(ByVal customerID As Integer) As IEnumerable(Of Order)

    <OperationContract()> _
    Function GetProductList() As IEnumerable(Of Product)

    <OperationContract()> _
    Function SaveOrders(ByRef orders As OrderList) As Boolean

    <OperationContract()> _
    Function DeleteOrders(ByVal orders As IEnumerable(Of Order), _
                          ByVal details As IEnumerable(Of OrderDetail)) As Boolean

End Interface

I'm just returning a collection of our business objects in the Get* methods and I've defined Save and Delete methods that accept a collection of orders to either Save (update or insert) and Delete against the database. I'm sending Orders and OrderDetail collections in the case of Delete because I implemented a very simple change-tracking strategy and I want to be able to delete OrderDetails independently. OrderList is a simple Serializable class definition that inherits from List(Of Order) and has the KnownType attribute applied. This is needed so I can pass the Orders collection by reference so that primary keys and validation messages are sent back on the objects cleanly. (There are many ways to do this like passing messages and keys back separately. This is the easiest and cleanest in my opinion but you need to be aware that there is more data on the wire in this approach.)

 <KnownType(GetType(Order))> _
<Serializable()> _
Public Class OrderList
    Inherits List(Of Order)

End Class

The last step to defining our contract is to set up our business objects so that they can be serialized as a WCF DataContract. We can do this via the O/R designer. Just open up the OMS.dbml file and in the properties for the DataContext set the Serialization mode to "Unidirectional".

This adds the DataContract attribute to the classes and the DataMember attribute to the properties. If we're using a WCF service with the DataContractSerializer then all this will work just dandy. This is because this serializer supports bi-directional relationships (like we have with Order and OrderDetail). However if we were trying to serialize this with the XMLSerializer used with non-WCF services we'd have issues; you'd only be allowed to serialize objects with no cyclic relationships.

We also need to add the DataContract and DataMember attributes to our BaseBusiness class as well because our objects inherit from this class. Also add a property called IsDirty to the BaseBusiness class that will help us later when we need to implement change tracking on our client. In the next post I'll talk about my approach to doing that when we talk about the client.

Disconnected Retrieval

Now that we have our contracts and solution set up we're ready to start partying on the data and write our data access layer code. Let's start with retrieving our Products and Orders. To recap what I mentioned in previous articles, when working in connected mode with the DataContext, collections of objects are not retrieved until they are accessed. This means that if I write a LINQ query to select an Order from the database it will not execute any statements to retrieve the OrderDetails until I attempt to access that child collection. This is a good thing because it saves calls to the database if they are not needed.

However when we are disconnected like in a distributed application there is no automatic lazy loading available, you have to roll your own. You need to decide how chatty you want to be in favor of less data on the wire. If you expect the client to be scrolling through smaller child collections it's probably better to just bring down a larger chunk of data in one call instead of making the client call the service multiple times. (Just to be clear, the client can still perform LINQ queries on the collections on the client-side to do additional filtering but that will be performed with standard LINQ to Objects, LINQ to SQL is only used on our server in the middle-tier.)

In this example, we won't need any child collections filled on Products, we're just using it as a lookup list. However, with Orders I want to pull all the Orders that haven't shipped for a specific customer ID and I want to also pull down those OrderDetails all in one call.

So now create a OMSDataManager class in our data access layer which our service implementation can call into. It's really easy to retrieve the Products. Note that the objects are automatically detached from the DataContext when they are serialized to the client through the service so we don't have to explicitly detach them here.

 Imports System.Data.Linq

 Public Class OMSDataManager

    Public Shared Function GetProductsList() As IEnumerable(Of Product)
        'Create the Datacontext
        Dim db As New OMSDataContext

        'Return the list of products. 
        'Objects are detached from the DataContext 
        ' automatically on serialization through the service
        Return db.Products.AsEnumerable()
    End Function

Now with Orders because we want load the OrderDetails as well we need to specify this in a DataLoadOptions object and pass them to the DataContext. The way we use this object is we call the LoadWith method to specify which data related to the main target should also be retrieved at the same time, resulting in one trip to the database. You pass it a Lambda expression to specify this. So to retrieve the Orders and OrderDetails in one LINQ to SQL query we can write:

  Public Shared Function GetOrdersByCustomerID(ByVal customerID As Integer) As IEnumerable(Of Order)
    'Create the Datacontext
    Dim db As New OMSDataContext

    'We want to also retrieve the OrderDetails collection as well.
    Dim dlo As New DataLoadOptions()
    dlo.LoadWith(Of Order)(Function(o As Order) o.OrderDetails)
    db.LoadOptions = dlo

    Dim orders = From order In db.Orders _
                 Select order _
                 Where order.CustomerID = customerID AndAlso _
                       order.ShipDate Is Nothing

    Return orders.AsEnumerable()
End Function

Disconnected Updates and Deletes

Disconnected updates are a bit more involved from a decision-making point of view. The way you update your data depends on the schema of your database and how you are performing concurrency checking. The documentation in the library goes into details on all the ways you can update your data but the simplest is using a timestamp field to track row modifications.

In our OMS database I have a "Modified" field specified on every table and I'm using that field to perform the concurrency checks. This makes it much easier to deal with updates on our middle-tier because that field's value is used to compare with the property value on the object and the update will only succeed if the values match. If you don't provide a RowVersion mechanism like a timestamp on your tables then you have to pass all the original values into the middle-tier as well. So I'm all for saving space on the wire and writing less code and timestamps are a common way to do row versioning and concurrency checking so I feel that this is the best approach for most applications.

The other issue you need to consider is what the best way is to work with your data. Is it better to update one entity at a time (for instance just sending one Order at a time to save) or is it better to accept a collection of updates and make just one chunky call to the middle-tier? Or a combination? I've almost always gone with the batch editing approach in the types of Windows applications I've written. Although there is more data on the wire, there's less calls to the middle-tier. So the client form caches all the updates, inserts and deletes to the collections and passes that around. You really need to watch how much data you're passing around, so in this example we're only pulling up open orders for a particular customer. Remember every scenario needs to be evaluated carefully. I took a simple approach, more data on the wire but 2 calls to the middle-tier, one to update/insert data and one call to delete data.

You can streamline this more by only sending the changes to the middle-tier from the client but then you need more code to figure out how to merge the middle-tier changes (validation messages and keys, for instance) back into the client. This is really easy to do with if we were using DataSets but for this example I took to simple route and am passing the entire collection -- but be warned -- this approach may not scale depending on your situation. As always, your mileage may vary.

So let's write our Save code for our Orders. Unlike when using DataSets with the TableAdapter, the DataContext in disconnected mode needs to be told explicitly what to do, insert, update or delete. An easy way to tell whether the objects in our collection are added or modified is by checking the primary key; if it's less then 1 then we know we have a new object to save. We also use the IsDirty flag we added on our BaseBusiness class to determine how to attach our objects to the DataContext.

 Public Shared Function SaveOrders(ByVal orders As IEnumerable(Of Order)) As Boolean
    If orders Is Nothing OrElse orders.Count = 0 Then
        Return False
    End If

    Dim db As New OMSDataContext

    For Each o In orders
        If o.OrderID = 0 Then
            db.Orders.InsertOnSubmit(o)
        Else
            db.Orders.Attach(o, o.IsDirty)
        End If

        For Each d In o.OrderDetails
            If d.IsDirty Then
                If d.OrderDetailID = 0 Then
                    db.OrderDetails.InsertOnSubmit(d)
                Else
                    db.OrderDetails.Attach(d, True)
                End If
            End If
        Next
    Next

    Try
        'This will continue to process the 
        'rest of the orders even if one fails
        db.SubmitChanges(ConflictMode.ContinueOnConflict)

        'Reset the IsDirty flag
        For Each o In orders
            o.IsDirty = False
            For Each d In o.OrderDetails
                d.IsDirty = False
            Next
        Next

    Catch ex As ChangeConflictException
        'TODO: Conflict Handling
        Throw
        Return False
    End Try

    Return True
End Function

Disconnected deletes of Orders in our case are very easy because of the way we set up how deletes should work by manually setting setting the DeleteOnNull attribute to true (see the end of this post for info on that). We just need to attach the incoming Orders and OrderDetails to the DataContext and then we can delete them all.

 Public Shared Function DeleteOrders(ByVal orders As IEnumerable(Of Order), _
                                    ByVal details As IEnumerable(Of OrderDetail)) As Boolean

    Dim db As New OMSDataContext
    Dim submit = False

    If orders IsNot Nothing AndAlso orders.Count > 0 Then
        'Delete orders and related details
        db.Orders.AttachAll(orders, False)
        db.Orders.DeleteAllOnSubmit(orders)

        For Each o In orders
            For Each detail In o.OrderDetails
                db.OrderDetails.DeleteOnSubmit(detail)
            Next
        Next
        submit = True
    End If

    If details IsNot Nothing AndAlso details.Count > 0 Then
        'Now delete the order details that were passed in
        ' (these order parents were not deleted, just the details)
        db.OrderDetails.AttachAll(details, False)
        db.OrderDetails.DeleteAllOnSubmit(details)
        submit = True
    End If
    Try
        If submit Then
            db.SubmitChanges(ConflictMode.ContinueOnConflict)
            Return True
        End If

    Catch ex As ChangeConflictException
        'TODO: Conflict Handling
         Return False

    End Try
End Function

The last thing we need to do to our middle-tier is call the data access layer from our service implementation. Note that I'm catching the ValidationException that's thrown from our business objects if they are invalid when the call to db.SubmitChanges is made and just returning false, which keeps our service from entering the fault state. The client will also perform validation of our business objects before the data is submitted to save a round-trip but the validation is also run here on the middle-tier. Validation messages are collected in a dictionary and serialized back to the client. (Read this post on how we set up validation on our LINQ to SQL classes.)

 Imports OMSDataLayer

Public Class OMSService
    Implements IOMSService

    Public Sub New()
    End Sub

    Public Function GetOrdersByCustomerID(ByVal customerID As Integer) _
    As IEnumerable(Of OMSDataLayer.Order) _
    Implements IOMSService.GetOrdersByCustomerID

        Return OMSDataManager.GetOrdersByCustomerID(customerID)
    End Function

    Public Function GetProductList() As IEnumerable(Of OMSDataLayer.Product) _
    Implements IOMSService.GetProductList

        Return OMSDataManager.GetProductsList()
    End Function

    Public Function DeleteOrders(ByVal orders As IEnumerable(Of OMSDataLayer.Order), _
                                 ByVal details As IEnumerable(Of OMSDataLayer.OrderDetail)) _
                                 As Boolean _
                                 Implements IOMSService.DeleteOrders

        Return OMSDataManager.DeleteOrders(orders, details)
    End Function

    Public Function SaveOrders(ByRef orders As OrderList) As Boolean _
    Implements IOMSService.SaveOrders

        Try
            Return OMSDataManager.SaveOrders(orders)

        Catch ex As ValidationException
            Return False
        End Try
    End Function
End Class

So that's the meat of our middle-tier. In the next post we'll build our client form and implement a simple technique for change tracking.

UPDATE: Read how to create the client in this post.

Enjoy!