LINQ to SQL

The previous sections of the tutorial have covered the basic elements of LINQ. Now we will look at a domain specific implementation which uses LINQ, i.e. LINQ to SQL.

LINQ to SQL is an implementation of an Object Relational Mapper (O/RM). With LINQ to SQL you can derive an object model of a relational database. The object model is a set of classes which not only mirror the structure of data within a particular database, but it provides methods to insert, update and delete data. It also supports transaction and stored procedures. The key advantage of LINQ to SQL is that it allows the developer to construct an application without having to concern themself with the underlying mechanisms for connecting to and querying a database at database level, e.g. with SQL.

Creating the initial model

We will use a much simplified application as the scenario - that of a bookshop. The bookshop database simply lists books and publishers and the SQL Server dabatase model looks like this:

Bookshop SQL Server database model

Note: the tables are named Books and Publishers and there is a one to many relationship between the Publishers table and the Books table through the key field PublisherID.

Without LINQ to SQL we would need to manually code connections to the database and create commands using SQL. All code is witihn the application. We could write a data access layer/class to encapsulate our requirements, but this would need to be done afresh for each new database and application. What LINQ to SQL provides is a generic library which will automatically create the data access layer for you, in such a way that you can use it directly, and even extend it, should you wish.

To create our O/RM model we simply add a new item to our application of type LINQ to SQL Classes. In our case I will add a class and call it Bookshop.dbml. The designer opens and we can drag the tables and functions we require from the Server Explorer window onto our designer. When the Books and Publishers tables are dragged onto the model the designer surface looks like this:

LINQ to SQL model of the Bookshop database

Note: LINQ to SQL has generated classes based on the two tables. Not only that it creates a DataContext class with properties for each table in our database. The data content class is called BookshopDataContext and when we instantiate it, we can use it's methods and properties to interact directly with the database.

Querying our database

If we want to retrieve data from the database we first create an instance of our data contenct class and then we can use LINQ syntax to perform the query against objects in our database. For example, to return a list of Books by Luke Rhinehart the code would be:

BookshopDataContext db = new BookshopDataContext();

var lukesbooks = from book in db.Books
                 where book.Author == "Luke Rhinehart"
                 select book;

Of course we could use the LINQ extension methods, instead of LINQ query syntax:

BookshopDataContext db = new BookshopDataContext();

var lukesbooks = db.Products.Where( book => book.Author == "Luke Rhinehart");

Modifying data

If we retrieve data from the database we can modify its values directly. Once we want to update the database we call the SubmitChanges method wich commits all previous transactions to the database. Each modelled table (e.g. Books) has an Add method which allows it to insert a new record. Again, the insert is only committed once we call the SubmitChanges method. For example, to add a new publisher we would:

BookshopDataContext db = new BookshopDataContext();

Publisher pub = new Publisher {
  PublisherName = "Wiley",
  Location = "London",
  ContactName = "Joe Bloggs",
  ContactEmail = "joe@wiley.co.uk",
  ContactTel = "02345 789543" };

db.Publishers.InsertOnSubmit(pub);
db.SubmitChanges();

To retrieve the record for Wiley and update the telephone number we would:

BookshopDataContext db = new BookshopDataContext();

var pub = db.Publishers.Single(p => p.PublisherName == "Wiley");

pub.ContactTel = "08705 123456";

db.SubmitChanges();

To delete the record for Wiley from the database we would:

BookshopDataContext db = new BookshopDataContext();

var pub = db.Publishers.Single(p => p.PublisherName == "Wiley");

pub.Publishers.Remove(pub);

db.SubmitChanges();

Further actions

If our database contains stored procedures we can include these in our LINQ to SQL model and the methods will be available directly to the developer. We can also take advantage of other server side processing, such as paging of query results.

The deferred execution model means that database handling is done efficiently. The extension methods Skip and Take use this feature and allow the developer to deliver paged results with minimal overhead in the local application, as the pagination is done on the database server.

Valid XHTML 1.0! | Valid CSS! | WCAG Approved AA
Page design by: John P Scott - Hosting with: Netcetera