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

There is no doubt that Linq to SQL will have an enormous impact on the way we write data access layers. I wouldn’t be surprised to find out that the impact is so profound, that we might even have to reconsider the very nature of a data access layer. In fact, what is a data access layer (DAL) anyway?

Let’s start by trying to create a (working) definition of a DAL. Wikipedia is usually a good place to start, but you’ll find that the Wikipedia article on DAL’s doesn’t exactly contain all the answers. So let’ give it a try ourselves.

A DAL is a layer. That means its part of a layered architecture. Other layers use the DAL to do data access. Indeed, the DAL is the layer accessing the data (and in the context of Linq to SQL, that’s relational data), and no other layers access the data directly.

That’s a good start, but what is layer? Is that a special kind of component? Not in my mind it isn’t. To me, a layer can contain multiple components, and that applies to a DAL as well. Let’s say I have a simple banking system. It contains functionality on clients, their accounts, and the operations (such as money transfers) they do on those accounts. That might result in a vertical partitioning of the application in three modules, “Clients”, “Accounts” and “Operations”. Each of those would be layered, and you’d find components at the intersections of the vertical modules and the horizontal layers. So you’d have “Clients DAL”, “Accounts DAL” and “Operations DAL” components. Obviously, these components are related, they have dependencies between them. The Operations DAL depends upon the Accounts DAL (and possibly the Clients DAL as well), and the accounts DAL depends on the Clients DAL.

In .NET, components like these correspond to assemblies. So our DAL would consist of several assemblies, with (non-circular) references (dependencies) to each other. Which part of the functionality do we put where?

The Clients DAL doesn’t know about accounts, that’s the responsibility of the Accounts DAL. That one knows about accounts, and about clients as well. After all, accounts are owned by clients. So that means the function to retrieve the list of accounts belonging to a given client sits in the Accounts DAL, not in the Clients DAL. Since this function has a client as a parameter (or at least a client id), the Accounts DAL may indeed need a reference to the Clients DAL. Each object returned by this function has a reference to the client owning the account, or at least the id of that client.

But wait, what’s the impact of Linq to SQL on what I said so far? If I have a database with Clients and Accounts tables (amongst many others) with a foreign key between them, the typical Client and Account entity classes will have a relationship between them as well. The Account class will have a delay-loaded Client property, and the Client class will have an Accounts property. That’s a mutual dependency, so these two classes need to sit in the same assembly. But where does that lead us to? Typically, all tables in a database are somehow related to each other, i.e. there are no disconnected islands of tables with relations between them, but no relations to other islands in the database. But that leads us to just one DAL per database! Is that what we want?

Well, SQLMetal, the Linq to SQL tool that generates an entity model based on a database schema, definitely pushes us in that direction. Typically, it generates one source code file containing one DataContext and all the entities in your data model. But that’s just the entities though, that code doesn’t do any data access! To actually access the data, you need to write queries! And those queries are the responsibility of our DAL components.

In our example above, we had three DAL components, and all three of them would access the same DataContext. That implies that the DataContext should exist in an assembly of its own, an assembly underlying all DAL assemblies. But that’s an additional layer, isn’t it?

Well maybe it is. Maybe we need to split our traditional Data Access Layer into two distinct sublayers. For lack of better terms, I’ll call them the “Entity Layer” and the “Entity Access Layer”.

The Entity Layer has just one assembly in it, so we might just as well refer to that assembly as the Entity Layer as well. The entire assembly is compiled from just one code file (and some housekeeping stuff perhaps, like an AssemblyInfo.cs file), generated by SQLMetal.

The Entity Access Layer (EAL) has several assemblies (three in our example), all using the Entity Layer. The EAL assemblies contain the actual queries.

Next time, we’ll look at the interface between the EAL assemblies and the business layer: what parameters are used, what results are returned? Do we expose the Entity Layer types? Do we expose query expressions or query results only?

That’s enough food for thought right now, and comments are more than welcome.


8 Responses to “Creating a Data Access Layer with Linq to SQL, part 1”

  1. 1 cumpsd August 22, 2007 at 10:08 pm

    I’m favored into thinking that linq *is* the DAL, but in the business I’m currently working on, there’s need for higher flexibility.

    What do I mean with that? I’ll be going for an architecture (haven’t finished it yet) which uses LINQ for all it’s data access, but all the layers above that shouldn’t know that linq is running under the hood.

    The reason for this, it’s very possible there comes a point during early stages of development where votes are against linq and I have to take it out again, replacing it by something else.

    At that point, I really don’t want to rewrite anything above the data layer.

    Does Linq bind us too much into choosing 1 solution for our data?

  2. 2 Adam Schaible January 8, 2008 at 3:20 am


    I’m faced with a similar situation – here’s my solution.

    Interface the DAO’s, and make your implementation use Linq – create a static service layer that wraps all of your DAO’s, and it’s kind of like the “DAL” if you will. All of your code will call this static service layer, that references the linq implementations by their interface type.

  3. 3 progrmr January 16, 2008 at 4:27 pm

    I agree with the blog. There are certainly many ways to arrange things but I’m doing pretty much the exact same thing. My LinqToSql schema represents the entire database and seperate entity classes utilize that single data context.

    Good blog – onto part 2.

  4. 4 Stiiifff January 28, 2008 at 10:54 am

    Hi Kris,

    You might have a look about what is named by Ayende as ‘Partial Domain Models’:
    In that case, the ORM is NHibernate … but the problem remains the same.

    IMHO, the fact that Linq to SQL force you to have all your domain model expressed in one file per database doesn’t mean that you have to go that way, especially when you would have liked to split your domain model in several sub-models. If you put all your entities in one big domain model, you end up trying to re-split the whole model in a higher layer.

    Another interesting option is to use the Repository pattern, defined by a generic interface exposing Queryable collection so that you can use Linq for all your queries. The actual implementation of the Repository being Linq to SQL, or any other ORM … it is less important as the Repository is actually abstracting your service layer, using Linq queries, from the actual ‘Data Layer’.

    Check this article:


  5. 5 mikew909 May 15, 2008 at 5:30 am

    Hiya – great article you got me rethinking about DALs now, I’ve been dabbling in ORM applications like EntitySpaces and LLBLGen but I’m going to investigate LINQ some more

    cheers 🙂

  1. 1 fluxcapacity » Blog Archive » Data Access Layer: To LINQ or not to LINQ Trackback on July 22, 2007 at 12:11 am
  2. 2 All you can LINQ » Sending the LINQ to SQL log to the debugger output window Trackback on August 1, 2007 at 12:21 am
  3. 3 » links for 2008-04-03 Trackback on April 3, 2008 at 10:36 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: