在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 = ...
我已经有了一个投影,但是我不确定如何获取整个文档表.文档与事件"具有一对多关系,我不想进行外部联接,因为它会带来多个结果,而内部联接可能不会返回一行:
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转换为HQL)不支持子查询内部的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屋!