SqlToLinq转换问题

Question

public class AlarmRecord : Entity
{
/// <summary>
/// 区域编号
/// </summary>
public virtual string AreaId { get; set; }

    /// <summary>
    /// 报警开始时间
    /// </summary>
    public virtual DateTime BeginTime { get; set; }

    /// <summary>
    /// 报警结束时间
    /// </summary>
    public virtual DateTime? EndTime { get; set; }

}

集合如下

static List<AlarmRecord> GetAlarms()
{
var alarms = new List<AlarmRecord>
{
new AlarmRecord(){BeginTime = DateTime.Now, EndTime = DateTime.Now.AddSeconds(30),AreaId = "FS"},
new AlarmRecord(){BeginTime = DateTime.Now.AddSeconds(-30), EndTime = DateTime.Now.AddSeconds(30),AreaId = "FS"},
new AlarmRecord(){BeginTime = DateTime.Now.AddSeconds(-20), EndTime = DateTime.Now.AddSeconds(30),AreaId = "FSV"},
new AlarmRecord(){BeginTime = DateTime.Now.AddSeconds(-23), EndTime = DateTime.Now.AddSeconds(30),AreaId = "FSV"},
new AlarmRecord(){BeginTime = DateTime.Now.AddSeconds(-22), EndTime = DateTime.Now.AddSeconds(30),AreaId = "CP"},
new AlarmRecord(){BeginTime = DateTime.Now.AddSeconds(-10), EndTime = DateTime.Now.AddSeconds(30),AreaId = "GYGC"}
};
return alarms;
}

我需要统计 最近7天各区域报警数量变化，请问下面的sql脚本如果转换成linq或Lamda？我转了几次运行都报错，提示无法翻译linq/lamda语句

SELECT CONVERT(VARCHAR(10), BeginTime, 120) AS DayDate ,
COUNT(CASE WHEN ( AreaID = 'FS' ) THEN 1
END) AS Fs4Count ,
COUNT(CASE WHEN ( AreaID = 'FSV' ) THEN 1
END) AS Fs5Count ,
COUNT(CASE WHEN ( AreaID = 'CA' ) THEN 1
END) AS CpCount ,
COUNT(CASE WHEN ( AreaID = 'Gygc' ) THEN 1
END) AS GCount
FROM dbo.AlarmRecord
WHERE BeginTime >= '2021-07-01 00:00:00'
AND BeginTime < '2021-07-08 00:00'
GROUP BY CONVERT(VARCHAR(10), BeginTime, 120)
ORDER BY DayDate;

Answer 1

DateTime begin = . . .
DateTime end = . . .

var result = GetAlarms( )
    .Where( a => a.BeginTime.Date >= begin.Date && a.BeginTime.Date < end.Date )
    .GroupBy( a => a.BeginTime.Date )
    .Select( g =>
        new
        {
            DayDate = g.Key.Date,
            Fs4Count = g.Count( v => v.AreaId == "FS" ),
            Fs5Count = g.Count( v => v.AreaId == "FSV" ),
            CpCount = g.Count( v => v.AreaId == "CA" ),
            GCount = g.Count( v => v.AreaId == "GYGC" )
        } )
    .ToList( );

Answer 2

I tested your sql and converted it to equivalent linq code, please try if it can work for you.

            List<AlarmRecord> alarmRecords = GetAlarms();  
  
            var re = (from record in alarmRecords  
                     where record.BeginTime >= DateTime.Parse("2021-07-01")  
                     where record.BeginTime < DateTime.Parse("2021-07-08")  
                     group record by record.BeginTime into gcs  
                     select new  
                     {  
                         DayDate = gcs.Key,  
                         Fs4Count = gcs.Where(r=>r.AreaId == "FS").Count(),  
                         Fs5Count = gcs.Where(r=>r.AreaId == "FSV").Count(),  
                         CpCount = gcs.Where(r=>r.AreaId == "CP").Count(),  
                         GCount = gcs.Where(r=>r.AreaId == "GYGC").Count()  
                     }).OrderBy(r=>r.DayDate);  

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.