将 SQL 语句转换为 Linq / Lambda Query
本文关键字:Lambda Query Linq SQL 语句 转换 | 更新日期: 2023-09-27 18:32:35
>Im 使用 MEF 并执行一个任务,该任务仅在返回超过 1 条记录时才需要使用聚合函数分组。我需要将开始小时的最大值和结束小时的最小值分组到一条记录中,就像我的 sql 会导致恢复的任务一样
var ohs = await Bl.UoW.Repositories.OperatingHours
.FindInDataSourceAsync(oh => ((oh.ProductId == productTypeId
&& oh.StateId == state)
|| (oh.StateId == complianceHours.State)));
这是 SQL,它基本上让我了解返回超过 1 条记录时需要什么
SELECT
StateId,
MAX(ComplianceHourStart),
MIN(ComplianceHourEnd)
FROM
OperatingHours
GROUP BY
StateId
HAVING
StateId = 'CA'
因此,当超过 1 个时,我可以进一步过滤它,但不确定如何实现最大值和最小值?
if (ohs != null && ohs.Count() > 1)
{
//
ohs = ohs.GroupBy(x => x.State).Max(x => x.ComplianceHourStart?...
}
谢谢
从你的SQL来看,这应该很接近:
var result = context.OperatingHours
.GroupBy(oh => oh.StateId)
.Select(oh => new {StateId = oh.Key,
MaxStart = oh.Max(x => x.ComplianceHourStart),
MinEnd = oh.Min(x => x.ComplianceHourEnd)});
。虽然我不确定您在限制状态 ID 列(组键)时为什么要分组。以下也足够了:
var result = context.OperatingHours
.Where(oh => oh.StateId == 'CA')
.Select(oh => new {MaxStart = oh.Max(x => x.ComplianceHourStart),
MinEnd = oh.Min(x => x.ComplianceHourEnd)});
这样的事情应该这样做:
ohs = ohs.GroupBy(x => x.State)
.Select(g => new
{
//You need to make a choice on StateId, here... First one?
StateId = g.First().StateId,
MaxComplianceHourStart = g.Max(o => o.ComplianceHourStart),
MinComplianceHourEnd = g.Min(o => o.ComplianceHourEnd)
});