Get Max row (Linq, NHibernate)

Question

Came across a problem whereby I wanted the last time data was imported to show failures. I ended up resorting to HQL because I had to execute two queries can anyone see why this doesn't work (properly) in Linq to NHibernate, is it a known bug in 3.1?

The sql I would write.

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

The HQL I ended up writing.

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

The Linq that works(but I think will break after 2000 rows)

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);

The Linq that doesn't work :(

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);

FYI http://stackoverflow.com/questions/656167/hitting-the-2100-parameter-limit-sql-server-when-using-contains — Mark, Apr 11 '12 at 10:43
Ahh I didn't know that, I did know contains was the DEVIL on performance though hehe :) — Mathew Thompson, Apr 11 '12 at 10:49
yes indeed. I would prefer my SQL query where I join to the sub query but NH doesn't support that currently as far as I know :( — Mark, Apr 11 '12 at 10:52
It does support joins, you'd need to use a join and a group by. Similar to this: http://stackoverflow.com/questions/695506/linq-left-join-group-by-and-count — Mathew Thompson, Apr 11 '12 at 10:58

score 3 · Accepted Answer · answered Apr 11 '12 at 07:48

3

Are you using .NET 4? If so, try this:

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

Or .NET 3.5 change the resultQuery part to be:

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

answered Apr 11 '12 at 07:48

Mathew Thompson

55,877
15
127
148

I'm using .Net 3.5. and the solution works!!! The .Net 4 solution looks like it will also have the 2000 result limit and executes two queries. – Mark Apr 11 '12 at 10:11
Can you explain why the innerQuery.Count works but my original query doesn't? (is it a bug/feature fixed in later versions of NH) – Mark Apr 11 '12 at 10:13
Ah glad it works. I think your original query doesn't work because you're doing a Contains before you're doing ToList and NHibernate is having trouble performing the Contains on a Queryable. I've seen some cases in NHibernate where a NotSupportedException is thrown when using stuff like IsNullOrWhiteSpace and stuff like that, the best thing to do (until they fix it) is resolve the query first (using ToList) THEN do your logic that you cannot achieve in the original query. – Mathew Thompson Apr 11 '12 at 10:22
In regards to the .Net 4 solution, Any is basically Count, but it's much faster because it stops as soon as it has found a match, whereas count obviously keeps traversing until it's been through the entire list. – Mathew Thompson Apr 11 '12 at 10:24
What I am trying to get at is the use of sub queries. They seam to work if used in conjunction with count but not with a contains (which translates to an in) or any(which translates to exists) unless the sub query is pre-executed (which incurs the 2000 input limit for any master query) – Mark Apr 11 '12 at 10:41
Yeah count just translates to `select count`. Any should work fine and faster that count, but is .NET 4 only. Try to avoid contains in subqueries at all costs really :) – Mathew Thompson Apr 11 '12 at 10:51

Get Max row (Linq, NHibernate)

1 Answers1