Section 5.2. LINQ to SQL


5.2. LINQ to SQL

Instead of executing our queries against a DataSet, suppose we want to execute against the database directly? No problem. With LINQ to SQL, we change only the target of our query:

             Databases.SchedulingDocs db = new Databases.SchedulingDocs(connectionInfo); var oct2006 = ( // find all doctors scheduled for October 2006:                 from d in db.Doctors                 join c in db.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(); 

In this case, SchedulingDocs is a class denoting a SQL Server 2005 database named SchedulingDocs. This class, and its associated entity classesD o ctors, Calls, and Vacationswere automatically generated by LINQ's SQLMetal tool to represent the database and its tables. As you would expect, the query expression is lazily evaluated, waiting for the query to be consumed:

             foreach (var initials in oct2006)  // execute the query:   System.Console.WriteLine("{0}", initials); 

The query is now translated into parameterized SQL, sent to the database for execution, and the result set produced. How efficient is the generated SQL? In this case (and the May CTP of LINQ), a single select statement is executed against the database:

 SELECT DISTINCT [t0].[Initials]   FROM [Doctors] AS [t0], [Calls] AS [t1]   WHERE ([t1].[DateOfCall] >= @p0) AND         ([t1].[DateOfCall] <= @p1) AND         ([t0].[Initials] = [t1].[Initials])   ORDER BY [t0].[Initials] 

NOTE

In the May CTP of LINQ, SQLMetal is provided as a command-line tool. To run, first open a command window and cd to the install directory for LINQ (most likely C:\Program Files\LINQ Preview\Bin). Now ask SQLMetal to read the metadata from your database and generate the necessary class files. For the SQL Server 2005 database named SchedulingDocs, the command is:

   C:\...\Bin>sqlmetal /server:. /database:SchedulingDocs /language:csharp /code:SchedulingDocs.cs /namespace:Databases 

This will generate a source code file SchedulingDocs.cs in the current directory.

Let's look at a more complex query that computes the number of calls for every doctor in the month of October 2006. Recall that an inner join produces results for only those doctors that are working:

 var oct2006 = from d in db.Doctors                join c in db.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 new { Initials = g.Key, Count = g.Count() }; 

An outer join is needed to capture the results for all doctors, whether scheduled or not. Outer joins are based on LINQ's join ... into syntax:

 var allOct2006 = from d1 in db.Doctors       // join all doctors                  join d2 in oct2006          // with those working in Oct 2006                  on d1.Initials equals d2.Initials                  into j                  from r in j.DefaultIfEmpty()                  select new { Initials = d1.Initials,                               Count = (r == null ? 0 : r.Count) }; 

This left outer join produces a result set "into" j, which is then enumerated across using the sub-expression "from r in ...". If a given doctor is working, then r is the joined result; if the doctor is not working then the result is empty, in which case j.DefaultIfEmpty() returns null. For each doctor, we then project their initials and the number of calls they are workingeither 0 or the count from the inner join. Iterating across the query:

 foreach (var result in allOct2006)        System.Console.WriteLine("{0}: {1}", result.Initials, result.Count); 

Yields:

 ay: 7 bb: 0 ch: 3 . . . 

When the query is executed, the following SQL is sent to the database (this is a test intended for the SQL wizards in the audience):

 SELECT [t7].[Initials], [t7].[value] AS [Count] FROM (     SELECT         (CASE             WHEN [t4].[test] IS NULL THEN 0               ELSE (                 SELECT COUNT(*)                 FROM [Doctors] AS [t5], [Calls] AS [t6]                 WHERE ([t4].[Initials] = [t5].[Initials]) AND                       ([t6].[DateOfCall] >= @p0) AND                       ([t6].[DateOfCall] <= @p1) AND                       ([t5].[Initials] = [t6].[Initials])               )         END) AS [value], [t0].[Initials]     FROM [Doctors] AS [t0]     LEFT OUTER JOIN (         SELECT 1 AS [test], [t3].[Initials]         FROM (             SELECT [t1].[Initials]             FROM [Doctors] AS [t1], [Calls] AS [t2]             WHERE ([t2].[DateOfCall] >= @p0) AND                   ([t2].[DateOfCall] <= @p1) AND                   ([t1].[Initials] = [t2].[Initials])             GROUP BY [t1].[Initials]         ) AS [t3]     ) AS [t4] ON [t0].[Initials] = [t4].[Initials] ) AS [t7] 

NOTE

In all fairness, it should be noted that the focus of this Short Cut is LINQ, and not particularly LINQ to SQL. For this reason, the picture painted of LINQ to SQL is quite superficial. For example, in LINQ to SQL, queries are translated to SQL and executed with SQL semantics. In comparison, most other LINQ queries are directly executed by .NET Framework objects with CLR semantics. Likewise, LINQ to SQL handles changes to the data quite differently than other flavors of LINQ. For more details, we encourage you to read the forthcoming Part 2 of this Short Cut series on LINQ (expected fall 2006), which focuses exclusively on LINQ to SQL. Watch for an announcement at http://oreilly.com



LINQ[c] The Future of Data Access in C# 3. 0
LINQ[c] The Future of Data Access in C# 3. 0
ISBN: N/A
EAN: N/A
Year: 2006
Pages: 25

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net