How can I recreate a SQL statement using NHibernate that has an inner select case?
- by brianberns
I am trying to recreate something like the following SQL using NHibernate criteria:
select Range, count(*) from (
select
case
when ent.ID between 'A' and 'N' then 'A-M'
else 'Other'
end as Range
from Subject
) tbl
group by tbl.Range
I am able to create the inner select as follows:
session.CreateCriteria<Subject>()
.SetProjection(
Projections.Conditional(
Expression.Between("Name", "A", "N"),
Projections.Constant("A-M"),
Projections.Constant("Other")))
.List();
However, I can't figure out how to pipe those results into a grouping by row count.
