Using Linq (nHibernate) to perform a Case statement with Counts

I'm sure this is one has done the houses a few times but i've never found a solution...

So is it possible to do something like this using nHibernate 3 with prefer开发者_开发知识库ably Linq:

SELECT   
COUNT(CASE WHEN IsWithdrawn = 1 THEN 1 END) AS WithdrawnCount, 
COUNT(CASE WHEN IsWithdrawn = 0 THEN 1 END) AS ViewAllCount
FROM Tutorials

I'm pretty sure that is isn't possible and that the best solution is to opt just for sql in this case... but maybe there is something new in nHibernate 3.1 that can do this with, even using queryover?

thanks

You can do it with HQL, which is almost the same as SQL:

SELECT   
SUM(CASE WHEN IsWithdrawn = 1 THEN 1 ELSE 0 END) AS WithdrawnCount, 
SUM(CASE WHEN IsWithdrawn = 0 THEN 1 ELSE 0 END) AS ViewAllCount
FROM Tutorials

(I'm not sure if the COUNT would work, I'm sure SUM does)

Here's a LINQ version that should work too:

session.Query<Tutorial>()
       .GroupBy(x => x.IsWithdrawn)
       .Select(x => new { x.Key, Count = x.Count() })

You can use Projections.Conditional with Criteria or QueryOver, but it's more work.

You can get the desired result with QueryOver, although it will be slower due to the subqueries.

var sums = repo.Session.QueryOver<Tutorials>()
    .SelectList(list => list
        .SelectSubQuery<Tutorials>(NHibernate.Criterion.QueryOver.Of<Tutorials>()
            .Where(t => t.IsWithdrawn)
            .ToRowCountQuery())
        .SelectSubQuery<Tutorials>(NHibernate.Criterion.QueryOver.Of<Tutorials>()
            .ToRowCountQuery())
    )
    .Take(1) // we want only one row in our result. In SQL I would use " from dummy".
    .List<object[]>();

Explanation:

I use two detached QueryOvers. The first one counts the rows in Tutorials where IsWithdrawn = true, the second once counts all rows. The two detached QueryOvers are then used as SubQueries in a normal QueryOver with a Projection (SelectList).

Here is the generated SQL:

SELECT TOP (1) 
(SELECT count(*) as y0_ FROM [Tutorials] this_0_ 
WHERE this_0_.IsWithdrawn = True) as y0_, 
(SELECT count(*) as y0_ FROM [Tutorials] this_0_) as y1_ 
FROM [Tutorials] this_;