Normally LINQ to SQL seems to be designed for 2 -tier program, especially when we use Visual Studio to visually create entities and DataContexts. No doubt, this is a very rapid way of developing a data access layer and using its classes for data operations. But when developing an enterprise application which is divided into multiple layers i.e. Data Access Layer, Business Layer, Service Layer and Presentation layer, etc. then developer gets confused in separating the business objects with the data access layer and to make them loosely coupled.

However, in order to utilize the LINQ to SQL for enterprise architecture I did some R & D and came to know that there are some methods supported by LINQ to SQL which helps to design a data access layer that is independent with the business objects. This helps the developer to use the same business objects even they are data contracts (specifically when talking in terms of WCF framework) or any other common business types.

Below procedure shows the way you can utilize the LINQ to SQL in multi tier architecture.

1. Create a Data Types layer which contains all types of business objects.
2. Create an empty BaseEntity class by which all the business objects will be inherited from. You can also put common properties under BaseEntity class like CreatedOn, CreatedBy, UpdatedOn, UpdatedBy usually common in all business objects.

public class BaseEntity
{

}

3. Now create a business object in our example its “Condition” class. [Note the property name should be equal to the result set column names of the procedure being called].

public class Condition :BaseEntity
{
Int32 conditionId;
public Int32 ConditionId
{
get { return conditionId; }
set { conditionId = value; }
}

String conditionDescription;
public String ConditionDescription
{
get { return conditionDescription; }
set { conditionDescription = value; }
}
}

4. Now create a Data Access Layer. Create a class Library Project and add LinqToSql, you may notice that .dbml file will be added into the solution. This provides the datacontext classes available to perform data operations.

5. Next, create a PersistanceManager class and adds following method. Here I have developed a wrapper of LINQ to SQL ExecuteQuery which provides the automatic binding of the type T and returns the List of T.

public List ExecuteQuery(String queryName, params Object[] param) where T : DataTypes.BaseEntity
{
List lst = new List();
using (DDMSDataContext context = new DDMSDataContext())
{
var items = context.ExecuteQuery(typeof(T), queryName, param);
foreach (var item in items)
{
T t;
t = (T)item;
lst.Add(t);
}
}
return lst;
}

As shown above, this method executes the procedure with the parameters supplied and returns the business object List to user. Once this is done you can call this method from Business layer or any other layer as follows

public List GetAllMedicalConditionsByConditionType(Int32 TypeId)
{
List lstMedConditions = PersistanceManager.GetInstance().ExecuteQuery("Exec SelectAllActiveMedicalConditionsByConditionType {0}", TypeId);
return lstMedConditions;
}