Hi all,
say that we have 2 entity EntityA and EntityB , with the related tables, TableA and TableB.
i have to implement this query:
Code:
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:
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:
Code:
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
Login
FAQ
Search