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.

One of the stumbling blocks on the road to understanding LINQ is deferred execution. The key to getting past this is being able to identify that a query is a definition of what you want, rather than the results themselves.

Here’s an example of how this works:

var itemsInStock = from item in warehouse.Items
                   where item.Quantity > 0;
                   select item;

// Display how many items are in stock
Console.WriteLine("Items in stock: {0}", itemsInStock.Count());

// Add a new item to the warehouse
warehouse.Items.Add(new Item("A new item", 50);

// Display how many items are in stock
Console.WriteLine("Items in stock: {0}", itemsInStock.Count());

The second time itemsInStock.Count() is called it returns the updated count that includes our new item. Instead of executing the query when it is defined, execution is deferred until a result is needed (such as iterating over the collection with a foreach loop, using ToList() to store the results in a List<T> or one of the many LINQ extension methods that force an actual result (such as Count() in this example). This has the added benefit of allowing a query to be extended like so:

var lowStock = from item in itemsInStock
               where item.Quantity < 5;
               select item;

This query can now be used to return items that are in stock, but have less than 5 available units.

Quite often you’ll want to work with a snapshot of the results from a query. Maybe you are writing a method that returns a particular set of items. In this scenario it may be better to return a list rather than the query itself. By returning a list, the calling code is able to iterate over the result multiple times without the result changing. For example you might implement your method like this:

private IEnumerable<Item> GetItemsInStockQuery()
{
   return from item in warehouse.Items
          where item.Quantity > 0
          select item;
}

public List<Item> GetItemsInStock()
{
   return GetItemsInStockQuery().ToList();
}

Calling code is able to get the information it needs and internally you can directly get access to the query.

Another important thing to remember is that because a query is executed every time you iterate it with a foreach loop you should use ToList() if you are repeatedly calling the query and don’t need the results to be recalculated each time.

More LINQ to come

In my next post I’ll explore lambda expressions.

Have you ever wished that a base class had a particular method? What about interfaces? Wouldn’t it be great to define a method on an interface along with its implementation? Any class that then implemented the interface would get this implementation for free.

In the past this was achieved with static utility classes. Unfortunately this leads to cluttering your code with the names of these utility classes and dilute the expressiveness of your code. Let’s say we have a utility class the gets the words and word count from a string. Don’t worry too much about the implementation, just the general structure.

public static class StringUtilities
{
   private static readonly Regex wordsRegex = new Regex(@"\w+");

   public static IEnumerable<string> GetWords(string source)
   {
      return from word in wordsRegex.Matches(source).Cast<Match>()
             select word.Value;
   }

   public static int WordCount(string source)
   {
      return GetWords(source).Count();
   }
}

To use this in our code we would have to do something like this:

var sentence = "The quick brown fox jumps over the lazy dog";

// Display each of the words
foreach (var word in StringUtilities.GetWords(sentence))
{
   Console.WriteLine(word);
}

// Display the word count
Console.Write("Total Words: ")
Console.WriteLine(StringUtilities.WordCount(sentence));

Look at all that clutter. The truth in this context is that we are really performing an action on the sentence. Wouldn’t it be better if we could just call sentence.GetWords() or sentence.WordCount() instead? It would certainly be more readable. Extension methods make this all possible. Here’s our updated StringUtilities class that creates the extension methods:

public static class StringUtilities
{
   private static readonly Regex wordsRegex = new Regex(@"\w+");

   public static IEnumerable<string> GetWords(this string source)
   {
      return from word in wordsRegex.Matches(source).Cast<Match>()
             select word.Value;
   }

   public static int WordCount(this string source)
   {
      return GetWords(source).Count();
   }
}

We’ve added this before the variable type. The rest of the code has been left untouched. So now we can use the extension methods like so:

var sentence = "The quick brown fox jumps over the lazy dog";

// Display each of the words
foreach (var word in sentence.GetWords())
{
   Console.WriteLine(word);
}

// Display the word count
Console.WriteLine("Total Words: {0}", sentence.WordCount());

Doesn’t that read better? We have been able to push the implementation details (the name of the static utility class) out of our code.

How to enable an extension method

In order to use an extension method it must be part of the local namespace or imported with a using statement. Once that’s done you can call extension methods just as you would any normal method.

What does this have to do with LINQ?

LINQ is all about extension methods. When you import the System.Linq namespace it comes with a whole bundle of extension methods. Most of them act on IEnumerable<T> and can be used to write your LINQ queries in method syntax. Let’s look at this query:

from item in items
where item.Price < 1
select item.Name

This query finds the items that are under one dollar and returns their names. We can write this query in method syntax like so:

items.Where(item => item.Price < 1).Select(item => item.Name)

It’s not quite as readable (although that is a matter of opinion), but it gives a good indication of what is going on (and further demonstrates why select is at the end). These methods also take advantage of Lambda expressions (which I’ll discuss in a future post).

There are other useful extension functions that work with queries. Some of the ones you’ll use most often are:

• ToList() executes the query and returns the results in a list. You will probably use this method a lot. I’ll cover this method an its consequences in more depth in a future post on deferred execution.
• Count() executes the query and returns the number of results. When used with LINQ to SQL it will execute SQL code to get the database server to return the count.
• Any() returns true if there are any results in the query. Use this instead of Count() > 0 to abstract out the implementation detail.
• First() returns the first result from the query. This is particularly useful when you have a query that will only return one result (such as looking up an entry based on its primary key). This method will throw an exception (InvalidOperationException) if the query yields no results.
• FirstOrDefault() returns the first result from the query, much like First(). If there are no results it will return the default for the type (e.g. 0 for an int, null for reference types).

Fortunately you aren’t limited to using these extension methods on LINQ queries. They are designed to work on any class that implements IEnumerable<T>. This means you can use them directly on a lot of the classes already in the .NET base class library.

What about old non-generic IEnumerable?

There are a lot of classes in the .NET framework that don’t implement IEnumerable<T> but instead implement the non-generic interface IEnumerable. A perfect example is MatchCollection used by Regular expressions. When we enumerate over a MatchCollection we are given the base object which we then need to cast to a Match object. Until we do this cast we can’t access any of the properties of Match. Fortunately there are a couple of LINQ extension methods designed to help out when dealing with IEnumerable.

• Cast<T>() returns a strongly typed IEnumerable<T> object. Each object is cast to the type T. If an object can’t be cast an exception is thrown (InvalidCastException). In the case of a MatchCollection I am confident that every object is a Match object and an exception won’t be thrown.
• OfType<T>() also returns a strongly typed IEnumerable<T> object. It goes further than Cast<T>() by only including objects of that type in the enumeration. In other words it filters out any class that isn’t of the desired type (without throwing exceptions). This is the method to use when you are unsure of what the type will be or if you are dealing with an enumeration that contains different typed objects.

If you want to see OfType<T>() in action, copy and paste the following example into LINQPad. (You’ll need to select C# Statement(s) from the language drop down).

var items = new object[]{"a string", 22, Math.PI};

items.OfType<string>().Dump("OfType<string>");
items.OfType<int>().Dump("OfType<int>");
items.OfType<double>().Dump("OfType<double>");

LINQPad has its own extension method Dump() which is used to output results to the LINQPad window. You’ll see that each individual dump returns a strongly typed IEnumerable<T> object. In this example items actually implemented IEnumerable<object>. Fortunately these methods don’t discriminate and happily work their magic on any IEnumerable<T> as well.

Still more to come

There is still plenty of more that I will post about LINQ. In my next post I’ll look at deferred execution, what it means and how you can take advantage of it.