Multi tier architecture for Linq to Sql


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 repository classes which provide easy to use Create, Retrieve, Update and Delete (CRUD) operations implemented with Generics. Also included is a T4 template for auto generation of repositories from a dbml file.

See here for a quick start introduction to using the code

Generic Entity Repository

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

public class GenericRepository<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 repositories 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:

//ProductRepository class
public class ProductRepository : GenericRepository<Product, NorthwindDataContext>

which allows client code such as the following:
   var productRepository = new ProductRepository();
   IList<Product> products = productRepository.SelectAll();

The use of Generics means that I don't need to add any code at all into my ProductRepository to get all the standard CRUD operations in a fully type safe way. My ProductRepository class can now be customised to add any further data access methods which are specific to Products, for instance GetByCategoryId().
There is also a class GenericStaticRepository which allows you to use this functionality in a static way so in the example above, rather than instantiating a ProductRepository I could simply do this:
   IList<Product> products = ProductStaticRepository.SelectAll();

All of these concrete classes are auto generated by the T4 template included with the project as described below.

T4 Template

Included in the source code is a T4 template which will generate entities and concrete repositories (both static and non-static) with interfaces to facilitate testing. To use it you need to do the following:
  • copy the files and L2ST4.ttinclude from the T4Templates directory into your project specific datalayer project
  • ensure that has the same name as the dbml file (so in the example Northwind project has been renamed to to match Northwind.dbml)
  • delete the Custom Tool setting for your dbml file in the properties tab - by default it is MSLinqToSQLGenerator which is how your LinqToSql classes get built, but as we are now doing this through a T4 template it is no longer needed and in fact will cause compile errors as there will be multiple definitions of entities.
  • to generate the classes, open (or whatever it is now called) and save it. You will probably get a dialog box asking if you are sure you want to do this, click Ok. All the classes will then be generated in a file called DataClasses.generated.cs where DataClasses is the same as the name of your dbml file.

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 GenericRepository class as follows:

        protected static TDataContext DataContext
                //We are in a web app, use a request scope
                if (HttpContext.Current != null)
                    TDataContext dataContext = (TDataContext)HttpContext.Current.Items[DataContextKey];
                    if (dataContext == null)
                        dataContext = CreateDataContext();
                        HttpContext.Current.Items.Add(DataContextKey, dataContext);
                    return dataContext;

                    // Creates a Thread Scoped DataContext object that can be reused. 
                    // The DataContext is stored in Thread local storage.
                    // See here
                    //for a discussion of this code.

                    LocalDataStoreSlot threadData = Thread.GetNamedDataSlot(DataContextKey);
                    object dataContext = null;

                    if (threadData != null)
                        dataContext = Thread.GetData(threadData);

                    if (dataContext == null)
                        dataContext = CreateDataContext();
                        if(threadData == null)
                            threadData = Thread.AllocateNamedDataSlot(DataContextKey);

                        Thread.SetData(threadData, dataContext);
                    return (TDataContext) dataContext;


As you can see from the code above it handles being used outside a web app by storing the DataContext in a named data slot on the thread it is running in.

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 CategoryRepository class as well as a ProductRepository class, both of which inherit from GenericRepository, 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 advisable to use the GenericObjectDataSource control which is included in the DataLayer project. An ordinary ObjectDataSource control will create a new object before doing updates and if there are any unbound properties on this object, they will be left with their default values which will then be saved to the database, the GenericObjectDataSource control gets round this by retrieving the original values from the database, before updating them. For more discussions of the details behind this, see

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 repository class to give Client code the ability to call this explicitly.

Sorting and Paging with GridViews

There are a number of methods on the GenericRepository 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:

EnablePaging="true" SelectCountMethod="Count" SortParameterName="sortExpression"


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 for more details


The code includes 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.


As with all file logging this can be pretty detrimental to performance and so should not be left on in production.

To handle the logging to the output window I have used the DebuggerWriter class described here:

Error Handling

Best practice for managing business rules relating to data integrity with Linq to Sql is to include these rules in partial methods in the entity classes and throw exceptions when they are violated (see for more details).
When using this strategy with an ObjectDataSource, you can then catch these exceptions in the OnUpdated or OnInserted events of your ObjectDataSource and present the information to the user.
To do so you set the OnUpdated and/or the OnInserted attribute of your GenericObjectDatasource:

and then handle this event in your code behind:
protected void ProductDataSource_Updated(object sender, ObjectDataSourceStatusEventArgs e)
        if (e.Exception != null)
            //litError is a literal control which I want to use to display the error to the user
            litError.Text = e.Exception.Message;
            //This tells the objectDataSource that I have handled the error and not to throw it
            e.ExceptionHandled = true;

There is an example of this in the Default.aspx page in the test website.


As has been widely 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.

The code

The code consists of 4 projects; GenericDataLayer, which contains the reusable code and 3 demo/test projects


This project contains all of the code you need to use this solution, it can be compiled and referenced in your projects or run as a project within your solutions.
It is made up of:
  • GenericRepository class - the main class which implements the generic CRUD methods as discussed above
  • GenericObjectDataSource - the custom ObjectDataSource to use in your aspx pages as discussed above. This control also uses the classes GenericObjectDataSourceView and ExposedSr
  • FileLogger and DebuggerWriter - classes for logging to text files and System.Debug.Out, see Logging section
  • GenericDataSection - a class to handle configuration data
  • Dynamic - some code to help in the sorting methods


This project is an example of how to use the code with your database (using Microsoft's NorthWind database), it references the GenericRepository project and contains all the project specific database classes that your application needs to work with. These are:
  • Linq to Sql dbml file - In this case pointing to the NorthWind database
  • Partial Entity classes which implements a custom data validation rules (see section on Error Handling) - In this project there is just one class Product which implements one rule
  • Concrete Repository classes which inherit from GenericRepository and implement custom data access code for specific database entities - This project has two, ProductRepository and CategoryRepository

A test web project

This project has one page with a GridView and a FormView which use the data layer via GenericObjectDataSources to list all the products in the Northwind database, allow editing and deletion of those projects, and insert new products via the FormView.

A console app

To test functionality outside the context of a web application

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
  • Create a class library to contain your Linq to Sql classes, partial entities and concrete repositories
  • Run the T4 template as described above to create concrete repository classes
  • Reference the GenericDataLayer project (either compiled or within your solution)
  • Optionally define any new entity specific data access operations in your entity repository classes
  • Make use of the GenericObjectDataSource to get easy inserts, updates, deletes, sorting and paging

Last edited Apr 15, 2009 at 12:57 PM by rbates, version 21