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.
Basic structure
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 type System.Data.Linq.Table<T> where 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 System.Data.Linq.Table<User>.
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:
var db = new TestDataContext();
Query the database (SELECT)
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.
SELECT *
FROM Entries
WHERE e.Archived = 0
That’s a pretty simple query and in LINQ it is also simple:
var frontPage = from e in db.Entries
where !e.Archived
select e;
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:
foreach (var entry in frontPage)
{
WriteBlogTitle(entry.Title);
WriteBlogBody(entry.Body);
// TODO: Show number of comments
}
Here WriteBlogTitle() and WriteBlogBody() methods are responsible for sending the output to the web browser. Both methods just take a string and 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:
foreach (var entry in frontPage)
{
WriteBlogTitle(entry.Title);
WriteBlogBody(entry.Body);
WriteBlogCommentCount(entry.Comments.Count);
}
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.
var frontPage = from e in db.Entries
where !e.Archived
select new {
Entry = e,
CommentCount = e.Comments.Count
};
foreach (var post in frontPage)
{
WriteBlogTitle(post.Entry.Title);
WriteBlogBody(post.Entry.Body);
WriteBlogCommentCount(post.CommentCount)
}
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.
var post = (from e in db.Entries
where e.Id == id
select e).FirstOrDefault();
if (post == null)
{
// Post could not be found
throw new ArgumentException();
}
WriteBlogTitle(post.Title);
WriteBlogBody(post.Body);
foreach (var comment in post.Comments)
{
WriteBlogComment(comment.Comment);
}
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 (INSERT)
Right now the blog is pretty boring, it can display posts and comments, but how do the posts and comments get there? Let’s start with the simple example of creating a post.
var post = new Entry();
post.Title = "My first post";
post.Body = "I love LINQ. It's the best.";
db.Entries.InsertOnSubmit(post);
db.SubmitChanges();
That’s it. The post is now in the database. What about a comment?
var comment = new Comment();
comment.Comment = "I love LINQ more";
comment.Entry = post;
db.Comments.InsertOnSubmit(comment);
db.SubmitChanges();
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 (DELETE)
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.
db.Comments.DeleteOnSubmit(comment);
db.SubmitChanges();
Updating data (UPDATE)
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.
var toArchive = from e in db.Entries
where !e.Archived
select e;
foreach (var entry in toArchive)
{
entry.Archived = true;
}
db.SubmitChanges();
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.
What’s next?
In my next LINQ post I’ll be exploring LINQ to XML.