开发者

Using a subquery for a column with QueryOver

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

SELECT
    docs.*,
    (SELECT TOP 1 eventDate from events WHERE id=do开发者_如何学Gocs.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.


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"

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜