LINQ系列：LINQ to SQL Transact-SQL函数

1. CASE WHEN ... THEN ...

var expr = from p in context.Products            select new             {                 商品ID = p.ProductID,                 商品名称 = p.ProductName,                 是否库存 = p.UnitsInStock > 0 ? "是" : "否"             };

SELECT     [Extent1].[ProductID] AS [ProductID],     [Extent1].[ProductName] AS [ProductName],     CASE WHEN ([Extent1].[UnitsInStock] > 0) THEN N'是' ELSE N'否' END AS [C1]    FROM [dbo].[Product] AS [Extent1]

2. Distinct

var expr = context.Products    .Select(p => p.CategoryID)    .Distinct();

SELECT     [Distinct1].[CategoryID] AS [CategoryID]    FROM ( SELECT DISTINCT         [Extent1].[CategoryID] AS [CategoryID]        FROM [dbo].[Product] AS [Extent1]    )  AS [Distinct1]

3. Count

var expr = context.Products.Count();

SELECT     [GroupBy1].[A1] AS [C1]    FROM ( SELECT         COUNT(1) AS [A1]        FROM [dbo].[Product] AS [Extent1]    )  AS [GroupBy1]

var expr = context.Products    .Count(p => p.UnitPrice > 10m);

SELECT     [GroupBy1].[A1] AS [C1]    FROM ( SELECT         COUNT(1) AS [A1]        FROM [dbo].[Product] AS [Extent1]        WHERE [Extent1].[UnitPrice] > cast(10 as decimal(18))    )  AS [GroupBy1]

4. LongCount

var expr = context.Products.LongCount();

SELECT     [GroupBy1].[A1] AS [C1]    FROM ( SELECT         COUNT_BIG(1) AS [A1]        FROM [dbo].[Product] AS [Extent1]    )  AS [GroupBy1]

var expr = context.Products    .LongCount(p => p.UnitPrice > 10m);

SELECT     [GroupBy1].[A1] AS [C1]    FROM ( SELECT         COUNT_BIG(1) AS [A1]        FROM [dbo].[Product] AS [Extent1]        WHERE [Extent1].[UnitPrice] > cast(10 as decimal(18))    )  AS [GroupBy1]

5. Sum

var expr = context.Products    .Select(p=>p.UnitsInStock)    .Sum();

var expr = context.Products    .Sum(p => p.UnitsInStock);

SELECT     [GroupBy1].[A1] AS [C1]    FROM ( SELECT         SUM([Extent1].[UnitsInStock]) AS [A1]        FROM [dbo].[Product] AS [Extent1]    )  AS [GroupBy1]

6. Min

var expr = context.Products    .Min(p => p.UnitPrice);

var expr = context.Products    .Select(p => p.UnitPrice)    .Min();

SELECT     [GroupBy1].[A1] AS [C1]    FROM ( SELECT         MIN([Extent1].[UnitPrice]) AS [A1]        FROM [dbo].[Product] AS [Extent1]    )  AS [GroupBy1]

　　查找每个类别中单价最低的商品：

var expr = from p in context.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            };

SELECT     [Project1].[CategoryID] AS [CategoryID],     [Project1].[C1] AS [C1],     [Project1].[ProductID] AS [ProductID],     [Project1].[CategoryID1] AS [CategoryID1],     [Project1].[ProductName] AS [ProductName],     [Project1].[UnitPrice] AS [UnitPrice],     [Project1].[UnitsInStock] AS [UnitsInStock],     [Project1].[Discontinued] AS [Discontinued]    FROM ( SELECT         [GroupBy1].[K1] 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             [Extent1].[CategoryID] AS [K1],             MIN([Extent1].[UnitPrice]) AS [A1]            FROM [dbo].[Product] AS [Extent1]            GROUP BY [Extent1].[CategoryID] ) AS [GroupBy1]        LEFT OUTER JOIN [dbo].[Product] AS [Extent2] ON ([GroupBy1].[K1] = [Extent2].[CategoryID]) AND ([Extent2].[UnitPrice] = [GroupBy1].[A1])    )  AS [Project1]    ORDER BY [Project1].[CategoryID] ASC, [Project1].[C1] ASC

7. Max

var expr = context.Products    .Max(p => p.UnitPrice);

var expr = context.Products    .Select(p => p.UnitPrice)    .Max();

SELECT     [GroupBy1].[A1] AS [C1]    FROM ( SELECT         MAX([Extent1].[UnitPrice]) AS [A1]        FROM [dbo].[Product] AS [Extent1]    )  AS [GroupBy1]

8.Avg

var expr = context.Products    .Select(p => p.UnitPrice)    .Average();

var expr = context.Products    .Average(p => p.UnitPrice);

SELECT     [GroupBy1].[A1] AS [C1]    FROM ( SELECT         AVG([Extent1].[UnitPrice]) AS [A1]        FROM [dbo].[Product] AS [Extent1]    )  AS [GroupBy1]

　　查找单个类别中单价高于平均价的产品：

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

SELECT     [Project1].[CategoryID] AS [CategoryID],     [Project1].[C1] AS [C1],     [Project1].[ProductID] AS [ProductID],     [Project1].[CategoryID1] AS [CategoryID1],     [Project1].[ProductName] AS [ProductName],     [Project1].[UnitPrice] AS [UnitPrice],     [Project1].[UnitsInStock] AS [UnitsInStock],     [Project1].[Discontinued] AS [Discontinued]    FROM ( SELECT         [GroupBy1].[K1] 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             [Extent1].[CategoryID] AS [K1],             AVG([Extent1].[UnitPrice]) AS [A1]            FROM [dbo].[Product] AS [Extent1]            GROUP BY [Extent1].[CategoryID] ) AS [GroupBy1]        LEFT OUTER JOIN [dbo].[Product] AS [Extent2] ON ([GroupBy1].[K1] = [Extent2].[CategoryID]) AND ([Extent2].[UnitPrice] > [GroupBy1].[A1])    )  AS [Project1]    ORDER BY [Project1].[CategoryID] ASC, [Project1].[C1] ASC