NH QueryOver-在子查询中使用主查询的属性

问题描述

我正在尝试将以下SQL转换为QueryOver:

I am trying to convert following SQL to QueryOver:

Select 1
From myTable mt
Where mt.ForeignKey in (select ID from otherTable ot where ot.ID = R.ID)

我想在EXISTS/NOT EXISTS语句内使用此子查询:

I want to use this subquery inside an EXISTS / NOT EXISTS statement like:

select * from table R where .... AND EXISTS (query above)

目前我有类似的东西

mainQuery.WithSubquery.WhereExists(QueryOver.Of<myTable>()
                    .Where(mt => mt.ForeignKey)
                    .WithSubquery.IsIn(QueryOver.Of<otherTable>().Where(c => c.Id == R.SomeId)));

我将此查询创建为要连接到主查询的子查询.问题是别名为R的表是主查询调用的表，我不知道如何访问表(NHibernate Model)R的列(在上面的查询中不可用)，所以我的问题是:

I created this query as a subquery which I want to connect to the main query.The problem is that the table aliased as R is the table called by the main query and I don´t know how to access columns of the table (NHibernate Model) R (which is not accesible in the query above), so my question is:

如何从主查询中获取值并在子查询中使用它们.我认为这只能通过内联创建子查询(如mainQuery.WithSubquery.Where(..)或smth.类似)来实现，但是我看不出这样做的最佳方法.感谢您的帮助！

How can I get values from the main query and use them in a subquery. I think this is only possible by creating the subquery inline (as in mainQuery.WithSubquery.Where(..) or smth. similar) but I can´t see what would be the best possible way to do so. I appreciate any help!

提前谢谢！

推荐答案

诀窍是对父查询使用适当的别名:

The trick is to use proper alias, for the parent query:

// the alias
myTable R = null;

mainQuery
    .WithSubquery
       .WhereExists(QueryOver
         .Of<myTable>( () => R) // the Alias in place
         .Where(mt => mt.ForeignKey)
         .WithSubquery.IsIn(QueryOver.Of<otherTable>().Where(c => c.Id == R.SomeId)));

请注意，对于mainQuery部分还不是很确定，但是这里的解决方案通常是这样的:

Note, not fully sure about the mainQuery part, but the solution in general here is like this:

// I. the outer query ALIAS
Employee emplyoee = null;

// II. the subquery - using the alias
var subQuery = QueryOver.Of<Contact>()
    .Select(x => x.ID)
    .Where(x => x.Related.ID == emplyoee.ID); // use alias

// III. declare the outer query and use the above alias
var query = session.QueryOver<Employee>(() => emplyoee) // declare alias
    .WithSubquery
        .WhereExists(subQuery); // put both together

还请检查此以获取更多想法

Also check this for more ideas

这篇关于NH QueryOver-在子查询中使用主查询的属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！