获取最大行(Linq，NHibernate)

本文关键字：NHibernate Linq 获取 | 更新日期: 2023-09-27 18:30:47

遇到了一个问题，我希望上次导入数据时显示失败。我最终求助于 HQL，因为我必须执行两个查询，任何人都可以看到为什么这在 Linq 到 NHibernate 中（正确）不起作用，这是 3.1 中的已知错误吗？

我会写的sql。

select JobImport.* from 
JobImportResult
inner join (
    select Max(JobImportResultId) as JobImportResultId 
    from JobImportResult
    group by JobImportId
)as tbl on tbl.JobImportResultId = JobImportResult.JobImportResultId
inner join JobImport on JobImport.JobImportId = JobImportResult.JobImportId
where ImportFailureReasonId is not null

我最终写的HQL。

select jir.JobImport from JobImportResult jir where jir.Id in 
(select max(mjir.Id) from JobImportResult mjir group by mjir.JobImport)
and jir.ImportFailureReason is not null

有效的 Linq（但我认为会在 2000 行后中断）

var innerQuery = Query<JobImportResult>()
    .GroupBy(jir=>jir.JobImport)
    .Select(jir=>jir.Max(jr=>jr.Id));
var innerQueryListed = innerQuery.ToList();
var resultQuery = Query<JobImportResult>()
    .Where(jir => innerQueryListed.Contains(jir.Id) && jir.ImportFailureReason != null)
    .Select(jir => jir.JobImport);

不起作用的 Linq :(

var innerQuery = Query<JobImportResult>()
.GroupBy(jir=>jir.JobImport)
.Select(jir=>jir.Max(jr=>jr.Id));
var resultQuery = Query<JobImportResult>()
.Where(jir => innerQuery.Contains(jir.Id) && jir.ImportFailureReason != null)
.Select(jir => jir.JobImport);

获取最大行(Linq，NHibernate)

你使用的是 .NET 4 吗？如果是这样，请尝试以下操作：

var innerQuery = Query<JobImportResult>()
    .GroupBy(jir=>jir.JobImport)
    .Select(jir=>jir.Max(jr=>jr.Id))
    .ToList();
var resultQuery = Query<JobImportResult>()
    .Where(jir => innerQuery.Any(j => j == jir.Id)
        && jir.ImportFailureReason != null)
    .Select(jir => jir.JobImport)
    .ToList(); //only use this if you want to resolve the query

或者 .NET 3.5 将resultQuery部分更改为：

var resultQuery = Query<JobImportResult>()
    .Where(jir => innerQuery.Count(j => j == jir.Id) > 0
        && jir.ImportFailureReason != null)
    .Select(jir => jir.JobImport)
    .ToList(); //only use this if you want to resolve the query
相关文章：