使用组by将SQL查询转换为LINQ

本文关键字：查询 转换 LINQ SQL by | 更新日期: 2023-09-27 18:18:30

我有下面的sql查询，我可以转换成LINQ

select count(*), BatchID 
from TimeSheetHeader 
group by BatchID having count(*) > 1

转换为

var duplicateBatchIDList = from c in _db.TimeSheetHeaders
                                               group c by c.BatchID into grp
                                               where grp.Count() > 1
                                               select grp;

但现在我正试图抛出一个额外的列，不包括在组by。我有SQL查询

select count(*), BatchID, TimeSheetCreationDate = min( TimeSheetCreationDate     ) 
from TimeSheetHeader 
where TimeSheetCreationDate >= CURRENT_TIMESTAMP
group by BatchID having count(*) > 1

但我不确定如何转换到LINQ

使用组by将SQL查询转换为LINQ

from c in _db.TimeSheetHeaders
where c.TimeSheetCreationDate >= CURRENT_TIMESTAMP
group c by c.BatchID into grp
where grp.Count() > 1
select new
{
    BatchID = grp.Key,
    Count = grp.Count(),
    TimeSheetCreationDate = grp.Min(x => x.TimeSheetCreationDate)
}

您可以在这里找到查询的lambda表达式版本:

var result = TimeSheetHeader
    .Where(t => t.TimeSheetCreationDate >= TIMESTAMP)
    .GroupBy(t => t.BatchID)
    .Where(g => g.Count() > 1)
    .Select(g => new 
        {
            Count = g.Count(),
            BatchID = g.Key,
            TimeSheetCreationDate = g.Min(x => x.TimeSheetCreationDate)
        })
    .ToList();

我稍微修改了Magnus的答案

var duplicateBatchIDList = (from c in _db.TimeSheetHeaders
                                               group c by c.BatchID into grp
                                               where grp.Count() > 1
                                               select new
                                               {
                                                   BatchID = grp.FirstOrDefault().BatchID,                                                      
                                                   TimeSheetCreationDate = grp.Min(x => x.TimeSheetCreationDate)
                                               }).Where(x => x.TimeSheetCreationDate <= DateTime.Now);
相关文章：