There is no pre-requisite (Oh yes even if you do not know LINQ this article will guide you) for this article what I need from you guys is time to read this article. So block your 10 minutes and rest assured you will understand LINQ in a much better way.


In this article we will understand core reasons of why we should use LINQ. Three tier / N-Tier is now a standard in almost all projects. New architectures which are coming up like MVC, MVP have the fundamental base and thinking of 3-tier methodology. So we have termed the 3-tiers as SALT, in other words we can not stay with out it.

LINQ (Pepper) on other hand is a new technology which helps us create execute queries against disparate data sources like ADO.NET, Custom objects, XML etc. So it's not a needed thing (Pepper) but it does helps to remove lot of issues related with traditional 3-tier.

So let's make a nice Three-tier LINQ dish using SALT and PEPPER. So we will first understand Three-tier, issues with it and then see how LINQ helps us to improve the same.

The SALT traditional 3-tier  

Let's first go through the traditional 3-tier architecture. We understand for many people this is just a revision but the main point which we want to stress is the issues with three-tier and how LINQ helps us in addressing those issues.

So let's start from Data access layer and move towards the UI layers. The below code is pretty self explanatory open connection, process data adapter and send dataset back to the business logic layer. Ok, I can smell easily many experienced professional saying that the below code can be optimized using enterprise data access layer. Oh, yes we can but for simplicity sake lets take the below code for now. I really do not want people who have not used Enterprise data access layer to be left behind. Below code is a simple data access layer which queries a country master to retrieve countryid and country code.
namespace NameSpaceCountryDAL
public class clsCountryDAL
public DataSet getCountriesByDataset()
SqlConnection objConnection = new SqlConnection(strConnectionString);
DataSet objDataset = new DataSet();
SqlDataAdapter objAdapter = new SqlDataAdapter("Select * from tbl_country", objConnection);
return objDataset;

The second layer is the business logic layer. Below is the business logic layer which is used to load country data. We have inherited the class from a collection class and in the constructor we have loaded the country object from the dataset received from the DAL layer.
public class CountriesNormalBO : CollectionBase
public CountriesNormalBO()
clsCountryDAL objCountryDal = new clsCountryDAL();
DataSet obj = objCountryDal.getCountriesByDataset();
foreach (DataRow objRow in obj.Tables[0].Rows)
CountryEntity objCountry = new CountryEntity();
objCountry.CountryId = Convert.ToInt16(objRow[0]);
objCountry.CountryCode = Convert.ToString(objRow[1]);

Finally the UI code which uses the business objects collection and loads the values in the UI.
CountriesNormalBO objCountryBo = new CountriesNormalBO();
foreach (CountryEntity obj in objCountryBo)

Below is the how the code snippet and 3-tier architecture mapping looks like.

Issues with the traditional 3-tier architecture 

Now that we have understood what is a 3-tier architecture lets try to get the issues related with it. There are six big issues:-

• No uniform mechanism
• Need to write business logic mapping from DAL data sources.
• For complex business object model transformation there is more complexity attached.
• Querying and Sorting of business logic needs be done using custom code.
• DAL component can be custom and in-consistent.
• When we need to do CRUD operations using business object it increases complications.

So what we will do is go through all of these issues and then see how LINQ can help us solve the problems.

No uniform mechanism 

One of the biggest problems in 3-tier architecture is the way data travels and transforms across tiers inconsistently. Inconsistency of data representation and transformation is one of the main problems which stem other issues. 
• When the data is in the DAL component it's represented by ADO.NET objects. So data has ADO.NET context attached to it. In other words it's in DATASET, DATAREADER or some other ADO.NET object form.
• From DAL these objects are sent to the BO. In BO data is now represented by business object collection and entity. So now the data representation is in form of strongly typed objects.
• In UI the same strongly types business objects are referred and used. So in this section there is no transformation as such. 

Need to write the business logic mapping code

Due to data inconsistency we need to write custom transformation and mapping logic to convert the DAL objects like DATASET,DATAREADER
etc in to strongly typed business objects.
The above figure represents how the dataset object is looped and strongly typed list object is created in the business layer section. If we look more closely we need to perform five steps to get the DAL data format in to business layer format:-
First we create object of the DAL
• Get the data in to dataset
• Loop through the data rows of the data table.
• Create objects using the row object
• Finally we add the strongly object to list collection. 

Aggregation and composition increases complexity 

In 3-tier model the data model and the business object model are different. The business object model represents the real world model while the database model represents a data model of a database. For instance below figure shows we have an address table which is de-normalized for performance. All the data is represented by one entity, in other words by one table. When we look at the object model i.e. business model the name and address are represented by two entities with one as to many relationships as shown below. So you have one 'name' class and many 'address' classes aggregated in the name object. In other words 'name' and 'address' objects have one to many relationships.
Do to data inconsistency this transformation becomes more complex. You need to write more code and logic for transforming the de-normalized data in to aggregation and composition relationship in business object model.

Querying and sorting of business object

There are situations when you want to search within the strongly typed business objects. For instance you can see from the code snippet below we are searching for particular 'country' object using the 'ID' value. So we loop through the country object collection and when we find the 'ID' matching we return the object. This can become complicated when we have large number of objects in collection and it can be worse for strongly types aggregated and composed object collection.

Inconsistent DAL component 

DAL layer is one of the integral parts of 3-tier architecture. Normally all Microsoft projects use enterprise data application blocks. But then there are projects that use their own DAL components and some special projects use third party DAL components which can connect to heterogeneous databases like SQL Server, ORACLE etc. Even with in same project some one can use a dataset , some on can use datareader to transfer data between tiers.
In other word non-uniform DAL implementation.

A word about Enterprise data application block from the consistent DAL perspective. An Enterprise data block even though provides a very consistent mechanism of DAL implementation it has one minor issue:-
• It's a part of a different framework i.e. Enterprise application block and not of the core framework. LINQ forms the part of the framework. So we do not need to install a separate framework for DAL implementation.

CRUD (Create, Update and delete) multiplies complication 

70% projects in IT industry are CRUD projects. In 3-tier architecture the CRUD operation needs to transfer data to and fro between layers. Due to inconsistent data representation between tiers the matter gets more complicated for CRUD operations.

Consolidating issues using a Fishbone 

Fish bone helps us to identify the potential factor which causes the various other issues.
You can read about fishbone on  .

So what we have done is we have listed down all the issues and we are trying to figure out the main cause. You can see the non-uniform data representation is the main cause of all issues. If we are able to crack that issues all other issues can be eliminated.

30,000 feet solution view to solve Non-uniform mechanism 

So let's see to solve the above problem what do we need. So we have two major issues one is the inconsistent data representation and the other is we need a generalized query mechanism by which we transform and filter business data.
So to solve the problem we need to figure out a consistent data representation across all tiers and general query mechanism by which we can filter and sort business objects.

Going back to basics what is data 

So let's first try to solve the first problem, by defining what DATA is?. If we really see at a granular microscopic level data is just a field with value. Yes, you can also say data has relationships between them but for now we will keep it simple. So DATA is nothing but a field and a value.
If you visualize this field value concept for a data can be understood by any tier. So if we can pass data in representation of field and value across tiers we can remove the inconsistent representation of data.

This field and value when represented by a class is termed as Entity. In the next section we will discuss how we can use the entity to establish a common representation mechanism across tiers.

Entity – the common protocol across layer 

Entity is nothing but a class with 'set' and 'get' properties. For instance below is a country entity class which has country code and country id. You can see how the table data structure is represented using the entity class.
This Entity A.K.A field/value can be used as a common representation platform across any tier. So let's shout with one voice "WE ALL UNDERSTAND ONE REPRESENTATION ENTITY". 


Common Query Mechanism 

The other issue we discussed was about querying and filtering the business objects. Due to inconsistent data we need to implement custom logic for transforming the data model in to object model. Due to which the code complication increases tremendously. 
To handle this problem we need also have a technology which can implement generalized query mechanism on any heterogeneous data sources like ADO.NET, Custom business objects, XML etc.

To summarize all the above mentioned issues for three tiers can be resolved by implementing a common data representation and a generalized query mechanism. So do we need make some framework – NOPE – the answer is LINQ.

LINQ the Pepper - Generalized Query and entity transformation Mechanism 

So we have discussed about the three tier and its issues. We have also looked from 30,000 feet angle the solution i.e. a common representation using entity and a generalized query mechanism. LINQ helps us to achieve both of them.

Let's first try to understand basics regarding LINQ. So what we will do is that we will create a simple 'Customer' table with 'CustomerCode' and 'CustomerName' fields and let's try to load the same using LINQ.

The first step is defining Entity – the common representation in LINQ. Below is the 'ClsCustomer' entity class which is attributed by using the 'Table' attribute. This table attribute is mapped with the physical table name present in the database. The SET and GET properties are mapped using 'Column' attribute as shown in the below code snippet. 
Now that we have built our Entity class using the 'Table' and 'Column' attribute its time to load the entity objects. 'DataContext' helps us to load the entity objects in LINQ. It acts a bridge in other words its a DAL layer between SQL Server database and the entity objects. 
So the first step we need to create the 'DataContext' object using the connection string.
DataContext db = new DataContext(@"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\SimpleLINQExample\WindowFormLINQ\
Country.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");

Now we can tag the customer entity class i.e. 'ClsCustomer' with 'Table' using generics and load data using the 'GetTable' function of the datacontext object.
Table<clsCustomer> Customers = db.GetTable<clsCustomer>();

We prepare a query on customer code basis and fire it on the 'Customers' object which we have created using 'Table<clsCustomer>'. LINQ queries look unusual but they are very powerful to solve complex business objects filtering and transformation.
var query = from c in Customers where c.CustomerCode == txtCustomerCode.Text
select new { c.CustomerCode, c.CustomerName };

Finally we use the enumerator to loop through the objects and display the data in UI.
var enumerator = query.GetEnumerator();
if (enumerator.MoveNext())
var customer = enumerator.Current;

Generalized Query 

One of the powerful things about LINQ query is that it's generalized. Below figure shows how we have used the same query to fire on an ADO.NET data source and a custom strong type's business collection.
You can get source code at the top of this article which shows how generalized queries are implemented using LINQ.

The sample has two button one which loads from the database and the other from customer object collection and the query is same.

 Three Tier Approach using LINQ 

Ok, now that we have understood the basics of LINQ. Let's move ahead implement LINQ in 3-tier architecture.
As said previously to solve the inconsistent data representation across tiers we need to represent the data format in entity. Even though we use the entity class type to represent our data format the entity has always a context attached. So when the entity is in the DAL component it has the 'Table' context and when the entity is sent to the business layer it has the 'IEnumerable' interface context and in the UI it's a plain entity class. 
Below is the code snippet for data layer. As this is the DAL we are creating the 'DataContext' object and attaching the entity with the 'Table' context.
public Table<CountryEntity> getCountries()
DataContext db = new DataContext(strConnectionString);
Table<CountryEntity> Customers = db.GetTable<CountryEntity>();
return Customers;

Once it comes to the business layer we are firing the LINQ query and attaching the 'IEnumerable' context. One of the important point to note is that we do not have any transformation code , all the magic is in the LINQ query.
public IEnumerable<CountryEntity> getCountries()
clsCountryDAL objCountryDal = new clsCountryDAL();
return from c in objCountryDal.getCountries() select c;

The UI happily loops through the business collection and displays the same in the UI.
CountryBO objCountryBO = new CountryBO();
foreach (CountryEntity obj in objCountryBO.getCountries())

You can download the 3-tier code from the top of this article.

Depends how you view LINQ 

So depending from which layer you are looking at LINQ you can visualize how LINQ can be useful. In the DAL component it can act as a readymade data access , when data is moving from DAL to BO it can provide functionalities for transformation to object model and finally in the BO itself it helps us to query and filter data.

Quantifying how much effort we can save using LINQ

Ok, let's quantify how much effort we can save using LINQ and also how much standardization we can achieve in project. In DAL side we can see 7 lines of code is only 3 lines. The most important thing it eliminates the non-standardized DAL components.
The best productivity using LINQ is in business object layer. You can see how 7 lines of code are minimized to 2 lines of code. 

Source code

You can download 3-tier LINQ source code from the top of this article.

You can download Simple LINQ source code from the top of this article which shows how generalized query can be implemented using LINQ