NHibernate QueryOver - Check out all and check the ones that are already selected. "

Question

Dear NHibernate Experts,

The following query gives me all my categories:

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

.. and by executing this query, I get the 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 these two queries so that I get all the categories with a boolean indicating which one was actually selected in the CategoryProduct question.

Is it possible to attribute it to an entity like this?

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

I tried to find the answer to this question using QueryOver, but could not. Is this possible in a "more or less" simple query? Any help is much appreciated. Thanks!

Mikal

Answer 1

One way to achieve this is to create a conditional SELECT statement. In the case of SQL Server, we would like to create something like this

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

But thanks to NHibernate and the abstract Querying API, we can create a query to work in all supported DB dialogs.

Try creating a draft new solution. First we will adjust SubQuery

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

Now we will create a 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 add this condition to QueryOver and use Transformers to have correctly filled category properties (including 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 displayed, but is used only for this SELECT (projection), is filled with the correct information.

NOTE: this approach works, but statements should be considered as a draft. Some adjustment may be required in your case ...