• 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
  • 相关阅读:
    无责任Windows Azure SDK .NET开发入门篇二[使用Azure AD 进行身份验证]
    无责任Windows Azure SDK .NET开发入门篇一[Windows Azure开发前准备工作]
    了解ASP.NET5 Web应用程序结构
    Hello ASP.NET5
    CentOS7 防火墙 firewall-cmd
    C# 中使用WebClient 请求 https
    使用 gridfs-stream 存储文件遇到的一个坑。
    overflow的几个坑
    IIS7启用静态压缩
    创建高性能移动 web 站点【转载】
  • 原文地址:https://www.cnblogs.com/libingql/p/4049400.html
Copyright © 2020-2023  润新知