Multi tier architecture for Linq to Sql

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.

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.

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