Putting It All Together

I wanted the examples in the previous chapter to be easy for someone trying to learn how to query with the LINQ to DataSet API. I wanted the time you spend working with any examples to be focused on LINQ. I didn't want you to have to struggle with getting a database or getting your connection string correct. But, before we leave this chapter, I want to provide a more complete example—one that is actually getting a DataSet from a database because this is most likely how you will obtain a DataSet in your real-life code.

I must admit that creating a reasonable-size example that gets data from a database and uses the LINQ to DataSet API to any degree feels very contrived. After all, I am going to perform a SQL query on data in a database using ADO.NET to obtain a DataSet, then turn right around and query that data again using LINQ to DataSet, all within several lines of code. In real life, some would ask, why not just change the SQL query to get exactly what you need in the first place? To them I say, play along! What I need here is a scenario to explain away the silliness.

In my scenario, I work for a company named Northwind. If ever there was a less than subtle hint at the database I will be using, that was it. My company has an already existing application that queries our database for orders. This particular application performs various analyses on which employees sold items to which customers, and to what countries the orders were shipped. So the application is already downloading the employees, customers, and shipping countries for all orders into a DataSet. My task is to perform one more analysis on that already queried data. I am required to produce a unique list of each employee who sold to each company for all orders that were shipped to Germany.

In this example, I instantiate a SqlDataAdapter followed by a DataSet and call the SqlDataAdapter object's Fill method to populate the DataSet. In this scenario, this would have already been done because this existing application is already doing it. So the DataSet object would be passed into my code. But since I don't have a full-blown application, I will just do it in the example. After I obtain the DataSet object with the results of the SQL query, all I have to do for my task is perform a LINQ to DataSet query and display the results. Listing 11-2 is the code.

Example. Putting It All Together

string connectionString =
  @"Data Source=.SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI;";

SqlDataAdapter dataAdapter = new SqlDataAdapter(
  @"SELECT O.EmployeeID, E.FirstName + ' ' + E.LastName as EmployeeName,
    O.CustomerID, C.CompanyName, O.ShipCountry
    FROM Orders O
    JOIN Employees E on O.EmployeeID = E.EmployeeID
    JOIN Customers C on O.CustomerID = C.CustomerID",
  connectionString);

DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet, "EmpCustShip");

//  All code prior to this comment is legacy code.

var ordersQuery = dataSet.Tables["EmpCustShip"].AsEnumerable()
  .Where(r => r.Field<string>("ShipCountry").Equals("Germany"))
  .Distinct(System.Data.DataRowComparer.Default)
  .OrderBy(r => r.Field<string>("EmployeeName"))
  .ThenBy(r => r.Field<string>("CompanyName"));

foreach(var dataRow in ordersQuery)
{
  Console.WriteLine("{0,-20} {1,-20}", dataRow.Field<string>("EmployeeName"),
    dataRow.Field<string>("CompanyName"));
}

As you can see, I am connecting to the Northwind database. You may need to tweak the connection string for your needs.

Notice that in the previous query, I use the AsEnumerable, Distinct, and Field<T> operators I covered in the previous chapter and the Where, OrderBy, and ThenBy operators from the LINQ to Objects API together to create the exact query I want. You really have to admire the way this stuff all plays together so nicely. If the query is doing what I need it to do, I should get a list of each employee who sold an order to each company where that order was shipped to Germany in alphabetical order by employee name and company name, and with no duplicate rows. Here are the results:

Andrew Fuller        Die Wandernde Kuh
Andrew Fuller        Königlich Essen
Andrew Fuller        Lehmanns Marktstand
Andrew Fuller        Morgenstern Gesundkost
Andrew Fuller        Ottilies Käseladen
Andrew Fuller        QUICK-Stop
Andrew Fuller        Toms Spezialitäten
Anne Dodsworth       Blauer See Delikatessen
Anne Dodsworth       Königlich Essen
Anne Dodsworth       Lehmanns Marktstand
Anne Dodsworth       QUICK-Stop
...
Steven Buchanan      Frankenversand
Steven Buchanan      Morgenstern Gesundkost
Steven Buchanan      QUICK-Stop

Notice that for each employee on the left, no company is repeated on the right. This is important because it is once again demonstrating the necessity of the LINQ to DataSet API set-type operators. As a test, change the call to the Distinct operator in the previous code so that the DataRowComparer.Default comparer is not specified, and you will see that you get duplicates.

Just so you can see another example using query expression syntax, Listing 11-3 is the same example again, but with the aforementioned syntax.

Example. Putting It All Together with Query Expression Syntax

string connectionString =
  @"Data Source=.SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI;";

SqlDataAdapter dataAdapter = new SqlDataAdapter(
  @"SELECT O.EmployeeID, E.FirstName + ' ' + E.LastName as EmployeeName,
    O.CustomerID, C.CompanyName, O.ShipCountry
    FROM Orders O
    JOIN Employees E on O.EmployeeID = E.EmployeeID
    JOIN Customers C on O.CustomerID = C.CustomerID",
  connectionString);

DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet, "EmpCustShip");

//  All code prior to this comment is legacy code.

var ordersQuery = (from r in dataSet.Tables["EmpCustShip"].AsEnumerable()
                   where r.Field<string>("ShipCountry").Equals("Germany")
                   orderby r.Field<string>("EmployeeName"),
                     r.Field<string>("CompanyName")
                   select r)
                  .Distinct(System.Data.DataRowComparer.Default);

foreach (var dataRow in ordersQuery)
{
  Console.WriteLine("{0,-20} {1,-20}", dataRow.Field<string>("EmployeeName"),
    dataRow.Field<string>("CompanyName"));
}

Now the query is using query expression syntax. While it was my goal to make the query functionally the same as the previous, I was not able to do this. Notice that the Distinct operator is called at the very end of the query now. Remember, the compiler cannot translate all operators from a query specified with query expression syntax, only the most commonly used ones. In this case, it does not know how to translate the Distinct operator. Because of this, I cannot make that call in the query expression syntax portion of the query. As you can see, I did call it at the end of the query. I will end up with the same results from this query.

However, there is a performance difference between the query in Listing 11-3 and the query in Listing 11-2. In Listing 11-2, the Distinct operator is called just after the Where operator, so duplicate records are eliminated from the results set prior to ordering them. In Listing 11-3, the Distinct operator is not called until the end, so the duplicate records are still there during the ordering of the results set. This means records are being sorted that will be eliminated once the Distinct operator is called. This is unnecessary work, but unavoidable if you wish to use query expression syntax for this query.