Section 5.1. LINQ to DataSets

5.1. LINQ to DataSets

LINQ supports the querying of both typed and untyped DataSets. Expanding on our theme of a hospital scheduling application, suppose we have a namespace DataSets with a typed dataset SchedulingDocs containing three tables: Doctors, Calls, and Vacations. The Doctors table contains one record for each doctor, the Calls table keeps track of which doctor is on call each day, and the Vacations table makes note of vacation requests. The DataSet is summarized in Figure 2.

Figure 5-1. Tables in the SchedulingDocs typed DataSet

Let's assume an instance of SchedulingDocs has been created and filled:

 DataSets.SchedulingDocs ds = new DataSets.SchedulingDocs();  // create dataset . .  // open connection to a database and fill each table? . 

To find all the doctors living within Chicago, the query is exactly as we've seen before:

 var chicago = from d in ds.Doctors               where d.City == "Chicago"               select d; 

In this case ds.Doctors denotes a DataTable object, and d represents a DataRow object. Since the DataSet is typed, these objects are strongly-typed as DoctorsTable and DoctorsRow, respectively.

NOTE

For completeness, here's the code to create and fill an instance of SchedulingDocs, declared within the DataSets namespace:

 using System.Data.Common; using DataSets; SchedulingDocsTableAdapters.DoctorsTableAdapter    doctorsAdapter; SchedulingDocsTableAdapters.CallsTableAdapter      callsAdapter; SchedulingDocsTableAdapters.VacationsTableAdapter  vacationsAdapter; SchedulingDocs     ds; DbProviderFactory  dbFactory; dbFactory        = DbProviderFactories.GetFactory(providerInfo); ds               = new SchedulingDocs(); doctorsAdapter   = new SchedulingDocsTableAdapters.DoctorsTableAdapter(); callsAdapter     = new SchedulingDocsTableAdapters.CallsTableAdapter(); vacationsAdapter = new SchedulingDocsTableAdapters.VacationsTableAdapter(); using (DbConnection dbConn = dbFactory.CreateConnection()) {   dbConn.ConnectionString = connectionInfo;   dbConn.Open();   doctorsAdapter.Fill(ds.Doctors);   callsAdapter.Fill(ds.Calls);   vacationsAdapter.Fill(ds.Vacations); } //dbConn.Close(); 

LINQ supports the notion of joins, including inner and outer joins. For example, let's join the Doctors and Calls tables to see which doctors are scheduled in the month of October 2006:

 var oct2006 = (                 from d in ds.Doctors                 join c in ds.Calls                 on d.Initials equals c.Initials                 where c.DateOfCall >= new DateTime(2006, 10, 1) &&                       c.DateOfCall <= new DateTime(2006, 10, 31)                 orderby d.Initials                 select d.Initials                )               .Distinct(); 

This query expression uses a number of standard LINQ query operators, including Join, OrderBy, and Distinct; Join implements an inner equijoin.

LINQ is not limited to yielding tabular data, but will produce hierarchical results as appropriate. For example, suppose we want to know not only which doctors are on call in October 2006, but also the dates. In this case, we join the Doctors and Calls tables, now grouping the results:

 var oct2006 = from d in ds.Doctors                join c in ds.Calls                on d.Initials equals c.Initials                where c.DateOfCall >= new DateTime(2006, 10, 1) &&                      c.DateOfCall <= new DateTime(2006, 10, 31)                group c by d.Initials into g                select g; 

Notice we group the calls ("c") on a per doctor basis ("d"). For each scheduled doctor, this yields an enumerable collection of calls. Here's how we process the query:

             foreach (var group in oct2006) {   System.Console.WriteLine("{0}: ", group.Key);   foreach (var call in group)      System.Console.WriteLine("  {0}", call.DateOfCall.ToString("dd MMM yyyy"));   System.Console.WriteLine("  calls = {0}", group.Count()); } 

The hierarchical output appears as follows:

 jl:   02 Oct 2006   calls = 1 mbl:   01 Oct 2006   31 Oct 2006   calls = 2 . . . 

How about we re-order the results by those working the most, and select just the data we need in the result set:

 var oct2006 = from d in ds.Doctors                join c in ds.Calls                on d.Initials equals c.Initials                where c.DateOfCall >= new DateTime(2006, 10, 1) &&                      c.DateOfCall <= new DateTime(2006, 10, 31)                group c by d.Initials into g                orderby g.Count() descending                select new { Initials = g.Key,                             Count = g.Count(),                             Dates = from c in g                                     select c.DateOfCall }; foreach (var result in oct2006) {   System.Console.WriteLine("{0}:", result.Initials);   foreach (var date in result.Dates)      System.Console.WriteLine("  {0}", date.ToString("dd MMM yyyy"));   System.Console.WriteLine("  calls = {0}", result.Count); } 

By projecting just the needed data, we can do things like data-bind the result for ease of display. The trade-off is that this potentially requires another set of custom objects to be instantiated. However, keep in mind that the design of LINQ enables the query operators to optimize away unnecessary object creation, much like compilers routinely eliminate unneeded code. This is especially true when applying LINQ in other situations, such as against a database (i.e., "LINQ to SQL").