linq加入groupby和max发行
本文关键字：max 发行 groupby 加入 linq | 更新日期: 2023-09-27 18:27:40
我在玩实体框架，在复制SQL中相对简单的linq查询时遇到了很多问题。
public class ReportQueueServiceCommandDTO
{
public int Id { get; set; }
public DateTime CommandDatetime { get; set; }
public int CommandId { get; set; }
public bool IsCommandAcknowledged { get; set; }
public int QueueNumber { get; set; }
}
public IQueryable<ReportQueueServiceCommandDTO> GetLastestUnprocessedRequestsPerQueue()
{
// Get the maximum Acknowledged record per queue
var maxDateTimesPerAcknowledgedQueue = this.Get()
.Where(w => w.IsCommandAcknowledged)
.GroupBy(gb => gb.QueueNumber)
.Select(s => new ReportQueueServiceCommandDTO()
{
Id = (int)s.Max(m => m.Id),
CommandDatetime = s.FirstOrDefault(fod => fod.Id == s.Max(max => max.Id)).CommandDatetime,
CommandId = s.FirstOrDefault(fod => fod.Id == s.Max(max => max.Id)).CommandId,
IsCommandAcknowledged = true,
QueueNumber = s.Key
});
// Get the maximum unacknowledged record which had an ID greater than the maximum acknowledged record per queue.
// If the queue entry does not yet exist from MaxDateTimesPerAcknowledgedQueue, use the record anyway (left join-ish)
return from record in _context.ReportQueueServiceCommands
from maxRecord in MaxDateTimesPerAcknowledgedQueue.DefaultIfEmpty()
where record.QueueNumber == (maxRecord == null ? record.QueueNumber : maxRecord.QueueNumber)
&& record.Id > (maxRecord == null ? 0 : maxRecord.Id)
&& !record.IsCommandAcknowledged
group record by record.QueueNumber into groupedRecords
select new ReportQueueServiceCommandDTO()
{
Id = (int)groupedRecords.Max(m => m.Id),
CommandDatetime = groupedRecords.FirstOrDefault(fod => fod.Id == groupedRecords.Max(max => max.Id)).CommandDatetime,
CommandId = groupedRecords.FirstOrDefault(fod => fod.Id == groupedRecords.Max(max => max.Id)).CommandId,
IsCommandAcknowledged = false,
QueueNumber = groupedRecords.Key
};
}
使用上面的代码，我得到一个异常
DbIsNullExpression的参数必须引用基元，枚举或引用类型
当尝试迭代GetLatestUnprocessedRequestsPerQueue() 的结果时
如果我将查询更改为：
return from record in _context.ReportQueueServiceCommands
from maxRecord in MaxDateTimesPerAcknowledgedQueue
where record.QueueNumber == maxRecord.QueueNumber
&& record.Id > maxRecord.Id
&& !record.IsCommandAcknowledged
group record by record.QueueNumber into groupedRecords
select new ReportQueueServiceCommandDTO()
{
Id = (int)groupedRecords.Max(m => m.Id),
CommandDatetime = groupedRecords.FirstOrDefault(fod => fod.Id == groupedRecords.Max(max => max.Id)).CommandDatetime,
CommandId = groupedRecords.FirstOrDefault(fod => fod.Id == groupedRecords.Max(max => max.Id)).CommandId,
IsCommandAcknowledged = false,
QueueNumber = groupedRecords.Key
};
我没有得到我的错误，我几乎得到了我想要的结果，但是，如果该队列碰巧没有成功处理的命令，那么maxDateTimesPerAcknowledgedQueue中就没有该队列的记录。
编辑：给定数据：
Id CommandDatetime CommandId IsCommandAcknowledged QueueNumber
0 2014-01-01 1 0 1 -- should be returned as it is the maximum unacknowledged record for this queue when there are *no* acknowledged records for the queue.
1 2013-12-31 1 0 2 -- should not be returned as it is not the maximum unacknowledged record greater than the maximum acknowledged record
2 2014-01-01 1 1 2 -- should not be returned as already acknowledged
3 2014-01-01 1 0 2 -- should not be returned as it is not the maximum unacknowledged record greater than the maximum acknowledged record
4 2014-01-02 1 0 2 -- should be returned as it is the maximum unackowledged record past its maximum acknowledged record
5 2014-01-01 1 1 3 -- should not be returned as there are no "unacknowledged" records for this queue
预期结果：
Id CommandDatetime CommandId IsCommandAcknowledged QueueNumber
0 2014-01-01 1 0 1
4 2014-01-01 1 0 2
实际结果：
Id CommandDatetime CommandId IsCommandAcknowledged QueueNumber
4 2014-01-01 1 0 2
"实际结果"的问题是队列1没有任何已确认的记录，因此不会返回该记录（但我需要它）。可能会在这里重申，但我试图实现的是：
返回每个队列的最大未确认记录，大于每个队列的最大已确认记录（如果没有已确认记录）存在于队列中，则返回maxumum未确认的记录。
我希望我努力实现的目标是有意义的，有人能帮我吗？
检查是否为null：问题出在maxRecord == null。
在检查null之前使用ToList()。
实体框架：DbIsNullExpression的参数必须引用基元或引用类型
我最终找到了一种实现我想要的东西的方法，尽管它感觉非常笨重，我想知道是否有更好的方法：
/// <summary>
/// Get the latest unprocessed batch service command per queue
/// </summary>
/// <returns></returns>
public IQueryable<ReportQueueServiceCommandDTO> GetLatestUnprocessedCommandFromQueue()
{
// Get the maximum Acknowledged record per queue
var maxDateTimePerAcknowledgedQueue = this.Get()
.Where(w => w.IsCommandAcknowledged)
.GroupBy(gb => gb.QueueNumber)
.Select(s => new ReportQueueServiceCommandDTO()
{
Id = (int)s.Max(m => m.Id),
CommandDatetime = s.FirstOrDefault(fod => fod.Id == s.Max(max => max.Id)).CommandDatetime,
CommandId = s.FirstOrDefault(fod => fod.Id == s.Max(max => max.Id)).CommandId,
IsCommandAcknowledged = true,
QueueNumber = s.Key
});
// Get the maximum unacknowledged record per queue
var maxDateTimePerUnacknowledgedQueue = this.Get()
.Where(w => !w.IsCommandAcknowledged)
.GroupBy(gb => gb.QueueNumber)
.Select(s => new ReportQueueServiceCommandDTO()
{
Id = (int)s.Max(m => m.Id),
CommandDatetime = s.FirstOrDefault(fod => fod.Id == s.Max(max => max.Id)).CommandDatetime,
CommandId = s.FirstOrDefault(fod => fod.Id == s.Max(max => max.Id)).CommandId,
IsCommandAcknowledged = false,
QueueNumber = s.Key
});
// Get the maximum unacknowledged record which had an ID greater than the maximum acknowledged record per queue.
// If the queue entry does not yet exist from MaxDateTimesPerAcknowledgedQueue, use the record anyway (left join-ish)
return (from unack in maxDateTimePerUnacknowledgedQueue
join ack in maxDateTimePerAcknowledgedQueue on unack.QueueNumber equals ack.QueueNumber into joinedRecords
from subAck in joinedRecords.Where(w => w.Id > unack.Id).DefaultIfEmpty()
select new ReportQueueServiceCommandDTO()
{
Id = (subAck.Id == null ? unack.Id : subAck.Id),
CommandDatetime = (subAck.CommandDatetime == null ? unack.CommandDatetime : subAck.CommandDatetime),
CommandId = (subAck.CommandId == null ? unack.CommandId : subAck.CommandId),
IsCommandAcknowledged = (subAck.IsCommandAcknowledged == null ? unack.IsCommandAcknowledged : subAck.IsCommandAcknowledged),
QueueNumber = (subAck.QueueNumber == null ? unack.QueueNumber : subAck.QueueNumber)
})
.Where(w => !w.IsCommandAcknowledged);
}