In my previous article, I looked at some of the core fundamentals of .NET 3.5 and C# 3.0, in particular we looked at LINQ to SQL using ad-hoc queries. In this article we will build on what we learnt and look at LINQ to SQL using stored procedures, and LINQ to XML; we will also demystify lambda expressions through a series of practical examples.

LINQ to SQL

One of the questions I often get asked when talking about LINQ to SQL is about how well it plays with stored procedures, after all not many companies or developers like to leave their database open to ad-hoc queries – we like to lock things down. The good news for anyone seriously looking at LINQ for use in their applications is that you can use stored procedures and still keep the flexibility offered by ad-hoc queries.

For all the examples in this article we will use the same database schema as the earlier article Next Generation Data Access with LINQ.

Configuring our Data Access Layer for Stored Procedures

We will use the SqlMetal tool again to generate our data access layer (DAL), however, this time we will look at how to hook up our DAL to use the stored procedures defined in our database. Setting up our data access layer for use with stored procedures is unbelievably easy using LINQ to SQL, even if you decided not to use the SqlMetal tool

The following snippet shows the definition for a stored procedure GetAuthors:

[StoredProcedure(Name="GetAuthors")]
public StoredProcedureResult<GetAuthorsResult> GetAuthors() {
  return this.ExecuteStoredProcedure
  <GetAuthorsResult>(((MethodInfo)(MethodInfo.GetCurrentMethod())));
}

One of the things to note is that when using SqlMetal to extract stored procedures from our database, the tool actually generates what looks like an entity for that stored procedure result:

public partial class GetAuthorsResult {
  private int _AuthorId;
  private string _AuthorName;
  public GetAuthorsResult() {
  }
  [Column(Name="AuthorId", Storage="_AuthorId",
  DBType="Int")]
  public int AuthorId {
  get {
  return this._AuthorId;
  }
  set {
  if ((this._AuthorId != value)) {
  this._AuthorId = value;
  }
  }
  }
  [Column(Name="AuthorName", Storage="_AuthorName",
  DBType="NVarChar(50)")]
  public string AuthorName {
  get {
  return this._AuthorName;
  }
  set {
  if ((this._AuthorName != value)) {
  this._AuthorName = value;
  }
  }
  }
}

The problem with this is that the GetAuthorsResult enumeration is actually identical to the entity Author already defined in the data access layer. There are a few ways around this, of which the simplest has been demonstrated excellently by Mike Taulty of Microsoft UK in one of his blog posts.

Note: Remember this is still a very early CTP of LINQ, so it’s likely that the final code generation tool will take into account the similarities between already defined entities and stored procedures.

Running a Few Queries

With our data access layer embracing the use of stored procedures, it’s time to run through some example queries to prove just how easy it is to use LINQ to SQL, and the flexibility it maintains in comparison to ad-hoc queries. Just like any custom DAL where you would map rows of data to user-defined entity types and expose them as a series of methods – we achieve the same effect here; however, the LINQ to SQL implementation is backed up with all the great new language enhancements introduced in C# 3.0 like anonymous types.

To retrieve all authors use:

BookShop db = new BookShop(_conn);
foreach (var author in db.GetAuthors()) {
  Console.WriteLine(author.AuthorName);
}

Just as I explained in the previous article there will be many times when you want to specifically choose what properties you want access to, and maybe you even want to filter the objects in a way that is not defined in the stored procedure – this is all possible when using stored procedures in LINQ to SQL.

For example, the following shows you how to use the GetAuthors stored procedure in conjunction with a query:

var query = from a in db.GetAuthors()
orderby a.AuthorName
select new { a.AuthorName };
foreach (var book in query) {
  Console.WriteLine(book.AuthorName);
}

Notice that stored procedures can be debugged in the usual way – see below image:

[ Debugging ]

When extracting the stored procedures from your database, all stored procedures that take any arguments will be taken into account when SqlMetal generates the methods associated with those stored procedures. In our scenario we have a stored procedure called GetAuthorByBookId, which takes a single parameter of type int – this leads to the generation of a method of the same name, which also takes a parameter of type int. If you have a stored procedure that takes n arguments then you will also have generated a method with the same n arguments.

For example:

//...
myDataContext.GetAuthorByBookId(8);
//...

In the next section, let's continue by looking at some other LINQ operations that we did not get to cover in this page.

In the previous section, you learned how to use LINQ with SQL stored procedures by looking at a few examples. In this page, let's look at how LINQ works with XML.

LINQ to XML

I have mentioned that LINQ can be used against several data stores, and so far we have only covered in-memory collections and relational data with SQL Server 2005. However, we can also use LINQ with XML data. Dubbed LINQ to XML, LINQ allows us to not only query XML in a truly unique way, but also create XML documents in a very expressive manner. You can find the LINQ extensions for XML in the System.Xml.Linq namespace.

Let’s dive in and create a sample XML document to represent a book and its related data:

XElement book = new XElement("book",new XAttribute("publisher", "MS Press"), new XElement("title", "CLR Via C#"),
new XElement("authors",
new XElement("author", "Jeffrey Richter")));
Console.WriteLine(book);

You can see the result produced by this code in the following image:

[ your XML document recreated ]

One of the things about which I was skeptical at first was the whacky indentation used when defining an XML document using some of the new types like XElement and XAttribute. This indentation is not compulsory, however it is remarkably clear to read when reviewing your code, so I would encourage you to preserve this indentation, if only for code clarity.

Querying XML Data

We will now take the opportunity to look at a few queries of XML data. For all the queries we load an XML document which contains several book elements, using the same definition as outlined in above. Just like any query, the simplest is to drag everything down; we will take the same approach and select all the book elements from the XML document as shown in this snippet:

XElement doc = XElement.Load("Books.xml");
IEnumerable<XElement> query = doc.Descendants("book");
foreach (XElement book in query) {
  Console.WriteLine(book);
}

I’m sure that you will agree with me that it is dead easy to query XML using LINQ – let’s now take the opportunity to look at a query that selects only the books of a certain publisher. Before we actually look at some code we need to come to terms with types in XML documents – there are none! For this reason we are provided with casts for all major value and reference types (int, double, float, string, etc…).

For example, to select only “MS Press” books use:

XElement doc = XElement.Load("Books.xml");
IEnumerable<XElement> query = doc.Descendants("book").Where(x => (string)x.Attribute("publisher") == "MS Press");
foreach (XElement book in query) {
  Console.WriteLine(book);
}

The result of this query can be seen in the following image:

[ the output produced by our query ]

Adding Data to the XML Document

Adding XML to the existing XML document is very simple, we need only construct our XML using a mixture of XElement, and XAttribute types (there are many more you can use other than these two!) and then add them to the document. The following adds a new book:

XElement doc = XElement.Load("Books.xml");
XElement myBook = new XElement("book", new XAttribute("publisher", "Addison-Wesley"), new XElement("title", "Exceptional C++ Style"), new XElement("authors", new XElement("author", "Herb Sutter")));
doc.Add(myBook);
doc.Save("Books.xml");

As with LINQ to SQL, where we have to call the SubmitChanges method of the DataContext to commit the changes to the database, in LINQ to XML, no changes are made to the loaded XML document until that document is saved.

In the previous section, we started exploring how LINQ and XML work with each other. In this page, let's continue by learning how you can use LINQ to make other XML tasks easier.

Updating Data

Updating XML data is also very simple, we just have to pick the element/attribute we wish to update and then set its new value. In this particular example we are just setting the value of the publisher attribute; when dealing with elements there are a host of methods we can use to do a similar operation.

XElement books = XElement.Load(@"Books.xml");
books.Element("book").Attribute("publisher").Value = "MS Press";
books.Save(@"Books.xml");

Deleting Data

Imagine you wake up one morning and suddenly feel the urge to get rid of all books of a particular publisher in your book collection – we will now construct the relevant LINQ query to do this:

XElement doc = XElement.Load("Books.xml");
doc.Descendants("book").Where(x => string)x.Attribute("publisher") == "MS Press").Remove();
doc.Save("Books.xml");

Here we pass a lambda expression in as an argument to the Where extension method. This lambda expression takes one argument (an XElement) and returns a Boolean depending on whether or not the attribute publisher of the book element is equal to MS Press.

Don’t be alarmed by the lambda expression argument to the Where extension method for now – we will cover lambda expressions later on in this article.

LINQ to SQL and LINQ to XML

So far we have looked at hard-coded XML – a great feature of LINQ to SQL is that we can actually generate an XML document directly from a query. The process of creating an XML document dynamically is very easy, and it’s something that I have grown to really appreciate ever since picking up the LINQ May 2006 CTP.

For our first example we will output the title of each book in the database:

BookShop db = new BookShop(_conn);
XElement titles = new XElement("titles", from b in db.GetBooks()
select new XElement("title",
b.Title));
Console.WriteLine(titles);

The code generates an XML document with the structure shown in the following image:

[ the XML document you see when you print out titles ]

Here, instead of creating a new anonymous type like we may do when selecting specific properties of an entity, we create the appropriate XML types – constructing a perfect XML document while using a very small amount of code. I still get excited when I use this particular feature of LINQ to SQL, look at how much code we are writing. The simplicity of it is just great!

One of the concepts introduced in our last code snippet was the ability to actually nest a query within the definition of an XElement type. Our next example builds on this concept and introduces a second nested query so that we can loop through all the authors associated with any particular book:

XElement books = new XElement("books",
from b in db.GetBooks()
select new XElement("book", new XAttribute("publisher",
b.PublisherName),
new XElement("title", b.Title),
new XElement("authors",
from a
in db.GetAuthorByBookId(b.BookId)
select new XElement("author",
a.AuthorName))));
Console.WriteLine(books);

The result of running the above code is shown below:

[ the results from running a nested query ]

Phew! As you can see, using LINQ with XML makes many common tasks, if not more easy, more elegant. In the next section, let's shift gears and talk a little about lambda expressions.

In the previous section, we wrapped our look at how LINQ and XML work. The final topic that I will cover in this page concerns lambda expressions. Read on to find out more!

Lambda Expressions

Many people find lambda expressions fairly hard to get to grips with, so we will take some time to create some basic lambda expressions and then replicate some queries of in-memory collections and relational data using lambda expressions. If you have ever done any functional programming before in a language such as Haskell or F# then you will be familiar with the syntax of lambda expressions – if you have no experience with either, then don’t worry. We will go through lambda expressions now with a series of practical examples.

The first thing to clear up is the basic structure of a lambda expression; this takes the form of args => expression. For the first example we will create a function that returns the square of any given integer argument:

Func<int, int> square = x => x * x;
Console.WriteLine(square(3));

In this example we create a lambda expression that takes one argument (x) of type int, and returns an int. To the left of the => token is our single argument, and to the right of the => token is our expression (x*x).

Let’s now apply what we have just learned to filtering an in-memory collection. Just as before, our lambda expression will take a single argument, but this time return a Boolean value determined by whether or not the argument satisfied the expression:

List<string> people = new List<string>(){ "Granville",
  "Rachel",
  "John",
  "Ross",
"Monica" };
IEnumerable<string> expr = people.Where(x => x.Length > 4);
foreach (string person in expr) {
  Console.WriteLine(person);
}

Here we pass in an inline lambda expression to the Where extension method, in this case the parameter is of type string and the expression returns true only if the string has a length of greater than four characters.

For our final example we will use a lambda expression to select only the AuthorName property of the Author entity instead of creating an anonymous type. First we show how we would do this returning an anonymous type:

BookShop db = new BookShop(_conn);
var authors = from a in db.Authors
select new { a.AuthorName };
foreach (var author in authors) {
  Console.WriteLine(author.AuthorName);
}

Next we perform the same operation using a lambda expression:

BookShop db = new BookShop(_conn);
IEnumerable<string> authors = db.Authors.Select(x => x.AuthorName);
foreach (string author in authors) {
  Console.WriteLine(author);
}

I strongly recommend that you familiarize yourselves with lambda expressions as they are incredibly useful when using LINQ. At the time of writing lambda expressions look set to become the de facto standard, with anonymous delegates playing a more subdued role in the future C# 3.0 language.

Summary

The simplicity and flexibility of LINQ has been shown throughout this article – it truly provides developers with a consistent language to query several data stores via several methods. An important fact to remember is that LINQ is still in early CTP stages, so logically it will only get better! I encourage everyone to further explore what LINQ can do, and more importantly how it can make your life a lot easier.

If you have any questions, please contact me or post on the forums.

Granville Barnett Explorations in Programming

Just a final word before we wrap up. What you've seen here is freshly baked content without added preservatives, artificial intelligence, ads, and algorithm-driven doodads. A huge thank you to all of you who buy kirupa's books, became a paid subscriber, watch the videos, and/or interact on the forums.

Your support keeps this site going! 😇