This project is now being hosted on CodePlex

http://www.codeplex.com/MultiTierLinqToSql

Please post any questions etc. over there as this site will no longer be updated

Multi tier architecture for Linq to Sql

Introduction

This resource shows an approach for using Linq to Sql within a multi tier Asp.Net application, and particularly how it can be made to work well with ObjectDataSources. It is here as a discussion piece as well as a usable bit of code.

The key features are a Request-scoped datacontext and entity controller classes which provide easy to use Create, Retrieve, Update and Delete (CRUD) operations implemented with Generics. It also discusses the use of DataLoadOptions and approaches to submitting changes back to the database.

Entity Controller classes

Each database entity has an associated controller class which is used to perform common CRUD operations. Specific entity controllers inherit from a generic class GenericController which already implements all the basic Select, Insert, Update & Delete operations.

[System.ComponentModel.DataObject]
public class GenericController<TEntity, TDataContext> where TDataContext : DataContext
{
    public static List<TEntity> SelectAll()
    {
          ...
    }
    public static void Insert(TEntity entity)
    {
        ...
    }
    public static void Update(TEntity entity)
    {
        ...
    }
    public static void Delete(TEntity entity)
    {
        ...
    }
}

Specific controllers then inherit from this by specifying the entity type which they are concerned with as well as the type of the DataContext used, as follows:

//ProductController class
public class ProductController : GenericController<Product, NorthwindDataContext>
{
}

which allows client code such as the following:

   IList<Product> products = ProductController.SelectAll();

The use of Generics means that I don't need to add any code at all into my ProductController to get all the standard CRUD operations in a fully type safe way. My ProductController class can now be customised to add any further data access methods which are specific to Products, for instance GetByCategoryId().

Request scoped DataContext

The second part of this solution is the request scoped DataContext. This is a pattern that is commonly used with the Hibernate OR mapper as well as elsewhere, and it means that there is only ever one DataContext per Http request which is stored in the HttpContext.Items collection. It is implemented as a protected property of the GenericController class as follows:

        protected static TDataContext DataContext
        {
            get
            {
                //We are in a web app, use a request scope
                if (HttpContext.Current != null)
                {
                    TDataContext _dataContext = (TDataContext)HttpContext.Current.Items["_dataContext"];
                    if (_dataContext == null)
                    {
                        _dataContext = Activator.CreateInstance<TDataContext>();
                        HttpContext.Current.Items.Add("_dataContext", _dataContext);
                    }
                    return _dataContext;
                }
                else
                {
                    //If this is not a web app then just create a datacontext
                    //which will have the same lifespan as the app itself
                    //This is only really to support unit tests and should not
                    //be used in any production code. A better way to use this
                    //code with unit tests is to mock the HttpContext
                    if (_dataContext == null)
                    {
                        _dataContext = Activator.CreateInstance<TDataContext>();
                    }
                    return _dataContext;
                }
            }
        }

Using the HttpContext.Items collection does introduce a dependency on System.Web to your data layer, but I've made the decision that since this solution is specifically designed for use in Asp.Net applications, this is acceptable. Checking for a valid HttpContext and then using an static variable if it doesn't exist gives a bit of flexibility to use the class outside of a web app, for instance in unit tests, although this will produce subtly different results, so mocking the HttpContext would probably be a better approach.

The usefulness of this approach can be seen if you consider a page which uses a number of ObjectDataSources; if we configure our ObjectDataSource to use the DataContext directly then each one will create and dispose of a new DataContext this will not only quickly become a performance overhead, but will also present us with a problem when specifying LoadOptions for the DataContext resulting in more complex, less maintainable code or quite possibly code which doesn't specify loading options at all.

It should be noted that this approach means that if I have a CategoryController class as well as a ProductController class, both of which inherit from GenericController, both of these classes will use the same DataContext. In other words the DataContext is shared across the whole request, not just by one class type.

Custom ObjectDataSource

In order for updates to work correctly with databinding it is necessary to use the GenericObjectDataSource control which is included in the DataLayer project. For more discussions of the details behind this, see https://archive.msdn.microsoft.com/Thread/View.aspx?ProjectName=multitierlinqtosql&ThreadId=488

DataLoadOptions

Before going any further, we need to look at the issue of DataLoadOptions. As a number of people have noted the way that Linq to Sql works, means that the DataLoadOptions for your DataContext need to be set before any operations are carried out, and cannot subsequently be changed. This means that in my Asp.Net application I will need to set the DataLoadOptions for the whole request somewhere near the start (assuming of course that I need to set DataLoadOptions at all). It also means that my UI or business layer will need to take responsibility for this.

What this means in practice is that in a classic Web forms type app I will probably set the DataLoadOptions in the OnInit event of my page, if I'm using MVC then my controller will do it. I don't really have a problem with this, particularly in the case of an MVC app as I think this is probably the best place to actually specify how you want your data loading logic to work. I'm interested to hear other opinions on this.

Submitting changes

Another feature of the Linq to Sql architecture is the way that changes are only submitted to the database when the SubmitChanges() method is called on the DataContext. This means that we must either decide at which point to call SubmitChanges() or delegate this responsibility to client code.
To make the classes as flexible as possible any operations which require a call to SubmitChanges (Insert, Update and Delete), are provided with a boolean parameter which specify whether to submit changes immediately or not. There is also an overload of these methods without this parameter which is equivalent to passing true (again this makes use of ObjectDatasources much more straightforward).
The SubmitChanges method of the DataContext is also exposed through the controller class to give Client code the ability to call this explicitly.

New stuff added in Release 1.2

There are now a number of new methods added to the GenericController class to automatically support paging and sorting in GridViews.
These are:

public static IQueryable<TEntity> SelectAll(int maximumRows, int startRowIndex)
{
...
}
public static IQueryable<TEntity> SelectAll(string sortExpression)
{
...
}

public static IQueryable<TEntity> SelectAll(string sortExpression, int maximumRows, int startRowIndex)
{
...
}
public static int Count()
{
...
}

To use this functionality all you need to do is turn it on in the ObjectDataSource and GridView with the following attributes:
ObjectDataSource:
EnablePaging="true" SelectCountMethod="Count" SortParameterName="sortExpression"

Gridview:
AllowPaging="true" AllowSorting="true"

And hey presto it just works! This makes it really easy to create sortable, pagable GridViews with no extra coding.

This release also includes some code from Scott Gu's blog to support Dynamic Querying, see http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx for more details

New stuff in release 1.6

Logging

There is now an easy way to configure Linq to Sql's logging feature (DataContext.Log) to write to either a text file or the System.Diagnostics.Debugger class which then appears in the output window within Visual Studio.

To use this functionality you need to add the following lines into your web.config:

In the <configSections> element:

<section name="GenericDataSection" type="DataLayer.GenericDataSection, DataLayer" allowDefinition="MachineToApplication" restartOnExternalChanges="true" requirePermission="false"/>

Then directly after <configSections>:

<GenericDataSection LogType="file" LogFile="C:\Projects\MultiTierLinqToSql_release\test.txt" DeleteExistingFile="true"/>

The first of these lines just declares the second line which is the one that contains the interesting stuff.
There are 3 config options in this section:

1. LogType - this can be either 'file' (logs output to a file), 'debug' (logs output to Systems.Diagnostics.Debug) or 'none' (switches logging off).
2. LogFile - only applies when LogType is set to 'file', and the default is 'log.txt' it specifies the name of the file to use for logging.
3. DeleteExistingFile - only applies when LogType is set to 'file', values can be 'true' or 'false', and the default is true. When this is set to true the file will be overwritten every time a new DataContext is created, effectively once per request. If set to false then all output is appended to the end of the file. This can result in a very large log files so be careful.

Notes

As with all file logging this can be pretty detrimental to performance and so should only be used when debugging.

To handle the logging to the output window I have used the DebuggerWriter class described here: http://www.u2u.info/Blogs/Kris/Lists/Posts/Post.aspx?ID=11

The code

The code consists of 2 projects

• The data access layer - this is made up of Linq to Sql dataclasses generated from the Northwind database, a class GenericController (discussed above) and two concrete subclasses ProductController and CategoryController.
• A test web project - this project has one page with a GridView and a FormView which use the data layer via ObjectDataSources to list all the products in the Northwind database, allow editing and deletion of those projects, and insert new products via the FormView.

To run the example project:

• Download and unzip the release
• Open the solution in VS2008
• Change the connection string in the web.config to point to a Northwind database
• Run the web project

To use the code in your own projects

• Copy the class GenericController into your Linq to Sql project
• Create one entity controller class for each Linq to Sql entity, inheriting from GenericController
• Optionally define any new entity specific data access operations in your entity controller classes