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:
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:
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 = "[email protected]",
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.