NHibernate的QueryOver - 检索所有，并已与QUOT标记的人;选择" [英] NHibernate QueryOver - Retrieve all, and mark the ones already "selected"

问题描述

尊敬的NHibernate的专家，

Dear NHibernate experts,

下面的查询给了我所有我的类别：

The following query gives me all my categories:

var result = Session.QueryOver(() => cat).List();

..并通过运行该查询，我得到选择那些（category_x_product表）：

.. and by running this query, I get the ones selected (category_x_product table):

int productId = 11;
Category cat = null;
CategoryProduct cp = null;

var subQuery = QueryOver.Of(() => cp)
        .Where(() => cp.ProductId == productId)
        .Select(Projections.Distinct(Projections.Property(() => cp.CategoryId)));

result = Session.QueryOver(() => cat)
                .WithSubquery
                .WhereProperty(() => cat.Id).In(subQuery)
                .List();

任何方式这两个查询组合，让我得到指示哪一个所有类别有一个布尔值实际上在CategoryProduct查询选择。

Any way to combine those two queries, so that I get all categories with a boolean value indicating which one was in fact "selected" in the CategoryProduct-query.

其映射到这样一个实体，也许？

Map it to an entity like this, maybe?

CategorySelected
----------------
Category Category { get; set; }
bool IsSelected { get; set;

我试图找到一个答案，使用QueryOver，但没有成功。这甚至可能在一个或多或少简单查询？任何帮助深表感谢。谢谢！

I've tried to find an answer to this, using QueryOver, but didnt succeed. Is this even possible in a "more or less" simple query? Any help is much appreciated. Thanks!

米卡尔

推荐答案

要实现这一目标的方法之一，就是创造条件SELECT语句。在SQL Server的情况下，我们想产生这样的事情

One way to achieve that, is to create Conditional SELECT statement. In Case of SQL Server we would like to generate something like this

SELECT CASE CategoryId IN (.... subselect ) THEN 1 ELSE 0 END ...

的但由于NHibernate的，和抽象查询API，我们可以创建于所有支持DB的方言是工作查询。的

让我们尝试创造新的解决方案草案。我们将调整子查询第一个

Let's try to create a draft of new solution. We will adjust the SubQuery first

var subQuery = QueryOver.Of(() => cp)
    .Select(Projections.Distinct(Projections.Property(() => cp.CategoryId)));

现在，我们将创造条件语句

Now we will create the conditional statement

var isSelected = Projections.Conditional(
    Subqueries.PropertyIn("Id", subQuery) // Category ID to be in the inner select
    , Projections.Constant(1)
    , Projections.Constant(0)
);

和我们将注入该条件到 QueryOver 并使用变形金刚来正确填充类别的（包括虚拟 IsSelected ）

And we will inject that condition into the QueryOver and use Transformers to have correctly populated properties of the Category (including the virtual IsSelected)

Category category = null
result = Session.QueryOver(() => cat)
    // SELECT clause is now built
    .SelectList(list => list
        .Select(isSelected).WithAlias(() => category.IsSelected)
        .Select(ca => ca.Id).WithAlias(() => category.Id)
        ... // all properites we would like to be populated
    )
    // Transform results into Category again
    .TransformUsing(Transformers.AliasToBean<Category>())
    .List<Category>();

而现在，我们的新的 IsSelected 属性，这是不映射，但只能用于此选择的（预测）的，填充用正确的信息

And now, Our new IsSelected property, which is not mapped, but only used for this SELECT (projections), is populated with the correct information.

的注：此方法是工作，但发言应被看作是一个草稿。可能需要你的情况... 的

这篇关于NHibernate的QueryOver - 检索所有，并已与QUOT标记的人;选择"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！