• 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
  • 相关阅读:
    Servlet中isCommitted含义及发生的条件
    戏说java web开发中的listener和filter
    Android中以JAR形式封装控件或者类库
    华为总裁在“2012实验室干部与专家座谈会”上的发言!(转)
    C#进行Visio二次开发
    我是谁,从哪里来,将要到哪里去?
    LTE相关网元功能
    走出柏拉图的“洞穴”
    LTE的协议结构
    被联通欠款拖死,被移动集采玩死
  • 原文地址:https://www.cnblogs.com/libingql/p/4049400.html
Copyright © 2020-2023  润新知