Hello everyone,
I am basically trying to create this query with NHibernate ICriteria interface:
SomeTable 1:n AnotherTable
SomeTable has columns: PrimaryKey, NonAggregateColumn
AnotherTable has columns: PrimaryKey, ForeignKey, AnotherNonAggregate, YetAnotherNonAggregate
SomeTable class has inverse one-to-many collection of AnotherTable
AnotherTable has many-to-one reference of SomeTable.
The SQL query gets the data I want, I just want to express it with ICriteria.
Code:
SELECT
table1.NonAggregateColumn,
subquery.SubQueryAggregate1,
subquery.SubQueryAggregate2
FROM
SomeTable AS table1
LEFT JOIN
(
SELECT
table2.ForeignKey,
COUNT(table2.AnotherNonAggregate) AS SubQueryAggregate1,
AVG(table2.YetAnotherNonAggregate) AS SubQueryAggregate2
FROM AnotherTable AS table2
GROUP BY (table2.ForeignKey)
) AS subquery ON subquery.ForeignKey = table1.PrimaryKey
It is clear that using Projection subquery is not very efficient, since SQL has to scan the table twice (one projection subquery per aggregate).
Using multiple GROUP BYs is not efficient as well.
Is there a solution for this ? So far I've been resorting to using raw SQL but this is getting unwieldy for complex reports.
I imagined at first that NHibernate would have implemented this via an overload on CreateAlias that took a DetachedCriteria parameter, but it wasn't there.
Thanks lot in advance...
Login
FAQ
Search