SqlToLinq转换问题

现有Model如下

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;

回答

这样的吧

            var list = GetAlarms();
            var results = list.Where(q => q.BeginTime >= Convert.ToDateTime("2021-09-23 00:00:00") && q.BeginTime < Convert.ToDateTime("2021-09-30 00:00"))
                .GroupBy(q => new { DayDate = q.BeginTime.ToString("yyyy-MM-dd")})
                .Select(q => new
                {
                    q.Key.DayDate,
                    Fs4Count = q.Count(u => u.AreaId == "FS"),
                    Fs5Count = q.Count(u => u.AreaId == "FSV"),
                    CpCount = q.Count(u => u.AreaId == "CP"),
                    GCount = q.Count(u => u.AreaId == "GYGC")
                })
                .OrderBy(q=>q.DayDate)
                .ToList();