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( );
SqlToLinq转换问题
建华 何
21
Reputation points
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;
Accepted answer
-
Viorel 117.6K Reputation points
2021-09-24T08:37:15.123+00:00
1 additional answer
Sort by: Most helpful
-
Timon Yang-MSFT 9,591 Reputation points
2021-09-24T08:39:20.62+00:00 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.