我正在使用Entity Framework，并且在复制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没有任何确认的记录，因此该记录没有被返回（但我需要它）。可能会在这里重申，但是我想要完成的是：
返回每个队列的最大未确认记录，大于每个队列的最大已确认记录，如果队列不存在已确认的记录，则返回最大未确认的记录。
我希望我要完成的目标有意义，有人可以帮助我吗？
我最终找到了一种方法来实现自己想要的目标，尽管感觉非常笨拙，而且我想知道是否有更好的方法可以做到这一点：
/// <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);
}
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句