SqlToLinq转换问题
1
悬赏园豆：20
[已解决问题]
解决于 2021-09-26 09:28
现有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;
最佳答案
2
这样的吧
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();
收获园豆：20
其他回答(2)
1
sql 因为每家（厂家）并没有做to linq 的实现（ef中，ef只做了linq to sql过程），所以不存在这个自动化过程。原则上是可以的，毕竟sql最终（语法解析完）的实现大致就是这些基本函数（更少）过程。
目前大约只能靠人工，站在程序员角度，sql本来就是多余的（没这个语法parse过程，数据库会省一步），只是这个已经是一个标准了，是关系数据库的通用方式。
你的意思是Linq实现不了？
0
333333333333
