Linq到SQL嵌套选择组由计数 [英] Linq to SQL Nested Select Group by Having count

问题描述

我需要从sql转换查询到linq
创建表历史

I need convert query from sql to linq Create Table History

CREATE TABLE [dbo].[History](
    [HistoryID] [int] IDENTITY(1,1) NOT NULL,
    [AuctionID] [int] NOT NULL,
    [UserName] [nvarchar](128) NULL,
    [Time] [datetime] NOT NULL,
    [Price] [decimal](18, 2) NOT NULL,
)

查询

select [UserName],[Price] from [History] 
where [Price] in 
    (SELECT [Price] FROM [History]  
    where ID=28 GROUP BY [Price]
    HAVING COUNT(*)=1) 
        Order by [Price]

推荐答案

寻找与您的SP类似的 LINQ 语句。使用下面的一个

I think you are looking for LINQ statement similar to your SP. Use below one

// Using Plain LINQ statements
var result1 = from history in lstHistory
              where history.ID == 28
              group history by history.Price into g
              orderby g.Key
              where g.Count() == 1
              select new 
                { 
                    Price = g.Key, 
                    UserName = g.Select(h => h.UserName).FirstOrDefault() 
                };

或

// Using Lambda Expressions
var result2 = lstHistory
                .Where(q => q.ID == 28)
                .OrderBy(t => t.Price)
                .GroupBy(h => h.Price)
                .Where(grp => grp.Count() == 1)
                .Select(g => new 
                           { 
                              Price = g.Key, 
                              UserName = g.Select(h => h.UserName).FirstOrDefault() 
                            });

这篇关于Linq到SQL嵌套选择组由计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！