转换SQL查询LINQ与一组由

问题描述：

我有下面的SQL查询，我能够conert到LINQ转换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;

但现在我试图抛出额外的列没有包含在组中。我有SQL查询

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

，但我不知道如何将它转换到LINQ

请检查我的答案呃 – user449689

答

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) 
}

g.key'上有'g'吗？ –

是g不存在在这里，也没有在新的 – Monzingo

c也添加DateTime.Now以取代Current_Timestamp – Monzingo

答

我稍微修改马格努斯的回答

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);

它如何可以downvoted当它的工作？其他答案有“钥匙”，因为它不是一把钥匙，所以它不被识别。 – Monzingo

'grp'具有'Key'的IGrouping'：https://msdn.microsoft.com/zh-cn/library/bb344977(v=vs.110).aspx – Magnus

@Monzingo请检查我的答案 – user449689

答

在这里你可以找到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();

转换SQL查询LINQ与一组由

相关推荐