A look into LINQ to SQL

This November 2007 Microsoft released the .Net framework 3.5, and among the several additions incorporated in this release, probably the most significant is the Language Integrated Query (LINQ), which provides several enhancements in the way applications can be developed.

In this post we will take a look into LINQ to SQL, which is the approach provided by LINQ to help developers accelerate the development of the data access layer.

Probably the three most critical functionalities of the data access layer are transactions, concurrency, and efficiency. We will take a look into how LINQ to SQL supports this.

Getting started

In order to see how to use LINQ, we will create a simple application that will allow us to create, edit and delete books, and assign categories to it. The database has 4 tables and one stored procedure.

The first step is to create a new Class Library Project (Our data access layer project) and add a new LINQ to SQL class called Library.dbml to it.


As a next step, we need to connect to the Microsoft SQL Server 2005 database using Server Explorer in Microsoft Visual Studio 2008.

This way we can see in our IDE all the tables and stored procedures available in our database.

We need to drag and drop all of these tables into the left side of the design surface of the Library.dbml file we created.

We also need to drag and drop the stored procedure into the right design surface of the Library.dbml file.

Once this is done, our Library.dbml file should look like this:


When we dragged and dropped all this information into our dbml file, what we did was to create a DataContext that maps all the information related to the database itself, by using attributes inside our .NET code. If we want to see the code generated by the designer, we only need to open the Library.designer.cs file nested under the Library.dbml file in our Solution Explorer. As you may imagine, there are many attributes that can be used and specified for our applications to customize the database access, but for our example the default values will work just fine.

This data context will allow us to use simple entities to access the database without having to write SQL code, as we will see ahead. This shows us how easy it is to create a functional representation of our database, even without having to write code for our Data Access Layer.

Next we create a new Class Library Project (Our business layer project) and add a new C# class called BookBL.cs to it.

Here we will code all methods required by the presentation layer, in order to execute business operations and access the database layer.

A method to add a new Book will look like this:

public Book addBook(Book book)
{
LibraryDataContext db = new LibraryDataContext();
db.Books.InsertOnSubmit(book);
db.SubmitChanges();
return book;
}

Here we create a new instance of the data context file we just created (Library.dbml). We know it contains a Books entity because we mapped the Books table into our data context. LINQ to SQL provides the method InsertOnSubmit in order to automatically store the book information for us, so we just call this method to add the new book. At the end, we execute the method SubmitChanges, also provided by LINQ to SQL, in order to commit our changes into the database. As you may see, we haven’t written any SQL code, and the LINQ to SQL framework has taken care of most of the data access work.

Another interesting operation is to query the database. For this we need to create a method called findByCategory in our BookBL.cs class that looks like this:

public List<Book> findByCategory(int id)
{
LibraryDataContext db = new LibraryDataContext();
var books =
from b in db.Books
where b.Category.id == id
select b;

return new List<Book>(books);
}

Here we are using LINQ syntax in order to query the database. We are storing the information retrieved from the database into a var datatype variable. This isn’t a variable datatype as in the old days of VB 6; instead it is a generic datatype that will be strongly typed with the specific datatype resulting of the database query (in our example, it will be strongly typed with IQueryable). The compiler will restrict us what can we assign to this variable, and it will also provide intellisense to it.

Another interesting thing to mention is that LINQ to SQL will allow us to execute stored procedures using LINQ syntax. So for example if we wanted to rent a book using our rentBook stored procedure, we could invoke it this way:

public void rentBook(int id)
{
LibraryDataContext db = new LibraryDataContext();
db.rentBook("anonymous", id);
}

Transactions

LINQ to SQL also helps developers in this point. By default, when the SubmitChanges method is called, if a transaction is not already in scope, the SubmitChanges method will automatically create a new transaction. All database operations executed during a single SubmitChanges will be wrapped into a single transaction, and as such, if any error occurs, the whole operation is aborted.

If we need to handle transactions at a higher level, or maybe even nest several SubmitChanges into a single transaction, we can make us of System Transactions, as int the following example:

public Book addBook(Book book)
{
using (TransactionScope ts = new TransactionScope())
{
LibraryDataContext db = new LibraryDataContext();
db.Books.InsertOnSubmit(book);
db.SubmitChanges();

return book;
}
}

Concurrency

It is possible to implement both Optimistic and Pessimistic concurrency for conflict resolutions when using LINQ to SQL.

By default LINQ to SQL takes care of optimistic concurrency, providing two different options to handle it.

One option is to provide a timestamp datatype column in the table were conflicts are expected. Using this approach, the LINQ to SQL framework will automatically update the timestamp column with every insert / update commited into the database. When someone tries to update a record by executing the SubmitChanges method, LINQ to SQL checks if the timestamp provided is valid. If it is different to the current timestamp, it means someone previously updated the record, and because of this data needs to be refreshed, so a ChangeConflictException is thrown.

So, a proper way to do an update operation is listed below, where we handle any possible ChangeConflictException:

public Book updateBook(Book book)
{
LibraryDataContext db = new LibraryDataContext();
db.Books.Attach(book, true);
try
{
db.SubmitChanges();
}
catch (ChangeConflictException cce)
{
throw new DataOutOfSyncException();
}

return book;
}

The other option of providing optimistic concurrency is by specifying which of the columns of a table should be checked for conflicts. If the listed columns differ when making an update, then a ChangeConflictException will be thrown.

To handle pessimistic concurrency, the only thing that needs to be done is to read the record, and then update it, but executing both operations inside a single System Transaction.

Efficiency

Object Relational Mapping solutions have always faced the challenge of generating efficient SQL statements. Since this technology is not new, great improvements have been achieved towards this goal. Even though, it is well known that for particular database operations that demand high optimization, the best solution is to manually create and tune the SQL statement.

LINQ to SQL provides the feature to print out the SQL statements being generated, so that the developer can decide if the generated statements are good for the application, or if manual intervention is required.

Conclusions

LINQ to SQL is a very powerful solution that allows us to reduce the amount of code, and hence, the amount of time invested developing applications. The data access layer is considerably automatized, and operations such as transactions and concurrency are easier to handle.

All this ease of development comes with the price of not-optimized SQL statements being generated. When choosing LINQ to SQL as the core data access technology we should study generated SQL statements to identify whether or not we need to manually enhance them. Depending on the application, the SQL statements being generated may be just good for the job.

Object Relational Mapping (ORM) solutions are not new, but LINQ to SQL is. This means that it lacks many of the features that more mature frameworks such as Hibernate provide. To tackle this, Microsoft is developing the ADO.NET Entity Framework as a separate addition to the .NET framework. This new framework will provide more functionality to the mix, while making development a little bit more complex.

This kind of ORM solutions have dominated the Java world for enterprise applications, and have done it for several years now. It won't come as a surprise if something similar happens with .Net applications, so we should pay close attention to this kind of solutions.