使用 QueryOver 对列使用子查询 [英] Using a subquery for a column with QueryOver

问题描述

我正在尝试通过 QueryOver 获得类似于以下 SQL 的内容:

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 = ...

我已经完成了投影，但我不确定如何恢复整个文档表.Documents 和 Events 是一对多的关系，我不想外连接，因为它会带来多个结果，内连接可能不会带回一行:

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

是否有更简单的方法来执行列的子查询?我不愿意将其替换为在其 get 中执行查询的属性.

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.

推荐答案

经过一番研究，看起来 HQL(QueryOver 转换成的)不支持子查询内的 TOP.

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

我的解决方案:创建一个包含计算属性的视图，然后在映射文件中将这些属性标记为 insert="false" 和 update="false"

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"

这篇关于使用 QueryOver 对列使用子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！