Friday, September 01, 2006

DLinq queries: Mathematics functions

by via Bill Wagner: C# Development Blog | MySmartChannels on 9/1/2006 2:12:16 PM

Avg, Sum, Min, Max, and T-SQL

This post will cover the Count/Sum/Min/Max/Avg category for DLinq. I’m changing the format because what’s interesting in DLinq isn’t the C# code, it’s how the DLinq library translates the query operators (and your lambda expressions) into SQL.  So, I’m going to concentrate less on the code and its output, and more on what SQL gets generated from your queries.  Unfortunately, I don’t know the exact mechanism LinqToSql uses to translate an expression tree into T-SQL.  But, seeing the inputs and the outputs are instructive.

A simple query on the count from a table:

var q = db.Customers.Count();

Generates this SQL:

SELECT COUNT(*) AS [value]
FROM [Customers] AS [t0]

A Count with a condition:

var q = db.Products.Count(p => !p.Discontinued);

Generates this SQL:

SELECT COUNT(*) AS [value]
FROM [Products] AS [t0]
WHERE NOT ([t0].[Discontinued] = 1)

You can sum a single field:

var q = db.Orders.Select(o => o.Freight).Sum();

Which generates a reasonably simple SQL script:

SELECT SUM([t0].[Freight]) AS [value]
FROM [Orders] AS [t0]

You can write a sum query slightly differently:

var q = db.Products.Sum(p => p.UnitsOnOrder);

Which generates almost the same query:

SELECT SUM([t0].[UnitsOnOrder]) AS [value]
FROM [Products] AS [t0]

Min can be written two different ways as well:

var q = db.Products.Select(p => p.UnitPrice).Min();
var q = db.Orders.Min(o => o.Freight);

Which generates these two (very similar) SQL scripts:

SELECT MIN([t0].[UnitPrice]) AS [value]
FROM [Products] AS [t0]

SELECT MIN([t0].[Freight]) AS [value]
FROM [Orders] AS [t0]

Sure, that stuff was simple.  I could write the SQL procedures as easily as I could write the C# code.  Now, it’s going to get a little more complex.

Look at this query:

var categories =
    from p in db.Products
    group p by p.CategoryID into g
    select new {
        CategoryID = g.Key,
        CheapestProducts =
            from p2 in g
            where p2.UnitPrice == g.Min(p3 => p3.UnitPrice)
            select p2
    };

This builds a list of product categories. Each product category contains two fields: The category ID, and a list of all the products in that category where the price is the cheapest price for that category.

The SQL to perform the query is:

SELECT [t1].[CategoryID], (
    SELECT COUNT(*)
    FROM [Products] AS [t2]
    WHERE ([t2].[UnitPrice] = [t1].[value]) AND 
 ([t1].[CategoryID] = [t2].[CategoryID])
    ) AS [CheapestProducts]
FROM (
    SELECT MIN([t0].[UnitPrice]) AS [value], 
 [t0].[CategoryID]
    FROM [Products] AS [t0]
    GROUP BY [t0].[CategoryID]
    ) AS [t1]
ORDER BY [t1].[CategoryID]

SELECT [t2].[ProductID], [t2].[ProductName], [t2].[SupplierID], 
 [t2].[CategoryID], [t2].[QuantityPerUnit], [t2].[UnitPrice], 
 [t2].[UnitsInStock], [t2].[UnitsOnOrder], [t2].[ReorderLevel], 
 [t2].[Discontinued]
FROM (
    SELECT MIN([t0].[UnitPrice]) AS [value], [t0].[CategoryID]
    FROM [Products] AS [t0]
    GROUP BY [t0].[CategoryID]
    ) AS [t1]
CROSS JOIN [Products] AS [t2]
WHERE ([t2].[UnitPrice] = [t1].[value]) AND 
 ([t1].[CategoryID] = [t2].[CategoryID])
ORDER BY [t1].[CategoryID], [t2].[ProductID]

This shows quite a few of the benefits to LinqToSQL.  First, I don’t like writing T-SQL procedures.  LinqToSql means I don’t have to.  Secondly, most developers I work with would solve this problem by using a few standard queries on the database, and compose the final results in the business layer (or worse, the UI layer).  That results in an inefficient design:  You’ll make more round trips to the database layer, and each layer retrieves more information. LinqToSql composes the SQL script on the fly, preserving only round trip, and transferring only the answers you asked for.  Cool.

[[Editor’s note:  The next three samples essentially replace Min with Max.  So, rather than waste all that bandwidth in all these tubes, I’ll just say that Min and Max are very similar. If you can use one, you can use the other. ]]

[[Another Editor’s note: Avg works the same as either Min or Max.  Simple, huh? ]]

The final Average sample does some interesting computation:

var categories =
    from p in db.Products
    group p by p.CategoryID into g
    select new {
        g.Key, 
        ExpensiveProducts =
            from p2 in g
            where p2.UnitPrice > g.Average(p3 => p3.UnitPrice)
            select p2
    };

This query generates a list of category keys where each category item contains the Category Key, and a list of all products whose unit price is greater than the average unit price for that category.  Here’s the SQL:

SELECT [t1].[CategoryID], (
    SELECT COUNT(*)
    FROM [Products] AS [t2]
    WHERE ([t2].[UnitPrice] > [t1].[value]) AND 
 ([t1].[CategoryID] = [t2].[CategoryID])
    ) AS [ExpensiveProducts]
FROM (
    SELECT AVG([t0].[UnitPrice]) AS [value], 
 [t0].[CategoryID]
    FROM [Products] AS [t0]
    GROUP BY [t0].[CategoryID]
    ) AS [t1]
ORDER BY [t1].[CategoryID]

SELECT [t2].[ProductID], [t2].[ProductName], [t2].[SupplierID], 
 [t2].[CategoryID], [t2].[QuantityPerUnit], [t2].[UnitPrice], 
 [t2].[UnitsInStock], [t2].[UnitsOnOrder], [t2].[ReorderLevel], 
 [t2].[Discontinued]
FROM (
    SELECT AVG([t0].[UnitPrice]) AS [value], [t0].[CategoryID]
    FROM [Products] AS [t0]
    GROUP BY [t0].[CategoryID]
    ) AS [t1]
CROSS JOIN [Products] AS [t2]
WHERE ([t2].[UnitPrice] > [t1].[value]) AND 
 ([t1].[CategoryID] = [t2].[CategoryID])
ORDER BY [t1].[CategoryID], [t2].[ProductID]

As with the above samples, notice that the generated SQL is quite a bit more verbose than the C# 3.0 code.  Your tools have gotten more expressive, and you can get more done in less time.  (And, in my case, you don’t need to worry that your SQL procedure skills just aren’t that great.)