LINQ to SQL Transact-SQL函数的用法

1. CASE WHEN ... THEN ...

linq用法：

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

sql用法：

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的用法

linq用法：

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

sql用法：

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

3. Count的用法

linq用法：

var expr = context.Products.Count();

sql用法：

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