LINQ to SQL Web Application Best Practices

In my experience building web applications, I’ve always used a n-tier approach. A DAL that gets data from the db and populates the objects, and BLL that gets objects from the DAL and performs any business logic required on them, and the website that gets it’s display data from the BLL.
I’ve recently started learning LINQ, and most of the examples show the queries occurring right from the Web Application code-behinds(it’s possible that I’ve only seen overly simplified examples). In the n-tier architectures, this was always seen as a big no-no.
I’m a bit unsure of how to architect a new Web Application. I’ve been using the Server Explorer and dbml designer in VS2008 to create the dbml and object relationships. It seems a little unclear to me if the dbml would be considered the DAL layer, if the website should call methods within a BLL, which then would do the LINQ queries, etc.
What are some general architecture best practices, or approaches to creating a Web Application solution using LINQ to SQL?


Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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)
            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.

Method 2

The LINQ to SQL is the DB access in the DAL implementation, if you want to separate between DAL and BLL. If you have less complex Web Application (and also never intend to switch DB backends), then you can get away without explicit DAL/BLL and do everything in the code behind. LINQ to SQL works great for readonly operations, but feels like a bit more work to implement write operations.

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x