c# - 获取最大行(Linq、NHibernate)

Question

遇到一个问题，我希望最后一次导入数据时显示失败。我最终求助于 HQL，因为我必须执行两个查询，谁能看出为什么这在 Linq to 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);

Answer 1

您使用的是 .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