如何使用 NHibernate 的 QueryOver API 表达包含 WHERE..IN 子查询的查询? [英] How do I express a query containing a WHERE..IN subquery using NHibernate's QueryOver API?

问题描述

我有一个对象模型，其中一个 Order 包含许多 LineItems，并且每个 LineItem 都有一个关联的 Product.在对象模型中，这些是单向关联——LineItem 对其Order 一无所知.

I have an object model where an Order contains many LineItems, and each LineItem has an associated Product. In the object model, these are one-way associations -- a LineItem does not know anything about its Order.

我想查询包含产品名称与字符串匹配的订单项的订单，为每个订单返回一行(以便可以执行分页).

I want to query for orders that contain a line item with a product name matching a string, returning one row for each order (so that paging can be performed).

SELECT * FROM Orders 
WHERE OrderID IN (
    SELECT DISTINCT OrderID 
    FROM LineItems 
    INNER JOIN Products on LineItems.ProductID = Products.ProductID
    WHERE Products.Name = 'foo'
)

鉴于我有一个 ICriteria 或一个 IQueryOver 表示子查询，我该如何将其实际应用到我的根订单查询中?

Given that I have an ICriteria or an IQueryOver representing the subquery, how do I actually apply it to my root Order query?

var subquery = QueryOver.Of<LineItem>
                        .Where(l => l.Product.Name == "foo")
                        .TransformUsing(Transformers.DistinctRootEntity);

我发现了很多假设查询中的根对象位于一对多关系的多"端的示例，但我不知道如何添加限制根对象有很多.

I've found plenty of examples that assume the root object in the query is on the "many" side of a one-to-many relationship, but I can't figure out how to add a restriction on something that the root object has many of.

推荐答案

我会使其成为订单和订单项之间的双向关系，以允许高效查询(减少所需的联接数量).但是，如果由于某种奇怪的原因你不能，你需要从订单开始子查询......

I'd make it a bi-directional relationship between order and line item to allow efficient queries (reduce the number of joins required). But, if for some weird reason you can't, you'll need to start the sub-query from the Order...

LineItem lineItemAlias = null;
Product productAlias = null;

var subQuery = QueryOver.Of<Order>()
            .JoinAlias(x => x.LineItems, () => lineItemAlias)
            .JoinAlias(() => lineItemAlias.Product, () => productAlias)
            .Where(() => productAlias.Name == "foo")
            .Select(Projections.Group<Order>(x => x.Id));

var results = Session.QueryOver<Order>()
              .WithSubquery.WhereProperty(x => x.Id).In(subQuery)
              .List();

这篇关于如何使用 NHibernate 的 QueryOver API 表达包含 WHERE..IN 子查询的查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！