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

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

Solution

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