So we already know that LINQ gives us a way to compose queries against
IEnumerable<T> objects. LINQ to SQL gives us a way to describe our database
so that we can use LINQ queries against the data and have LINQ to SQL generate
the necessary SQL statements to get the results. With LINQ to SQL you can
query, insert and delete data all with fully compiled LINQ queries. Although
LINQ to SQL will take a back seat to the Entity Framework (I’ll cover that in
a future post) according to the ADO.Net team blog’s “Update on LINQ to SQL and LINQ to Entities Roadmap”,
LINQ to SQL remains a good simple way to set up a database.
I’ll just be covering the core concepts here. Each of these core concepts will still apply to LINQ to Entities although there are sure to be small differences.
In Visual Studio LINQ to SQL comes with a full designer experience. You’ll find “LINQ to SQL Classes” as an item under “Data” in the Add New Item dialog. Keep in mind that “DataContext” will be added to the end of the name you give. I’ve created many TestDataDataContext classes!
Once this item has been added you will be presented with a designer window.
Assuming you are using a SQL Server or SQL Express database you can simply
drag tables from the Server Explorer to the designer window (I’ll be
discussing the tool
sqlmetal.exe in a future post which can be used to get
LINQ to SQL working with SQL Server Compact Edition). As you add more tables,
the relationships will begin to appear. Dragging all these tables simply
generates extra classes under the scenes. There are four important files that
are part of the LINQ to SQL classes:
- TestDataContext.dbml is the core file. It is an XML formatted file that describes the tables and relationships in your Data Context.
- TestDataContext.dbml.layout is purely for the designer experience so that you can lay out your tables in a way that makes sense to you.
- TestDataContext.designer.cs is where things get interesting. This is the file that contains all the classes that are generated from your .dbml file. These are partial classes so you can add extra methods and properties in a separate file.
- TestDataContext.cs is the file where you put your extra methods and properties.
Each of the tables is represented as a class in your object model. The fields of the tables become the properties and relationships are also represented as properties. It is important to remember that the classes represent a single row from the database.
In addition to the classes representing your data model you also get a
DataContext class (in this example TestDataContext) which you use to interact
with your database. It has a property for each of your tables which are of
T is the name of your class. So if
you have a table of Users, you might call the class User (as it represents one
user) and your DataContext would have a property Users of type
Initialize your DataContext
All access to your data is done through the DataContext object. Before you can
begin you’ll need to create an instance of the class. If you’ve used Visual
Studio to connect to SQL Server or SQL Express database you probably have
access to the default constructor which will use the connection string from
your app.config file. If you’ve used
sqlmetal.exe or if you want to manually
specify a connection string you can use a simple constructor that just takes
the connection string.
In the next examples, we’ll assume we have already initialized the data context using the connection string from app.config like so:
Query the database (
Let’s start with a simple query to pull data from the database. Let’s say that we have a really simple blog with the following tables: Entries, Comments and Users. We’ll be using this same basic database through all of the following examples.
Let’s start with the front page. Because this is a simple blog all the posts will stay on the front page until we archive them. So we want to start by finding all the blog posts that haven’t been archived. Let’s start by looking at the raw SQL.
1 2 3
That’s a pretty simple query and in LINQ it is also simple:
1 2 3
Again, this is just our query definition, this line of code will not execute the query against the database. To actually execute the query we need to do something like this:
1 2 3 4 5 6
WriteBlogBody() methods are responsible for
sending the output to the web browser. Both methods just take a
are completely unaware of where the data is coming from.
I’ve included a TODO comment suggesting it might be good to show the number of comments for each entry. First I’ll show you how to do this by running an extra query for each entry:
1 2 3 4 5 6
Because comments are associated with the entry we can directly access its count. This line will cause another database query so has obvious performance implications (as it needs to hit the database server for every single post). Instead we’ll rewrite the query so we pull everything we need from the database in one query.
1 2 3 4 5 6 7 8 9 10 11 12
By creating a new anonymous type in the query we send one complex query to the database instead of lots of simple queries.
So what if a user actually wants to view those comments. This time we’ll accept that two database queries is acceptable (it’s a constant). So, let’s see how that might work for a page that displays a specific post.
1 2 3 4 5 6 7 8 9 10 11 12 13 14
It almost seems to easy to believe. Because the relationships are already defined LINQ to SQL is able to generate the second SQL statement to get each of the comments for that post. Most importantly we did all this without any strings!
Inserting data (
Right now the blog is pretty boring, it can display posts and comments, but how do the posts and comments get there? _place_holder; Let’s start with the simple example of creating a post.
1 2 3 4 5
That’s it. The post is now in the database. What about a comment?
1 2 3 4 5
We’ve been able to link the comment to the post by using the Entry property directly. We haven’t had to worry about how our tables are related in our code.
Deleting data (
Did you get a spam comment on your blog? Don’t worry, LINQ to SQL can help you delete it. It follows a similar model to that of inserting data.
Updating data (
Maybe you want to start cleaning up the front page because it is starting to get cluttered. We need to update the Archived field on each of the entries we want to archive. To keep things really simple, we’ll archive all the posts that aren’t already archived.
1 2 3 4 5 6 7 8
Remember to submit your changes
The key thing to remember when manipulating data with LINQ to SQL is to call
SubmitChanges() on your DataContext object. Only then will the SQL
statements be generated and executed on the server.
In my next LINQ post I’ll be exploring LINQ to XML.