A 3-Tier Architecture with LINQ TO SQL

Recently, I posted a 5-part article about developing a 3-tier architecture using ADO.NET. In this post, I am going to mention how to develop such an architecture using Linq to Sql. Since the last article was too long, I am going to make this one short. Therefore, the architecture is not changed and still includes four layers: Common, Data Access, Business and Presentation. Moreover, since the business and presentation layers do not change that much, I am going to focus on the development of Common and DAL layers.

The nature of Linq to Sql seems to be designed for 2-tier programs, especially when we use Visual Studio to visually create entities and DataContexts. However, we can separate the definition of entities and the data access layer following the methods mentioned below.

First of all, inside Visual Studio 2008, create a new blank solution and then add a Class Library project to it, named Linq3TierCommon. Right-click on Linq3TierCommon, choose “Add new item” and then add a new “Linq to Sql classes” item to your project. Then design your entities visually or even drag-drop any table, view or stored procedure you like on to this .dbml surface (if the surface is not seeing, in Solution Explorer, righ-click on the .dbml file and choose “View Designer” ). The following image is an example:

Actually, you do not have to use .DBML (Linq to Sql classes item) and you can define your required entities by writing code or using SqlMetal tool. However, it is needless to say that using Visual Studio makes everything much easier.

Anyway, up to now we have created entities and the relationships, but the problem is that an automatically generated DataContext will let you access to database thorough the common layer. To avoid this, set the “Access” property of your DataContext class to “Internal”:

After completing the common layer, add a new class library to the solution and name it Linq3TierDAL. To shorten this post I have put the base DAL class and the concrete DAL classes in one class library, but for a real application, I strongly recommend to put them in different assemblies. However, add a class to this library and name it Linq3TierDALBase.cs

This class is the base DAL class and contains common DAL functionalities. First, we define a protecred property of type DataContext for CRUD actions:

private DataContext _innerDataContext = null;
protected DataContext innerDataContext
string ConnectionString = ConfigurationManager.ConnectionStrings[“main”].ToString();
if (_innerDataContext == null)
_innerDataContext = new DataContext(ConnectionString);
return _innerDataContext;

Then, we will add a generic method named GetTables in order to return a Table<T> collection. This collection will be used by concrete data access classes to perform Linq query on them.

protected Table<T> GetTables<T>() where T:class
return innerDataContext.GetTable<T>();

You may also add a method to call the ExecuteCommand method of DataContext class in order to execute stored procedures.

we also add a method named Save to save all entity changes:

public virtual void Save()

Now we will add a new class, named UserDAL.cs which is inherited from Linq3TierDALBase.cs. This class may have several methods like, FetchAll, FetchByPK, Save and so on:

Notice that these methods are not constant and you may define as many as methods you like.

Afterwards, add a new class library project to this solution and call it Linq3TierBusiness, containing a class named UserBiz.cs . This class may have several business methods, like FetchAll, Save and so on. UserBiz is responsible for validations, concurrency control and so on. The following code is a very simple example of what a business class can be:

public class USERBiz
private USERDAL innerDAL = new USERDAL();
public IQueryable<USER> FetchAll()
return innerDAL.FetchAll();
public void Save()

// perform validations…

PLEASE NOTICE that you must call .SAVE method of a DAL object from which you have retrieved your entities. Otherwise, LINQ to Sql manager assumes that you have made no change and it will save nothing. The good thing about linq is that you may ever use Linq To Objects inside your business classes, withough interfering the data access layer.

For the presentation layer, I have written a small Console application, which retrieves all users, find a user whose login name is “admin” and changes it’s password:

class Program
static void Main(string[] args)
USERBiz biz = new USERBiz();
IQueryable<USER> Users = biz.FetchAll();

USER admin = Users.Single(x => x.LOGINNAME == “admin”);

if (admin.PASSWORD != “newpass”)
admin.PASSWORD = “newpass”;

foreach (USER item in Users)
Console.WriteLine(“Login Name: {0} , Password={1}”, item.LOGINNAME, item.PASSWORD);


That’s it. Please notice that this so-called architecture needs to be improved a lot and what is mentioned here is just to give an idea.

You can download a full source code from here. The source code contains a SQL 2005 database backup, used to write the sample program of this post. you may use it or use your own database.

P.S. The password of the attached sample file is aspguy.wordpress.com


20 thoughts on “A 3-Tier Architecture with LINQ TO SQL

  1. Hi,
    I like the idea for making the Three tier arquitecture with LINQ to SQL.
    I will put it in practice to see how it goes.
    Thanks for this tutorial and have a nice day.

  2. i hardly able to read and your choice of color is very strange.

    I changed the theme to a black & white one. check if you are able to read it.

  3. Hello,

    Your article is two good. But I have few confusions, please guide me.
    I have a solution having following projects.
    1) Presentation Layer (Win form application)
    2) Business Layer (Class library)
    3) RealDAL (Class library having your DAL and DALBase class)
    4) DALCommon (Class library having dbml file)

    I have reference projects in following way.
    – (2) => (1)
    – (3) => (2)
    – (4) => (3)

    Following is the code of one class of Business Layer.

    namespace BAL
    public class Person
    private RealDAL.DAL rd = new DAL();

    public List FetchAll()
    return rd.FetchAll().ToList();

    public void Save()


    It gives me following error wihile compilation.

    “Error 1 Cannot implicitly convert type ‘System.Collections.Generic.List’ to ‘System.Collections.Generic.List’ H:\Demo Projects\LinqToSqlNTier\BAL\Person.cs 16 20 BAL”

    If I change method as following:

    public List FetchAll()
    return rd.FetchAll().ToList();

    Then all works well in BAL but in presentation it ask to add reference of DALCommon project. By rule, I can not add reference of data access project to presentation project as all the things should come via business layer only (i may be wrong) .

    Please guide me. Please let me know where I am wrong?

    Thanks in advance.

    • The role of a common layer is to share entities between all layers so it is ok to add a reference to Common layer from any layer.

  4. Hi,

    i like the way you created a data access layer with LINQ, but i have a question about performance. Since you call everytime the function FetchAll and after you get only the records you what with a predicate or paging , the entire table is loader everytime. It’s ok for a table who has only a couple of records but what would you do with a table who contain 1 million of records ?

    thanks in advance 🙂


    • Thanks for the reply. you are actually right , but in that post I focused on how to arrange components and layers. I will post about a new model of such an architecture using Entity Framework and in that one I will work more on performance 🙂

  5. I have an question on the context.. What Microsoft says, you need to make the DataContext as short lived as possible, because it tracks every tiny thing you change on it.

    Therefore you have a huge lack of performance.



  6. This is very helpful article, very well explained, thanks a lot.
    BTW. I believe there’s already “Gates III” saved in the DB, because of this, an exception is thrown in this line
    Person p = business.FetchByFilter(x => x.LastName == “Gates”).Single();
    the collection is empty and the Single method cannot execute.


  7. You post is full of confusion specially for beginners. Because I tried it…find lots of difficulties in namespaces and logical

    This article was not for beginners 🙂

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com 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