(流利)NHibernate SELECT IN

发布于 2024-12-17 07:46:26 字数 606 浏览 0 评论 0 原文

给出下表:

tool
 *toolid
 *n other fields

process
 *processid
 *n other fields

toolprocess
 *toolprocessid
 *toolid
 *processid
 *n other fields

当尝试为特定进程选择所有工具时,我在 toolprocess 上进行了数千次选择,其中我的 Linq 如下所示:

from tool in tools
where toolprocesses.Any(t=>t.Tool.Id==tool.Id)
select tool

其中 toolprocesses 包含具有相同 processid 的工具进程列表

在 SQL 中我只需编写

SELECT * FROM TOOL WHERE toolid IN 
    (SELECT TOOLID FROM TOOLPROCESS WHERE processid = 'someid');

它几乎不需要时间并且按预期工作

如何让 NHibernate 创建此查询(或类似的查询)?

Given the following tables:

tool
 *toolid
 *n other fields

process
 *processid
 *n other fields

toolprocess
 *toolprocessid
 *toolid
 *processid
 *n other fields

When trying to select all tools for a specific process I get up to a few thousand selects on toolprocess where my Linq looks like this:

from tool in tools
where toolprocesses.Any(t=>t.Tool.Id==tool.Id)
select tool

where toolprocesses contains the list of toolprocesses with the same processid

In SQL I would just write

SELECT * FROM TOOL WHERE toolid IN 
    (SELECT TOOLID FROM TOOLPROCESS WHERE processid = 'someid');

It takes almost no time and works as expected

How can I get NHibernate to create this query (or something similar)?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

在梵高的星空下 2024-12-24 07:46:26

我不知道是否可以在 Query 中完成,但可以在 QueryOver / Criteria 中完成。

在 QueryOver 中,它看起来像:

var subQuery = QueryOver.Of<Toolprocess>()
                        .Where(x => x.Process.Id == id) 
                        .Select(x => x.Tool.Id);

var result = session.QueryOver<Tool>()
                    .WithSubquery.WhereProperty(x => x.Id).In(subQuery)
                    .List();

http: //www.philliphaydon.com/2010/09/28/queryover-with-nhibernate-3-lovin-it/

或者,如果你想做 Exists 而不是比在,我在这里写了博客:

http://www.philliphaydon.com/2011/01/19/revisiting-exists-in-nhibernate-3-0-and-queryover/

I don't know if you can do it in Query, but you can do it in QueryOver / Criteria.

In QueryOver it would look like:

var subQuery = QueryOver.Of<Toolprocess>()
                        .Where(x => x.Process.Id == id) 
                        .Select(x => x.Tool.Id);

var result = session.QueryOver<Tool>()
                    .WithSubquery.WhereProperty(x => x.Id).In(subQuery)
                    .List();

http://www.philliphaydon.com/2010/09/28/queryover-with-nhibernate-3-lovin-it/

Alternatively, if you want to do Exists rather than In, I've blogged about it here:

http://www.philliphaydon.com/2011/01/19/revisiting-exists-in-nhibernate-3-0-and-queryover/

知足的幸福 2024-12-24 07:46:26

尝试一下

from t in Session.Query<Tool>()
join tp in Session.Query<Toolprocess>() on t equals tp.Tool
where tp.Process.Id == 'someid'
select t;

,我假设您使用的是 NH 3.X。这应该比 Select...Where...In 查询更快。

Try

from t in Session.Query<Tool>()
join tp in Session.Query<Toolprocess>() on t equals tp.Tool
where tp.Process.Id == 'someid'
select t;

I assume that you're using NH 3.X. This should be even faster than the Select...Where...In query.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文