NHibernate QueryOver - Retrieve all, and mark the ones already "selected"

Question

Dear NHibernate experts,

The following query gives me all my categories:

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

.. 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();

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;

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!

mikal

score 3 · Accepted Answer · answered Sep 15 '13 at 14:58

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 ...

But thanks to NHibernate, and abstract Querying API, we can create the query to be working in all supported DB dialects.

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)
);

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>();

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

NOTE: this approach is working, but the statements should be taken as a draft. Some adjustment could be needed in your case...

Thanks again Radim! This works, but selecting and transforming to Category doesn't bring my child collections data along (Ie. Languages- list (NHibernate Bag-collection). Is this possible to include, when using .SelectList() ? — mikal, Sep 16 '13 at 11:16
Yes, transformers will (until you introduce your own) will never populate the one-to-many collections (``). In these cases, you have to use no-projections (directly access category). If the *IsSelected* feature is needed, you can use "formula" mapping. That will allow you, to have the really mapped proeprty to that conditional statement. On the other hand, this will be executed always and can bring you performance issues. So, you have to select what approach to use when... ;) — Radim Köhler, Sep 16 '13 at 11:25

NHibernate QueryOver - Retrieve all, and mark the ones already "selected"

1 Answers1

Linked