NHibernate QueryOver puts group by in select

Question

This might have been asked a few times and I indeed found a few questions (one or two) with the same subject. However those questions were rather old aswell as the fact they remained unaswered.

Basically I've got a QueryOver (tried to get the same result with Linq to NHibernate but apperently a bit to complex for the Linq):

PrintJobType printJobType = null;
var test = unitOfWork.Session.QueryOver<PrintJob>()
  .JoinAlias(pj => pj.PrintJobType, () => printJobType)
  .Where(pj => pj.PrintedOn == null)
  .Select(Projections.ProjectionList()
            .Add(Projections.Group(() => printJobType.PriorityWeight))
            .Add(Projections.Group(() => printJobType.ID)))
  .OrderBy(pj => printJobType.PriorityWeight).Desc
  .OrderBy(Projections.Min<PrintJob>(pj => pj.ID)).Asc
  .List<object[]>()
  .Select(x => x[1])
  .Cast<int>();

The biggest problem is that for the life of me I can't get the grouping out of the select, I only want the ID of the printjob type returned but I can't seem to be able to get that to work. This query basically Sorta by PrintJobWeight first and the Min Printjob ID second.

(So the current query looks like:)

SELECT 
    printjobty1_.PriorityWeight as y0_, printjobty1_.ID as y1_ 
FROM 
    [PrintJob] this_ 
    inner join [PrintJobType] printjobty1_ on this_.PrintJobType_id=printjobty1_.ID 
WHERE 
    this_.PrintedOn is null 
GROUP BY 
    printjobty1_.PriorityWeight, 
    printjobty1_.ID 
ORDER BY 
    printjobty1_.PriorityWeight desc, 
    min(this_.ID) asc

So I'd basically like to only return the ID of the printjobtype.

As a 'bonus' what we really want is select the PrintJobType in it's entirety since as it currently stands I have to do a .Load(id) call right after selecting the list which to me also seems rather redundant but I could live with that but the current result just seems super inefficient to me.

Meaning in a perfect world the query hitting the server would look like:

SELECT 
    printjobty1_.*
FROM 
    [PrintJob] this_ 
    inner join [PrintJobType] printjobty1_ on this_.PrintJobType_id=printjobty1_.ID 
WHERE 
    this_.PrintedOn is null 
GROUP BY 
    printjobty1_.*
ORDER BY 
    printjobty1_.PriorityWeight desc, 
    min(this_.ID) asc

(Yes you can't put a printjobty1_.* in the group by clause but it's quicker then writting down all the properties and things by hand)

NHibernate version: 3.3.1.4000 - SQL Server 2012

Answer 1

Desired solution with NHibernate would use a subquery. That way we can firstly get the ID we need (Grouping, sorting, filtering) and then use that ID to get the clear result. Please observe the power of NHibernate and Subqueries here:

• Query on HasMany reference

I am not 100% sure what you want to get, but there is a draft of the solution:

PrintJob printJob = null;
PrintJobType printJobType = null;

// simply this is the place, where we do all the magic
// to select the PrintJob.ID
// I guess that my adjustments would result in the same stuff 
// as in the query above without the grouping
// ... but if not, we still do have a draft of HOW TO
var subQuery = QueryOver.Of<PrintJob>()
    .JoinAlias(pj => pj.PrintJobType, () => printJobType)
    .Where(pj => pj.MiddleName == null)
    .SelectList(l => l
        .Select(pj => pj.ID)
    )
    .OrderBy(() => printJobType.PriorityWeight).Desc
    .OrderBy(pj => pj.ID).Asc
    .Take(1)
    ;

// here we SELECT the clean, root object and its reference... 
// that all will be executed as only ONE Select statement
var result = session.QueryOver<PrintJob>(() => printJob)
    .WithSubquery
        .WhereProperty(() => printJob.ID)
        .In(subQuery)
    .JoinAlias(x => x.PrintJobType, () => printJobType)
    .SingleOrDefault<PrintJob>();

Now, for some test purposes, we can continue:

// here we say session clear just for the TEST purposes below
session.Clear();

// now, session is closed, no lazy loading, but due to JOIN ALIAS
// the PrintJobType is loaded as well
Assert.IsTrue(result != null);
Assert.IsTrue(result.PrintJobType != null);
Assert.IsTrue(result.PrintJobType.PriorityWeight != null);