Nested Select Count Query with Criteria

Question

Hi all,

say that we have 2 entity EntityA and EntityB , with the related tables, TableA and TableB.

i have to implement this query:

select a.Id , (select count(b.Id) from TableB b where b.FKa = a.Id and b.AnotherField > 0) as TheCount
from TableA a 

i'm very close to that since i wrote this code:

var subCrit = DetachedCriteria.For<EntityB>
                                        .Add<EntityB>(e => e.AnotherField > 0)
                                        .SetProjection(LambdaProjection.Count<EntityB>(e => e.Id).As("TheCount"));

var crit = Session.CreateCriteria<EntityA>
                        .SetProjection(LambdaProjection.GroupProperty<EntityA>(e => e.Id).As("Id),
                                            Projections.SubQuery(subCrit));

if i execute this criteria i obtain the following SQL:

select a.Id as Id , (select count(b.Id) from TableB b where b.AnotherField > 0) as TheCount from TableA a

as u can see , it's very close to what i'm trying to achieve...the problem (and it's definetely a big problem :D) is that theres no link between the subquery and the entities of TableA ( where b.FKa = a.Id ). I cant find a way to correlate the subquery to the external query via criteria.

Any suggestions ?

Ta a lot

Alessandro

EDIT:

changing the point of view i also could do something like that:

 var crit = Session.CreateCriteria<EntityA>()
                   .CreateAlias<EntityB>(a => a.B, () => b);
                   .SetProjection(LambdaProjection.Count<A>(a => b.Id).As("TheCount"),
                   .SetProjection(LambdaProjection.GroupProperty<EntityA>(a => a.Id));

and this is generating the following sql:

select count(b.Id) as TheCount, a.Id as IDa
from TableA a left outer join TableB b
on a.Id = b.FKa
group by a.Id

but here you can see that the additional where clause b.AnotherField > 0 is missing , and i dont know how to insert it just for the count.

Hope it's clear , thanks again

Answer 1

My suggestion is to change the SQL Statement

SELECT a.is, count(a.id) FROM TableA a JOIN TableB b ON a.id = b.fka AND b.af > 0 GROUP by a.ID

And create for it easy criteria.

Answer 2

Here is the solution:

var condition = Expression.Gt("b.AnotherField",0);
var conditionalProjection = Projections.Conditional(condition, Projections.Constant(1), Projections.Constant(0));

crit = Session.CreateCriteria<EntityA>()
                   .CreateAlias<EntityB>(a => a.B, () => b);
                   .SetProjection(Projections.Count(conditionalProjection).As("TheCount"),
                     (LambdaProjection.GroupProperty<EntityA>(a => a.Id));

and this is the sql generated:

select count(case b.AnotherField > 0 then 1 else 0 end) as TheCount , a.Id from TableA a inner join TableB b

hope it can be useful

cheers

Alessandro

enter code here