注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

午夜阳光

 
 
 

日志

 
 

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

2015-01-15 12:00:45|  分类: Linq |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

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
复制代码
  评论这张
 
阅读(81)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017