Traditional Queries
Before getting into the features of LINQ, let’s have a look how queries are done today. To demonstrate this, queries are done across a list of objects, in the database and inside XML data. With all data sources, a list of Formula 1 world champions is searched. The query could be, for example, to get all Formula 1 world champions from Brazil in the order of races won.
Query in Object Lists
The first variant of a traditional query is to search data in an object list. Before the search can start, the object type and the list must be prepared.
For the object, the type Racer is defined. Racer defines several properties, constructors to initialize the properties, and an overloaded ToString() method to display a racer in a string format. This class implements the interface IFormattable to support different variants of format strings, and the interface IComparable<Racer>, which can be used to sort a list of racers based on the Lastname.
using System; using System.Text; namespace Wrox.ProCSharp.LINQ { [Serializable] public class Racer : IComparable<Racer>, IFormattable { public Racer(string firstname, string lastname, string country, int starts, int wins) { this.firstname = firstname; this.lastname = lastname; this.country = country; this.starts = starts; this.wins = wins; } public Racer(string firstname, string lastname, string country, int starts) : this(firstname, lastname, country, starts, 0) { } public Racer(string firstname, string lastname, string country) : this(firstname, lastname, country, 0) { } public Racer(string firstname, string lastname) : this(firstname, lastname, "unknown") { } private string firstname; public string Firstname { get { return firstname; } set { firstname = value; } } private string lastname; public string Lastname { get { return lastname; } set { lastname = value; } } private int wins; public int Wins { get { return wins; } set { wins = value; } } private int starts; public int Starts { get { return starts; } set { starts = value; } } private string country; public string Country { get { return country; } set { country = value; } } public override string ToString() { return firstname + " " + lastname; } public int CompareTo(Racer other) { return this.lastname.CompareTo(other.lastname); } public string ToString(string format) { return ToString(format, null); } public string ToString(string format, IFormatProvider formatProvider) { switch (format) { case null: case "N": return ToString(); case "F": return firstname; case "L": return lastname; case "A": StringBuilder sb = new StringBuilder(); sb.AppendFormat("{0} {1}, {2}; starts: {3}, wins: {4}", firstname, lastname, country, starts, wins); return sb.ToString(); default: throw new FormatException(String.Format("Format {0} not supported", format)); } } } } The class Formula 1 returns a list of racers in the method GetRacers(). The list is filled with all Formula 1 champions in the years 1950 to 2006.
using System; using System.Collections.Generic; namespace Wrox.ProCSharp.LINQ { public static class Formula1 { public static IList<Racer> GetRacers() { List<Racer> racers = new List<Racer>(40); racers.Add(new Racer("Nino", "Farina", "Italy", 33, 5)); racers.Add(new Racer("Alberto", "Ascari", "Italy", 32, 10)); racers.Add(new Racer("Juan Manuel", "Fangio", "Argentina", 51, 24)); racers.Add(new Racer("Mike", "Hawthorn", "England", 45, 3)); racers.Add(new Racer("Phil", "Hill", "USA", 48, 3)); racers.Add(new Racer("John", "Surtees", "England", 111, 6)); racers.Add(new Racer("Jim", "Clark", "Scotland",72, 25)); racers.Add(new Racer("Jack", "Brabham", "Australia", 125, 14)); racers.Add(new Racer("Denny", "Hulme", "New Zealand", 112, 8)); racers.Add(new Racer("Graham", "Hill", "England", 176, 14)); racers.Add(new Racer("Jochen", "Rindt", "Austria", 60, 6)); racers.Add(new Racer("Jackie", "Stewart", "Scotland", 99, 27)); racers.Add(new Racer("Emerson", "Fittipaldi", "Brazil", 143, 14)); racers.Add(new Racer("James", "Hunt", "England", 91, 10)); racers.Add(new Racer("Mario", "Andretti", "USA", 128, 12)); racers.Add(new Racer("Jody", "Scheckter", "South Africa", 112, 10)); racers.Add(new Racer("Alan", "Jones", "Australia", 115, 12)); racers.Add(new Racer("Keke", "Rosberg", "Finland", 114, 5)); racers.Add(new Racer("Niki", "Lauda", "Austria", 170, 25)); racers.Add(new Racer("Nelson", "Piquet", "Brazil", 204, 23)); racers.Add(new Racer("Ayrton", "Senna", "Brazil", 161, 41)); racers.Add(new Racer("Nigel", "Mansell", "England", 187, 31)); racers.Add(new Racer("Alain", "Prost", "France", 197, 51)); racers.Add(new Racer("Damon", "Hill", "England", 114, 22)); racers.Add(new Racer("Jacques", "Villeneuve", "Canada", 165, 11)); racers.Add(new Racer("Mika", "Hakkinen", "Finland", 160, 20)); racers.Add(new Racer("Michael", "Schumacher", "Germany", 250, 91)); racers.Add(new Racer("Fernando", "Alonso", "Spain", 88, 15)); return racers; } } } Now let’s get into the heart of the object query. First, you need to get the list of objects with the static method GetRacers(). The list is filled into the generic class List<T>. The FindAll() method of this class accepts a Predicate<T> delegate that can be implemented as anonymous method. Only the racers where the Country property is set to Brazil should be returned. Next, the resulting list is sorted with the Sort() method. The sort should not be done by the Lastname property as is the default sort implementation of the Racer class, but you can pass a delegate of type Comparison<T>. It is again implemented as an anonymous method to compare the number of wins. Using the r2 object and comparing it with r1 does a descending sort as is required. The foreach statement finally iterates through all Racer objects in the resulting sorted collection.
private static void ObjectQuery() { List<Racer> racers = new List<Racer>(Formula1.GetRacers()); List<Racer> brazilRacers = racers.FindAll( delegate(Racer r) { return r.Country == "Brazil"; }); brazilRacers.Sort( delegate(Racer r1, Racer r2) { return r2.Wins.CompareTo(r1.Wins); }); foreach (Racer r in brazilRacers) { Console.WriteLine("{0:A}", r); } } The list displayed shows all champions from Brazil, sorted by the number of wins:
Ayrton Senna, Brazil; starts: 161, wins: 41 Nelson Piquet, Brazil; starts: 204, wins: 23 Emerson Fittipaldi, Brazil; starts: 143, wins: 14
| Tip | Sorting and filtering object lists is discussed in Chapter 10, “Collections.” |
Database Query
Now let’s do the same with a database query. The database Formula1 contains the table Racers, as shown in Figure 45-1, with the same columns as before with the Racers class. The table contains the same data that was returned by the Formula1 class shown earlier.
Figure 45-1
To access the database, various classes from the namespace System.Data.SqlClient are needed. First, a connection is opened with the SqlConnection class. The SqlCommand class defines the SQL statement. And here the query is defined. With the SELECT statement the columns of the table are defined to be used by the query. The WHERE clause defines the filter: only rows where the column Country has a value set to Brazil should be returned. ORDER BY defines the sort order. After the connection is opened, the data is read with the help of the SqlDataReader class, which reads row by row. Every selected column is written to the console. The result is exactly the same as before.
private static void SqlQuery() { SqlConnection connection = new SqlConnection( "server=(local);database=Formula1;trusted_connection=true"); SqlCommand command = connection.CreateCommand(); command.CommandText = "SELECT Firstname, Lastname, Country, Starts, Wins " + "FROM Racers WHERE Country = @Country ORDER BY Wins DESC"; command.Parameters.AddWithValue("@Country", "Brazil"); connection.Open(); SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); try { while (reader.Read()) { Console.WriteLine("{0} {1}, {2}; starts: {3}, wins: {4}", reader["Firstname"], reader["Lastname"], reader["Country"], reader["Starts"], reader["Wins"]); } } finally { reader.Close(); } } | Tip | Sorting and filtering with relational data is discussed in Chapter 25, “Data Access with .NET.” |
XML
The same data has been used with the object list, and the database is now stored in an XML document Racers.xml. Part of the content with the first three racers is shown here. A racer is defined with the XML element <Racer>, which has several children elements for the first name, last name, country, number of starts, and wins.
<?xml version="1.0" encoding="utf-8" ?> <Racers> <Racer> <Firstname>Nino</Firstname> <Lastname>Farina</Lastname> <Country>Italy</Country> <Starts>33</Starts> <Wins>5</Wins> </Racer> <Racer> <Firstname>Alberto</Firstname> <Lastname>Ascari</Lastname> <Country>Italy</Country> <Starts>32</Starts> <Wins>10</Wins> </Racer> <Racer> <Firstname>Juan Manuel</Firstname> <Lastname>Fangio</Lastname> <Country>Argentina</Country> <Starts>51</Starts> <Wins>24</Wins> </Racer> <Racer> <!-- ... --> </Racers> To filter the <Racers> element an XPath expression can be used. The XPath expression /Racers/ Racer[Country = ‘Brazil’] returns all Racer elements where the child element Country has a value set to Brazil. XPath doesn’t offer sorting.
One way the sort can be done is by using an XmlDocument with the XPath expression to get all racers from Brazil, and then sort the resulting object list with the help of a collection class. To do a filter and sort with XML syntax, XSLT can be used.
The XSLT document Racers.xslt defines two templates. The first template does a match to the root element, which is <Racers>. For the root element, a <Racers> element is returned. The <xsl: apply-templates> element directs the XSLT processor to find all Racer elements where the child element Country has a value of Brazil. The select attribute requires an XPath expression. The sort is defined by the <xsl:sort> child element. The <Wins> child element of Racer is selected, the order set to descending, and the data type is a number.
The second template in this XSLT document is already processed just for the filtered racer elements in the correct order. For every racer a Racer element is returned.
<?xml version="1.0" encoding="UTF-8" ?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/"> <Racers> <xsl:apply-templates select="/Racers/Racer[Country = 'Brazil']"> <xsl:sort data-type="number" select="Wins" order="descending"/> </xsl:apply-templates> </Racers> </xsl:template> <xsl:template match="Racer"> <Racer> <Firstname> <xsl:value-of select="Firstname"/> </Firstname> <Lastname> <xsl:value-of select="Lastname"/> </Lastname> <Country> <xsl:value-of select="Country"/> </Country> <Starts> <xsl:value-of select="Starts"/> </Starts> <Wins> <xsl:value-of select="Wins"/> </Wins> </Racer> </xsl:template> </xsl:stylesheet> Using the XML and XSLT documents from C# code is done with the help of the XSLT processor class XslCompiledTransform from the namespace System.Xml.Xsl. The Racers.xslt document is loaded, and then the XSLT is used to transform the source file Racers.xml. The output is written to an XmlTextWriter that has its store in a MemoryStream. For reading the XML data returned into the memory stream, the stream is positioned to the begin for reading it again with the XmlDocument. The XmlDocument is used to select the filtered and sorted <Racer> elements. In a foreach loop all racers from the resulting XmlNodeList are iterated and displayed on the console. The result is again the same as with the previous two examples.
private static void XmlQuery() { MemoryStream stream = new MemoryStream(); XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8); XslCompiledTransform xslt = new XslCompiledTransform(); xslt.Load("../../Racers.xslt"); Never use a path like the following. Should have a virtual root defined. xslt.Transform("../../Racers.xml", writer); stream.Seek(0, SeekOrigin.Begin); XmlDocument doc = new XmlDocument(); doc.Load(stream); writer.Close(); XmlNodeList racers = doc.SelectNodes("//Racer"); foreach (XmlNode racer in racers) { Console.WriteLine("{0} {1}, {2}; starts: {3}, wins: {4}", racer["Firstname"].InnerText, racer["Lastname"].InnerText, racer["Country"].InnerText, racer["Starts"].InnerText, racer["Wins"].InnerText); } | Tip | Sorting and filtering XML data is discussed in Chapter 26, “Manipulating XML.” |
Now you’ve seen how sorting and filtering is done with object lists, relational data, and XML. Every one of these technologies has a different syntax for filtering and sorting. Now let’s look at how LINQ can be used to filter and sort the same data.
