Selecting from a subquery instead of an entity with NHibernate

Question

Is this possible using QueryOver or CreateCriteria:

SELECT FooName, SUM(FooAmount)
FROM (
     SELECT
         (SELECT Name FROM Foo
         WHERE Foo.BarId = Bar.Id) as FooName,
         FooAmount
     FROM Bar
) as inner_query
GROUP BY FooName

Please note that this query has been constructed for demonstration purposes. I am not interested in adding relationships between entities, rewriting the query itself etc. Also, the app is setup to take advantage of query construction using CreateCriteria - it is not possible to change it to HQL.

It is possible to construct the inner query in NHibernate as a detatched criteria. The problem is getting NHibernate to select from the inner query, instead of over an entity. Does anyone know if this is possible?

Radim Köhler · Accepted Answer · 2013-07-05 05:09:39Z

1

NHibernate - as typical ORM tool - provides query/domain manipulation language(es) (HQL, QueryOver, Criteria...) on top of object model. FROM clause is always defined by the Object mapping.

The options you have

introduce new readonly object mapped to view (or subselect instead of table)
use session.CreateSQLQuery() to get full access to underlying structured query language (SQL)

answered Jul 5, 2013 at 5:09

Radim Köhler

124k48 gold badges243 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Selecting from a subquery instead of an entity with NHibernate

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related