Using a subquery for a column with QueryOver

Question

I'm trying to get something similar to the SQL below via QueryOver:

SELECT
    docs.*,
    (SELECT TOP 1 eventDate from events WHERE id=docs.id 
    AND type=4 ORDER BY eventDate DESC) as eventDate
FROM documents as docs
WHERE doc.accountId = ...

I've got close with a projection, however I'm not sure how to get the entire documents table back. Documents has a one-to-many relationship with Events, I don't want to outer join as it will bring multiple results, and an inner join may not bring back a row:

var query = QueryOver<Document>
    .Where(d => d.Account == account)
    .SelectList(list => list
        .Select(d => d)
        .Select(d => d.Events.OrderByDescending(e => e.EventDate).FirstOrDefault(e => e.Type == 4))
    )
    .List<object[]>()
    .Select(d => return new DocumentSummary(d[0],d[1]) etc.);

Is there an easier way of performing subqueries for columns? I'm reluctant to replace this with the property performing a query in its get.

Johan

Related: #2713568 – Johan

What version of NH are you using? – Adulterant

Another related: #2049001 – Johan

Answer 1

After some research it looks like HQL (which QueryOver is converted into) does not support TOP inside subqueries.

My solution: create a view which includes the computed properties, and then mark these properties in the mappings files as insert="false" and update="false"

Johan