LINQ系列：LINQ to SQL Group by/Having分组

1. 简单形式

var expr = from p in context.Products
           group p by p.CategoryID into g
           select g;

foreach (var item in expr)
{
    Console.WriteLine(item.Key);

    foreach (var p in item)
    {
        Console.WriteLine("{0}-{1}", p.ProductID, p.ProductName);
    }
}

SELECT 
    [Project2].[CategoryID] AS [CategoryID], 
    [Project2].[C1] AS [C1], 
    [Project2].[ProductID] AS [ProductID], 
    [Project2].[CategoryID1] AS [CategoryID1], 
    [Project2].[ProductName] AS [ProductName], 
    [Project2].[UnitPrice] AS [UnitPrice], 
    [Project2].[UnitsInStock] AS [UnitsInStock], 
    [Project2].[Discontinued] AS [Discontinued]
    FROM ( SELECT 
        [Distinct1].[CategoryID] AS [CategoryID], 
        [Extent2].[ProductID] AS [ProductID], 
        [Extent2].[CategoryID] AS [CategoryID1], 
        [Extent2].[ProductName] AS [ProductName], 
        [Extent2].[UnitPrice] AS [UnitPrice], 
        [Extent2].[UnitsInStock] AS [UnitsInStock], 
        [Extent2].[Discontinued] AS [Discontinued], 
        CASE WHEN ([Extent2].[ProductID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM   (SELECT DISTINCT 
            [Extent1].[CategoryID] AS [CategoryID]
            FROM [dbo].[Product] AS [Extent1] ) AS [Distinct1]
        LEFT OUTER JOIN [dbo].[Product] AS [Extent2] ON [Distinct1].[CategoryID] = [Extent2].[CategoryID]
    )  AS [Project2]
    ORDER BY [Project2].[CategoryID] ASC, [Project2].[C1] ASC

2. 最大值

var expr = from p in context.Products
            group p by p.CategoryID into g
            select new
            {
                g.Key,
                MaxUnitPrice = g.Max(p => p.UnitPrice)
            };

foreach (var item in expr)
{
    Console.WriteLine("{0}-{1}", item.Key, item.MaxUnitPrice);
}

SELECT 
    [GroupBy1].[K1] AS [CategoryID], 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        [Extent1].[CategoryID] AS [K1], 
        MAX([Extent1].[UnitPrice]) AS [A1]
        FROM [dbo].[Product] AS [Extent1]
        GROUP BY [Extent1].[CategoryID]
    )  AS [GroupBy1]

3. 最小值

var expr = from p in context.Products
            group p by p.CategoryID into g
            select new
            {
                g.Key,
                MinUnitPrice = g.Min(p => p.UnitPrice)
            };

SELECT 
    [GroupBy1].[K1] AS [CategoryID], 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        [Extent1].[CategoryID] AS [K1], 
        MIN([Extent1].[UnitPrice]) AS [A1]
        FROM [dbo].[Product] AS [Extent1]
        GROUP BY [Extent1].[CategoryID]
    )  AS [GroupBy1]

4. 平均值

var expr = from p in context.Products
            group p by p.CategoryID into g
            select new
            {
                g.Key,
                AverageUnitPrice = g.Average(p => p.UnitPrice)
            };

SELECT 
    [GroupBy1].[K1] AS [CategoryID], 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        [Extent1].[CategoryID] AS [K1], 
        AVG([Extent1].[UnitPrice]) AS [A1]
        FROM [dbo].[Product] AS [Extent1]
        GROUP BY [Extent1].[CategoryID]
    )  AS [GroupBy1]

5. 求和

var expr = from p in context.Products
            group p by p.CategoryID into g
            select new
            {
                g.Key,
                TotalUnitPrice = g.Sum(p => p.UnitPrice)
            };

SELECT 
    [GroupBy1].[K1] AS [CategoryID], 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        [Extent1].[CategoryID] AS [K1], 
        SUM([Extent1].[UnitPrice]) AS [A1]
        FROM [dbo].[Product] AS [Extent1]
        GROUP BY [Extent1].[CategoryID]
    )  AS [GroupBy1]

6. 计数

var expr = from p in context.Products
            group p by p.CategoryID into g
            select new
            {
                g.Key,
                ProductNumber = g.Count()
            };

SELECT 
    [GroupBy1].[K1] AS [CategoryID], 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        [Extent1].[CategoryID] AS [K1], 
        COUNT(1) AS [A1]
        FROM [dbo].[Product] AS [Extent1]
        GROUP BY [Extent1].[CategoryID]
    )  AS [GroupBy1]

var expr = from p in context.Products
            group p by p.CategoryID into g
            select new
            {
                g.Key,
                ProductNumber = g.Count(p => p.UnitsInStock > 0)
            };