SQL到LINQ -滚动月份的日期

本文关键字：日期 滚动 LINQ SQL | 更新日期: 2023-09-27 18:02:41

希望将此转换为LINQ。

select COUNT(*) as 'BillsOver30' from pssuite_web.pujhaccd 
where billdays>30 and DATEDIFF(month,'07-07-2016', GETDATE()) <= 13
Group By Month(billdate)

这将显示从07-07的13个滚动月，每个月有多少账单超过30。

这是我在LINQ中写的可怕的查询，它不起作用:

 DateTime earliestDate = objdate1.DateStart.Value.AddMonths(-13);
 var custQuery9 = ((from m in DataContext.pujhaccds
                    where m.billdays > 30
                    && m.billdate >= earliestDate
                    && m.billdate <= objdate1.DateStart
                    group m by m.billdays into p
                    select p)).Count();

SQL到LINQ -滚动月份的日期

得到你在注释中描述的结果:

var result = (from m in DataContext.pujhaccds 
              where m.billdays > 30 && 
                  m.billdate >= earliestDate && 
                  m.billdate <= objdate1.DateStart 
              group m by m.billdate.Month into p   
              select new { Month = p.Key, Count = p.Count() }).ToList();

将上面的select替换为如下所示:

select p.Count()

如果你有不同年份的数据并想要分开，那么分组将是有问题的。如果是这种情况，按2个字段分组-年和月:

var result = (from m in DataContext.pujhaccds 
              where m.billdays > 30 && 
                  m.billdate >= earliestDate && 
                  m.billdate <= objdate1.DateStart 
              group m by new { m.billdate.Value.Month, m.billdate.Value.Year }  into p   
              select new { Date = $"{p.Key.Month} - {p.Key.Year}", Count = p.Count() }).ToList();
相关文章：