LINQ to SQL Web Application Best Practices

I’m afraid you’ve indeed seen overly simplified examples. LINQ to SQL (System.Data.Linq) is your DAL layer. The classes L2S generates is your domain (but not to confuse with Domain-Driven Design). On top of that you can still write your Business Layer.

I always try to prevent leaking the LINQ to SQL DataContext into the presentation layer (your web app). So it shouldn’t be able to create or commit a DataContext. Neither should you return IQueryable<T> objects to the presentation layer. IMO the business layer should have full control over the lifetime of the DataContext (unit of work) and the shape of the SQL queries.

However, there are several flavors. Some people tent to relax these constraints. Others even go much much further. It depends on your own taste and the size of the application. The bigger the application, the more it is justified to add layers of abstraction.

When disallowing IQueryables and other data related stuff from leaving the business layer, you’ll end up having some interesting challenges. For instance, the presentation layer must instruct the business layer how to sort results. While you could let the presentation layer sort the results itself, this would mean that you would have to get all data from the database and page at the presentation layer, what would lead to a very badly performing system. There are several solutions to this problem. In all cases you will need to inform the business layer how to sort the results for you. Solutions can be found here at SO when you search for LINQ dynamic sort. I’ve written such a solution myself, here.

Another challenge that disallowing IQueryables from leaving your BL will bring is that also domain objects can often not leave your BL. Most of your LINQ to SQL domain objects will contain lazy loaded properties (for instance, collections to other domain objects). However, when the DataContext is in control of the Business Layer, it will be disposed, before you return results to the presentation layer. When the presentation than accesses a lazy loaded property, an exception will occur, because the DataContext has already been disposed. When you dispose the DataContext in your business layer, this behavior is of course ‘by design’. Allowing the presentation layer to get lazy loaded properties means the BL loses control over the queries that are sent to the database, thus losing control over performance.

To resolve this issue, you should return Data Transfer Objects (DTO) from the BL to the presentation layer. A DTO will contain just data and no internal DataContext, and no lazy loaded properties. A DTO can be specially formatted for the actual request at hand. DTOs of course lead to coding overhead themselves, so the size of your system and the performance needs must justify it. To make it easier for myself, I tend to put static projection methods on the a DTO. While this doesn’t conform to the separation of concerns principle, I foudn it to be a very practical solution. Look for instance at this CustomerDTO:

public class CustomerDTO
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    // City is flatterned from Address.City.
    public string City { get; set; }

    internal static IQueryable<CustomerDTO> AsDTO(IQueryable<Customer> customers)
    {
        return
            from customer in customers
            select new CustomerDTO()
            {
                CustomerId = customer.Id, 
                Name = customer.Name,
                City = customer.Address.City
            };
    }
}

This DTO defines an internal AsDTO method, which is able to convert a collection of Customer domain objects to a collection of CustomerDTO DTOs. This makes conversion of domain objects to DTOs much easier. Look for instance at this BL method:

public static CustomerDTO[] GetCustomersByCountry(string country)
{
    using (var db = ContextFactory.CreateContext())
    {
        IQueryable<Customer> customers =
            (from customer in db.Customers
            where customer.Address.Country == country
            orderby customer.Name, customer.Id);

        return CustomerDTO.AsDTO(customers).ToArray();
    }
}

The nice thing about this approach is that when you look at the SQL query, you’ll see that only the customer Id, Name and the City of the Address table will be retrieved from the database. This is because the AsDTO method translates one IQueryable to another, allowing LINQ to SQL to perform the total operation in the database.

I hope this gives some ideas of what you can do. Of course, this is my view on the subject and the things I’ve found practical in my situations.