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