Best Practices for using LINQ in your Data Access Layer

This post will try to expose some of the issues that commonly arise when you choose to use LINQ to SQL or LINQ to Entity in your application. First a little background.

LINQ General

By now most people know what about LINQ and how to use it. For the following discussion, it is important to remember that LINQ (Language Integrated Query) is a set of general purpose language extensions that allows you to query against any IEnumerable<T> based object source. For example the following LINQ expression queries an array of doubles.

double[] monthlyHighTempsInFarenheit = {
            10.0, 25.0, 38.0, 50.0,
            65.0, 73.3, 83.6, 95.6,
            82.1, 72.0, 43.0, 31.0};

var tooCold = from m in monthlyHighTempsInFarenheit
              where m < 40.0
              orderby m
              select m;

foreach (var value in tooCold)
{
    Console.WriteLine(value.ToString());
}

Another feature of LINQ is deferred query evaluation. Very simply, expressions are not evaluated until they are needed. The above LINQ expression for tooCold is not executed until it is needed in the foreach code block.

LINQ to SQL / LINQ to Entity

Now, let’s assume you have a database. The database in our example will be very simple. The following is the only table in our database.

Next we use LINQ to SQL as an ORM tool to generate a C# class that maps to this database. We now have a LINQ to SQL object that looks like this:

Then we could easily write some code that uses the LINQ to SQL data context that returns a list of users from the database.

public class UserModel
{
    public IQueryable<User> GetUsers()
    {
        SiteDbDataContext context = new SiteDbDataContext();

        var users = from p in context.Users
                    orderby p.LastName
                    select p;

        return users;
    }
}

Because we are returning an IQuerable<T> object, we can even implement paging with a simple one liner as follows:

GetUsers().Skip(10).Take(10);

So far this is great. LINQ has provided a lot of features with very little effort. Who wants to write database queries and map the results to objects.

So What Is The BIG Deal?

We must be careful to consider how the layers in our application interact with each other. I would consider LINQ to SQL objects data access layer code. The LINQ to SQL data context is a very powerful wrapper around the database access code.

The big deal comes when these LINQ to SQL objects are used outside of the data access layer. For instance, I have seen ASP.NET MVC examples using LINQ to SQL data objects in the model or controller classes. Typically, this is done to create a quick and feature rich data access layer. However, allowing data access layer code to be used in your presentation layer (MVC) or business logic layer has risks.

The risks are associated with adapting to change or maintaining the code base. For instance, if next year a new .NET ORM tool is introduced that has features that you want to leverage you may be in for a painful upgrade. Each occurrence of LINQ to SQL code that has leaked out of your data access layer, will amplify your upgrade pains.

How Can I Use LINQ to SQL to Produce Maintainable Code?

Above, we have hinted that the source of the maintainability issues is the leaking of LINQ to SQL objects outside of the data access layer. Encapsulation is always your best friend and it will not let us down here.

First, let’s start with using data transfer objects (DTOs) to pass information out of our data access layer instead of using the LINQ to SQL objects. This is important, because the LINQ to SQL objects are generated by the ORM and betray the database implementation via attributes that map each property to a database field. This information has the potential of being used outside the data access layer. For maintainability, we should not allow this information to propagate. So we create a DTO that looks like the following in our case:

public class UserDto
{
    public Guid Id { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }
}

This is a very straight-forward mapping of the database fields to properties in our DTO. The act of mapping the LINQ to SQL objects to our DTO will be done manually in our example. However, you should know that there are libraries that can help with this. See for example AutoMapper.

Next we need to consider how will the rest of our application, interact with our data access layer. A contract is needed that both sides can agree to support. This is an ideal case for an interface. Let’s define a simple interface that includes a few data access operations.

public interface IUserRepository
{
    IQueryable<UserDto> GetUsers();
}

This interface obviously falls well short of providing the full CRUD operations that will undoubtedly be needed by our application. One way to create these interfaces is to insert all the methods that you anticipate using. In my experience, I have never anticipated them all and usually added a few that I never used. So it is better to use the YAGNI approach,  where we only implement the ones we need now.

This contract is the key to isolating our data access layer changes from the rest of our application. If the only information that your application knows about is via this interface, then the concrete implementation of this interface can be changed without effecting the rest of the application. For instance we can have a LINQ to SQL implementation today and an implementation that supports the new .NET ORM next year.

Notice that the interface is still returning an IQueryable<T> object. This allows the query execution to be deferred until needed. The key is that we are using our DTOs. Therefore we can still implement paging using the Skip and Take methods.

It is important that the repository interface and the DTO definitions be available to all layers of the application. At minimum they, should reside in their own namespace (Project.Dal.Core for instance). This code does not belong in your MVC model folder. That folder is for “view models” not data access layer code. You may be able to get away with this for small projects, but it is not a good coding habit. Many times I like to have these packaged in their own assembly (DLL).

Then a concrete LINQ to SQL implementation of our repository interface can be created. For instance the following will provide a basic implementation:

public class UserRepository : IUserRepository
{
    public IQueryable<UserDto> GetUsers()
    {
        SiteDbDataContext context = new SiteDbDataContext();
        context.Log = Console.Out;

        var users = from p in context.Users
                    orderby p.LastName
                    select new UserDto
                       {
                           Id = p.Id,
                           FirstName = p.FirstName,
                           LastName = p.LastName
                       };

        return users;
    }
}

This is nearly the same as above, except we are implementing the interface (our contract) and returning DTOs instead of raw LINQ to SQL objects. Note that exception / error checking has been omitted for clarity. Now when a new .NET ORM becomes available with features you want to leverage, you simply create a new instance of this repository interface.

The concrete implementations of the repository should also have their own namespaces (Project.Dal.LinqToSql for instance). Again, many times will I have these packaged in their own assemblies (DLLs). Especially if I think there will ever be two applications (web and stand alone) that need to access this data. Then I simply add a reference to the DLLs and I am set.

Summary

We now have a clean boundary between our data access layer and the rest of our application. There still exists a need somewhere to create a concrete instance of our repository. Who ever is responsible for creating this instance will need to know about which concrete implementation we want to use. If you decide to hard code the implementation type then that code will need to be modified if you want to switch to a new type. If you want to dynamically be able to remove and plug-in repositories without recompiling then you will want to look into implementing the provider pattern or look into using a dependency injection framework that allows this configuration via XML.

I am interested to hear about how you design in the separation between your data access layer and your application. Please share below in the comments your recommendations.