Using LINQ to SQL in N-Tier Architectures

• Download source code - 61.2 KB

Introduction

This article assumes some basic knowledge and experience with LINQ to SQL. The article targets the design approach of using LINQ to SQL in N-Tier architectures, explains the solution to some of the problems faced, and gives a sample code for the solution.

Connected vs. Disconnected Business Objects

Most software applications are data driven. This includes web / Windows / Web Service / network, or other types of applications. When dealing with the Data Access Layer, one of the most frequent design challenges is to decide if you want to work in “Connected” or a “Disconnected” mode.

The answer actually depends on what you are trying to achieve. If you work on 2-Tier architecture, and require high performance, the most feasible choice would be to work in a “Connected” mode. However, when you work in a 3-Tier architecture, the “Disconnected” mode will be a more logical choice. Why? In 3-Tier architectures, the Business objects are transferred from the middle-tier to the (smart) client-tier. The communications is two way, meaning objects are transferred back and forth.

“Connected” means that the objects somehow maintain the connection to the Data Tier, and the connection is used to retrieve details, or child objects when needed. “Disconnected” means the objects are serialized/de-serialized and the connection is not maintained with the BOs.

LINQ to SQL gives a very good solution if you decide to work in “Connected” mode. The generated ORM uses the DataContext to retrieve information, or child objects. However the adaptation of LINQ to SQL to work in N-Tier architectures doesn’t come out-of-the-box. Some additional work will be required. This topic explains the problems found in this approach, and the best practices.

Using LINQ to SQL with WCF

.NET 3.0 came with a very nice enhancement to Web Services, called Windows Communication Foundation. It gives you the ability to write more robust and more extensible Web Services.

If you use normal Web Services with the generated objects from LINQ to SQL, it won’t work. This is because the generated classes won’t have the [ISerializable] attribute. However, they still can be serialized using the DataContractSerializer, since they have the DataContract attribute. This makes WCF a convenient way to transfer objects between different tiers.

Unidirectional vs. Bi-Directional Serialization

The generated objects in LINQ to SQL do not support serialization by default. To change that, you have to change the serialization mode in the LINQ to SQL designer to “Unidirectional”.

But, what does it mean to serialize in a unidirectional mode? Let’s first have a look at the following Data-Model. Category can have many Materials, and Material can have many Prices.

If you use the unidirectional mode, only child relations will be generated; the Material class will have a property Prices, as in the following code:

[DataMember(Order=20, EmitDefaultValue=false)]
public EntitySet<Price> Prices;

However, the parent relations will not be generated, so there will be no relation to the Category class. And, this is actually too bad. Because I will miss the option to write a piece of code like this:

string name = material.Category.Name;

But, what is the problem with bi-directional serialization? Suppose we use bi-directional serialization; in this case, the Material class will contain a reference to the Category class, and the Category class will contain a reference to the Material class. This is a cyclic dependency. If the serializer is not smart enough to handle such a case, the serializer will get stuck in this cyclic dependency. A partial solution is available in .NET 3.5 SP1.

Using the (IsReference=true) Attribute in DataContract Serialization

In .NET Framework 3.5 SP1, a new enhancement was done to mark a DataContract with the (IsRference=true) attribute. This will tell the serializer (DataContractSerializer) that this contract is a reference, so that it will deal with it as a reference, and in this case, it will be able to detect the cyclic dependencies.

The problem now is that no one updated the LINQ to SQL designer to add a new mode for “Bi-Directional” serialization. Um… this is a real problem. I believe they will fix it in the next release of Visual Studio, but for now, there is another solution from my hero damieng at CodePlex: http://www.codeplex.com/l2st4.

To use the template in your project:

1. Download it from the above link.
2. Include the template in your project
3. Rename the tt file to the same name as your DBML file.
4. Open the tt file, and set SerializeDataContractSP1 = true. As we discussed before, this will generate bi-directionally serializable objects.
5. Click the tt file and choose “Run Custom Tool”.
6. You must change the compilation option for the original designer.cs file. Set the build action to None, instead of Compile, to avoid having duplicate generated classes with the same name.

Using Load Options to Pre-load Child/Related Objects

In LINQ, classes are loaded from the DB only when needed. However, in a disconnected environment, you might need to pre-load the child objects and send them all to the client at once. You need to save multiple trips to the server, and load all that you need in one trip.

To achieve this, you need to use the load options with the data context. The following code is an example:

DataClasses1DataContext dc1 = new DataClasses1DataContext(); 

DataLoadOptions dlo = new DataLoadOptions(); 
dlo.LoadWith<Material>(m => m.Category); 
dlo.LoadWith<Material>(m => m.Prices); 
dc1.LoadOptions = dlo; 
return dc1.Materials.ToList();

Optimistic Concurrency Checks

In a multi-user environment, it is very much possible that two users try to edit the same row at close times. Suppose that in a 3-Tier environment, the following scenario happens:

1. User A loads a record into his Smart Client.
2. User B loads the same record into his Smart Client.
3. User A makes a modification and saves the record.
4. User B makes a different modification and saves the same record.

If there is no concurrency check, user B will overwrite User A’s changes. The ideal situation is that user B will receive a message that the current version of his record is not valid anymore and he has to load the latest version before saving his modification.

To do so, add a timestamp column in all editable tables. Configure the properties from the DBML designer as in the following figure. The generated SQL queries will actually contain all the necessary checks for you. You won’t have to do any additional work. Except, of course, to catch the row conflict exception and handle it properly as your business requires.

Using the Code

I hope that the attached code is self-explanatory regarding how to use LINQ with an N-Tier architecture. To be able to use the code, first create a database on your localhost\SQLEXPRESS instance with the name: “LINQ”, and use the included .sql file.

Please download the AdventureWorks databse to make the code work: http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en.