The missing LINQ (Part 1)

I had a plan. The plan was to write an ASP.NET MVC application in my spare time and to blog about it as I went. I would use the opportunity to learn about some crazy new frameworks and just generally have a good time.

Step 1 in my plan on the road to MVC glory was M, the Model. I wanted to give LINQ to SQL a go so I figured I'd spend a couple of hours spinning up a LINQ to SQL Data Access Layer and then move on from there. I ran into a snag or two. I've been meaning to blog about it for weeks now but I just hadn't had the time to put together some code for it.

Firstly, some background on the application I'm writing. The application, currently code-named UGLi is for managing a library of books. It will deal primarily with Books, Members and Loans.

I won't into too much detail about how LINQ to SQL works but here is a brief overview. When you point the LINQ to SQL designer at a database it creates a LINQ to SQL DataContext class that represents all interactions with that database. As you drag tables onto the designer surface two things happen. Firstly a class is generated which represents rows of data for that table and secondly the DataContext gets a Table object that represents the actual database table.

For Example: If you drag a Book table onto the designer surface you'll get a Book class with all of the fields for an individual book and the DataContext will get a property of type Table<Book> called Books. You might use this in a method like this:

public IQueryable GetBooksForPublisher(Publisher publisher)


  using (var dataContext = new LibraryDataContext())


    return from b in dataContext.Books

           where b.Publisher = publisher

           select b;



You can see that dataContext hides the SQL part from you and all you see is an IQueryable<Book> view of the database. If you want to create a new Book you just need to instantiate a Book object and call dataContext.Books.InsertOnSubmit(book), which will add the Book to the local Books collection and then call dataContext.SubmitChanges(), to actually push the new book to the database. Similarly, in order to update a book you first need to get a Book object from the dataContext.Books collection, update it's members directly and then call SubmitChanges() again on the dataContext. It can be a lot more complicated than this but it's enough for what I want to talk about.

The best part about all of this is that LINQ statements return expressions explaining what you you wanted to do and not the actual data returned by the query (until you actually start iterating over it anyway). What this means for LINQ to SQL is that if you call the GetBooksForPublisher method shown above and never use the results for anything, the database is never actually called.

Let me repeat that because it's important.  Talking to LINQ to SQL produces a code-artifact which knows HOW to access the database but does not actually access the database until absolutely required. Why is this good?

This is a good thing because LINQ is available everywhere (in .NET 3.5 anyway) and we can layer the creation of LINQ artifacts. Having this in place lets us build up queries to the database in layers which helps us to achieve the logical separation of layers within the application that architects traditionally recommend. These are the User Interface (UI), Business Logic (BL) and Data Access (DA) layers.

To show what I am talking about consider this scenario: The user has selected a particular Publisher from a drop down list and would like to see the list of books published by that publisher. They have also selected that they would like to see books ordered by the date they were published. To keep resource use to a minimum (and speed up the loading time on the page) the system shows results in pages of 20 and the User has opted to see page 3.

In one system I have worked on the UI code would marshal all of those parameters into a call to a business layer component. The BL components would pretty much just pass the request out to a Data Access Component. The DAL Component would call a stored procedure with, you guessed it, pretty much the same parameters. The Stored Procedure returns a table of data which might get passed passed back as a DataReader or DataTable from the DAL component. That information might be filtrated back to Business Objects by the BLL components and finally the UI layer will bind it to a grid (or do something else with it).

This looks idiotic but unfortunately it frequently happens. The main issue here is that eventually we want to run the most efficient query we can against the database and minimize the amount of information we want to get back. It just so happens that the parameters that allow us to do that the most are the paginations ones.

So we end up with a Stored Procedure (which is below the Data Access Layer level) that knows about Pagination (for efficiency reasons). But where does Pagination actually belong? Isn't that purely a user interface decision? If we were writing a Command-Line Interface wouldn't the pagination rules be very different? In fact, what does Pagination have to do with Book and Publisher Data?

Compare this to the LINQ to SQL scenario. There is a DAL component on the DataContext called Books. All it knows how to do is provide access to all of the book data in the database. This property is accessed by the method provided above which filters the books being returned by the DataContext. Above that a UI component will provide pagination against the IQueryable like this:

public IQueryable<T> Paginated<T>(IQueryable<T> itemsToPage, int pageSize, int pageNumber)


  return itemsToPage.Skip(pageSize * (pageNumber - 1)).Take(pageSize);


You can imagine how you might add in ordering and clean this up with some nice syntactic sugar to that you might get something like GetBooksForPublisher( oreilly ).OrderedBy(b=>b.PublicationDate).InPagesOf(20).GetPage(3)

In the LINQ to SQL version we still go down all of the layers from UI to BLL to DAL to Database but what happens is that instead of carrying information down with us in the form of parameters, we pull information about how to access the data back up and refine the final SQL query using LINQ statements. Although the SQL will be generated and executed right up at the top when the data is bound (or iterated over), the only part of the whole application that knows about SQL Server is right down the bottom and it's surface area is quite small so it could be easily replaced. On top of this the rest of the application is designed to use IQueryable<Book> collections so if we want to drop all of the book data into an XML file we could do it with a minimal amount of fuss and use LINQ to XML as our Data-Persistence mechanism.

Whilst these benefits are not unique to LINQ to SQL, I think that the integration with LINQ makes the solution nice and elegant. In the next instalment I want to look at how to manage DataContexts and use that layering effect to isolate the database from the rest of the code as much as possible. After that we might actually get to the MVC part.

Posted by: Mike Minutillo
Last revised: 27 May, 2011 02:42 PM History


17 Mar, 2008 04:49 AM @ version 0

Some free LINQ to SQL (and .NET 3.5) training for developers here:

* New Features in C# 3.0
* New Features in VB 9.0

No new comments are allowed on this post.