Creating a Data Access Layer with Linq to SQL, part 2

Last time, we looked at how Linq To SQL might impact how we think about what a Data Access Layer (DAL) is, based on the dependencies between assemblies. This time, we’ll take a different approach: let’s look at typical Linq to SQL code, and try to decide where to put it. I’ll use a code sample from the “DLinq Overview for CSharp Developers” document included in the Linq May CTP (in C# 3.0, but the same applies to VB9).

A simple start

Let’s take a look at the following code:

Northwind db = new Northwind(@"c:\\northwind\\northwnd.mdf");

var q = from c in db.Customers
        where c.City == "London"
        select c;

foreach (var cust in q)
  Console.WriteLine("id = {0}, City = {1}", cust.CustomerID, cust.City);

It should be clear that the first line belongs in the DAL. The DataContext encapsulates a database connection, and knows about the physical location of the database. That is not something that higher layers should know about.

Let’s say the actual query definition belongs in the DAL too, but clearly, the foreach loop sits in some higher layer. That means the two first statements need to be encapsulated in some function in the DAL, for example as follows (sticking with the “Entity Access Layer” terminology introduced before):

public class CustomersEal
  private Northwind db = new Northwind(@"c:\\northwind\\northwnd.mdf");

  public IQueryable<Customer> GetCustomersByCity(string city)
    return from c in db.Customers
           where c.City == city
           select c;

The business layer then contains the following code:

CustomersEal customersEal = new CustomersEal();

foreach (var cust in customersEal.GetCustomersByCity("London"))
  Console.WriteLine("id = {0}, City = {1}", cust.CustomerID, cust.City);

Looks good, doesn’t it? All the business layer knows about the database, is that it can return Customer objects.


But wait, what if I write the following in my business layer:

CustomersEal customersEal = new CustomersEal();

var q = from c in customersEal.GetCustomersByCity("London")
        orderby c.ContactNamer
        select new { c.CustomerID, c.City }; 

foreach (var cust in q)
  Console.WriteLine("id = {0}, City = {1}", cust.CustomerID, cust.City);

This code highlights a few interesting facts.

First of all, it wasn’t the DAL that executed the query, at least not in the traditional sense of the word. The DAL (CustomersEal to be precise) merely supplied the definition for the query. The query got executed when the foreach statement started looping over the result! In a traditional DAL, a call to a method like GetCustomersByCity would have executed the query, but not with Linq, at least not if we implement our code like this.

Secondly, the business layer can refine the query definition. This definitely has some advantages, but I realize some might argue that this is really bad. Note though, that the business layer cannot redefine the query, or execute just any query it wants. Or can it? You need the DataContext to start the process, and only the DAL has access to that, right? In fact, the Entity Layer generated by SQLMetal is referenced by the business layer too, it needs it to get to the definitions of the entities!

Thirdly, it is absolutely not clear where a developer should draw the line between what’s business logic, and what belongs in the DAL. I could have moved the orderby into the DAL (especially if I always want customers to be ordered by their ContactName). But likewise, I could have moved the where clause to the business layer! How do I decide what to do?

I hate it when developers have to make choices like that during routine development. Choosing takes time, and that’s not likely to improve productivity. But much worse is the fact that different developers will make different choices. Even a single developer may make different choices from one day to the next. That leads to inconsistencies in the code. Developers will spend more time trying to understand the code they’re reading, because it doesn’t always follow the same pattern. That’s bad for productivity. In the worst case scenario, developers start rewriting each other’s code, just so it matches their choice of the day. That kills productivity. (Wasn’t Linq all about improving productivity?)

The solution?

We need a clear and simple criterion to decide which code goes where.

Note that the absolute minimum for a DAL is the following:

public class CustomersEal
  private Northwind db = new Northwind(@"c:\\northwind\\northwnd.mdf");

  public IQueryable<Customer> GetCustomers()
    return db.Customers;

It’s a bit silly of course, if that’s all this layer does, we might just as well skip it (the connection string should be externalized in a configuration file anyway, and a default constructor that reads the connection string from the config file should be added to the Northwind DataContext in a partial class). Silly or not, it is a “lower bound” to an EAL as we have defined it here. I believe there’s an “upper bound” too: I think the DAL shouldn’t do projections (well, it definitely should not expose anonymous types). But that still leaves us with a very broad range. How to make a choice?

I’m inclined to say that the only way to make a clear and simple choice once and for all, it to go with the minimalist approach. And indeed, that means we don’t need/write/use an Entity Access Layer. The business logic directly accesses the one assembly generated by SQLMetal.

How’s that for a DAL?

22 Responses to “Creating a Data Access Layer with Linq to SQL, part 2”

  1. 1 Kurt Quirijnen December 6, 2006 at 11:59 am

    You are touching on terribly interesting stuff here. LINQ looks indeed very promising, but unfortunately my
    hands-on experience is infinitely small up to now.
    It is like with many other new technologies/tools/blablabla: the challenge is to find the right patterns to build clean code and a high quality system. I understand that you focussed mainly on maintainability of the code and more especially the data access part of that (still somewhat 80% of most enterprise business apps?).
    Are there other LINQ discussion points concerning for example performance, less-buggy/more-robust code, developer productivity…?
    I would be happy to read more on your experience with implementations with LINQ.

  2. 2 Darren Fieldhouse July 18, 2007 at 10:35 am

    Since hearing about LINQ I’ve be struggling to imagine how it would fit into a traditional 3-tiered architecture. This is the first article I have seen addressing that question – well done!

    I must say, I’m not sure about the DAL being replaced by the output of SQLMetal. The reason we have been building DALs all these years is to isolate the BLL from all the messy data access code. What you are suggesting would push that code back into the BLL, in the form of LINQ queries rather than SQL strings, but either way, is that really what we want?

    I don’t have any answers for this myself yet, but I’m hoping to see a few lively discussions on blogs like this as people start to use the technology more and more.

  3. 3 Josh July 18, 2007 at 2:23 pm

    If your going to encapsulate queries why don’t you compile the query?

  4. 4 Eric Wild July 18, 2007 at 2:37 pm

    Wow! This is exactly what I have been wondering about with LINQ. Thanks for putting this into words. I have not been able to really sit down and play with LINQ, but I have been reading the tutorials, and I have been asking to myself; “What do we do with this”? “Is it a DAL, is it a BAL, is it both?” “Is this a good thing?”.
    For one, I have seen the examples where you can create a querry, and then have the query “new up” a brand new object that contains a subset of our data from the DB. One of the purposes of a BAL, and a DAL, I thought was to have an interface that would provide the UI an object that represented the buisness’s representation of a customer. Doesn’t it also seem weird, that in the wild wild west days, SQL used to be slapped on the side of an ASP page, and we called this bad. Why? for one, any develeoper can create there on reprsentation of a customer. IE SELECT * FROM Costomer INNER JOIN Address AS PrimaryAddress WHERE PrimaryAddress.AddressType = “HOME” Then the next querry makes the PrimaryAddress.AddressType = “BUSINESS”.
    So what is the difference now? You have shown above that the business layer doesn’t have to live by any rules that the DAL has established, and can create queries on the fly and return objects that it wants. I have an associate that has an app that still just throws the raw SQL right there on the UI. Leaving the SQL injection debate alone, I argue the Stored Procs are the Database’s interface to the DAL. I know, that No select/insert/update or delete happens unless it does throught the SP, and that needs to be processed by the DAL. I seems weird, that when you take a look at your database, and you see one of your fields is being updated with an incorrect value that may be creating a bug, the first thing to do is start looking at your UI, and see who has been creating so update query on the fly that is messing up the database. Yikes? As you can see I am torn as to what to do with this, and how to prevent the wild wild west days.

  5. 6 John Walker July 19, 2007 at 8:05 am

    Fantastic post. The code you show directly under the Problems heading is eye-opening and clearly shows the conundrum. I’m not sure the dba’s will be happy. Will be interested to see the different approaches for this. Thanks again for a great post.

  6. 7 Pablo Montilla July 25, 2007 at 2:55 pm

    I think that with Linq to SQL there’s still a need for an intermediate layer to shape and control the way the app interacts with the datasource. A better tech to do what you suggest is the next versión of ADO.NET with their entity model, eSQL, etc.

    There you have a powerful enough declarative way to shape and model the datasource so you have a workable and business oriented model to use with your app, that persists to a correctly normalized and performant db model (both of wich could wildly differ).

    That’ll be fun to use!


  7. 8 Andrew August 21, 2007 at 1:17 am

    Hey Kris,

    You may want to take a look at the Repository pattern. For a LINQ to SQL example check out:



  8. 9 Hasmukh August 24, 2007 at 8:54 am

    I am trying to implement lINQ for Update/Insert operations.
    I have written my own classed with Linq attributes.
    I can very much retrive the data from Database using DataContext.

    DataContext dt = new DataContext(OpenDBConnection());
    foreach (ServiceConfig item in s)

    // For Update
    foreach (ServiceConfig item in s)

    I got problem with Read Only
    After Updating Table schema with Primary Key,

    I got another error:
    “Cannot insert explicit value for identity column in table ‘SXASRServiceConfig’ when IDENTITY_INSERT is set to OFF”

    I dont know how to update the rows in the Database table.
    I had to set
    “UpdateCheck = UpdateCheck.Never” on field attribute.

    Does is affect anywhere else?


  9. 10 Krish August 27, 2007 at 2:35 pm

    My personal thoughts are that having a distinct business logic and data access layer is good. In fact, you do not want the business logic layer to know the mechanics of how the data access layer constructs and/or executes queries that result in a hit to the database. That said, with LINQ to SQL/LINQ to Entities, your business logic might just be a very thin wrapper around your DAL.

    That is, the data access layer should be returning just arrays of entities (T[] or IEnumerable). It should [not] be returning IQueryable. Returning IQueryable gives you more rope to hang yourself with because (i) the caller (business logic layer) can redefine the query that ultimately gets sent to the database (ii) the caller (business logic layer) now executes the query because of deferred query execution (iii) the distinction between the caller (business logic layer) and the DAL blurs.

    By having the DAL return an array of entities(T[] or IEnumerable), you ensure that (i) the DAL is solely responsible for the construction and execution of the query which ultimately results in a database hit and (ii) the business logic layer can only work with LINQ to Objects and/or LINQ to XML without triggering “n” unintentional SQL queries just because it decided it could slap on a few “groupby, orderby and select” clauses to an IQueryable returned by the DAL.



  10. 11 Mohammad Azam October 5, 2007 at 7:40 pm


    You left out the main thing. How will you handle the Domain object in your UI layer when the domain objects are contained inside the DAL layer and UI does not reference the DAL layer. Let’s say you have 3 projects. One is the DAL layer which simply contains the Database.dbml file. Second is the Manager project which contains the code like GetUsers(). And third is the UI layer which has a reference to the Manager project. The Manager project has a reference to the DAL project. Now, The entities like “Customer”, “User” are automatically generated using LINQ to SQL and are in the DAL project. UI does not know about them so how can UI access the entities like Customer, User etc.

  11. 12 Narek December 14, 2007 at 9:03 pm

    This is what I was looking for. After 5 days of working with LINQ, I decided to move back to net tiers.

  12. 13 Darick Carpenter January 9, 2008 at 11:14 pm

    I think one of the benefits of having a tiered approach is the ability to change datasources easily. It seems that using LINQ you wouldn’t be able to do that. If you are passing an IQueryable object to business and ui layers, then if you need to change your data source then you have to make changes throughout the business and ui layers too. Though there may be workarounds for this they would cumbersome. Ideally you should be able to swap dals that come from different sources without the need to re-write any of the bl or ui layers (sources such as Oracle, mySql, Sql Server, or xml). It seems that this would just be difficult to do with LINQ. It would nice (and maybe you can do this) to be able to use LINQ and then easily populate a dataset and then pass the dataset or datatables between your layers. Then if the need arises to change sources you can keep the dataset and just use dataadapters to populate them from the new source. Then your other layers would not have any knowledge of how or where the data comes from, which is how it should be.

    I do think that it will be very handy to be able to query against objects though.

  13. 14 Christian Staffe January 16, 2008 at 11:34 am

    I fully agree that the DAL should return array of entities (T[] or List for instance and not IQueryable. Now a further problem related to SOA is which kind of object you return.

    Let’s take an example, you have a customer, account and reporting service for instance and a single database. You can design try to assign each table to a specific service, having one DAL per service but each service is able to use the DAL of the other services. That’s not “pure” SOA but it’s reasonable to do this in my opinion.

    But this also implies that your business entities (the ones returned by the DAL) also need to be shared between services as they are interconnected (a client has a list of accounts for instance). But this starts to defeat the purpose of services. Depending on the service, I would like to design my entities with only the data related to the domain covered by the service but then it leads to a lot of duplicated code describing the entities.

    Does someone have some insight/experience with this problem ?

  14. 15 progrmr January 16, 2008 at 4:52 pm

    I agree with the fullest seperation between the BLL and the DAL in an application – so returning T[] or IEnumerable is the way to go.

    I also see the point of one poster above that the presentation layer cannot/should not communicate with the DAL – meaning that the BLL is going to have to handle results returned to it and return to the presentation a native format that doesn’t worry about the underlying entity classes.

  15. 16 Stiiifff January 28, 2008 at 11:25 am

    Well, actually, Querying ‘is’ a business concern:

    Why would we write static-named methods for all possible queries we have to make on our Domain Model ?
    Linq to SQL is bringing DDD to the mass … and it is indeed a problem if you still think in terms of ‘layers’ instead of ‘services’.

    In DDD, your Domain Model contains data structure AND business logic. The Repositories abstract your domain model from the ‘boiler-plate data access code’. Concerning the Service Layer, you don’t actually need one. If you have one, it can be very thin, just acting as a bridge/facade.

    See the ‘BackgroundMotion’ website architecture as a living example of those concepts. Kudos to Andrew ;o)

  16. 17 Daniel GAVARIN February 1, 2008 at 1:05 pm

    Sorry for my english.

    I think that it’s to hard to build a solid architecture with Linq To SQL ( I dont’t talk about Linq but only Linq To SQL ).

    But you can see a solution in writing this :

    DAL = CRUD on DLINQ with Expression in output IEnumerable, and symple type, you can use class of SQLMetal (I you want to Optimise Query maybe you need to change UpdateCheck, because i don’t find any solution to change this value in runtime)

    BIZ = use class of DAL, Linq to Object and prepare Expression for DAL

    UI : Use BIZ, linq to Object

    It’s ok, but its not a solid architecture because you loose Entity concept, and when you try to work in SOA architecture you will to find with Entity to DataTable and true Entity !!!???? It’s not good

    A good idea, it’s to use a best linq provider to access DataBase because DLinq is nearest SQLServer, and that force to have a bottom up vision.

  17. 18 noiser November 19, 2008 at 5:50 pm

    has any consensus been reached yet?

    i’m struggling with the same architectural dilemma and it seems like where ever i go to find help on it all i find is other people with the same problem but only with so-so workarounds and they all seem to differ to.

    so far i’m agreeing with what progrmr said, that you simple have to pass IEnumerables up to the BAL and basically remap the result to a native format for the UI. i’m thinking im simply gonna have to create business entities and populate them from the result. seems a bit redundant though!

  18. 19 gsdwriter May 9, 2009 at 2:04 am

    How you use Linq to SQL depends on what problem you need to solve.

    Let’s say you are building an app that needs to be written quickly, will allow users to enter criteria for reports that it will then display, that it will run against SQL Server and the possibility of ever changing to another database (like Oracle, etc.) is remote. Do you really need a formal DAL? Can you get away with runing SQLMetal and having some Linq to SQL queries in your UI? Probably.

    What if you are building an app that may get really big and end up being split across multiple physical machines? Clearly you need a formal DAL and there is no way you are going to be passing IQueryable’s around.

    Linq to SQL is a new tool that we can now use to solve the problems we hit, but it is not a “one size fits all” solution.

  19. 20 RK May 21, 2010 at 4:37 am

    I agree with your version of DAL, now how are we going to design the business layer?

  1. 1 All you can LINQ » Sending the LINQ to SQL log to the debugger output window Trackback on August 1, 2007 at 1:53 am
  2. 2 LINQ and multi-tier architecture design Trackback on July 27, 2008 at 12:56 am

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

RSS My U2U Blog

  • An error has occurred; the feed is probably down. Try again later.

RSS Top blogs linking to here

  • An error has occurred; the feed is probably down. Try again later.

%d bloggers like this: