NHibernate - joining on a subquery using ICriteria

Posted by owensymes.mp on Stack Overflow See other posts from Stack Overflow or by owensymes.mp
Published on 2009-05-13T04:57:41Z Indexed on 2010/04/05 22:03 UTC
Read the original article Hit count: 566

Filed under:
|
|
|
|

I have a SQL query that I need to represent using NHibernate's ICriteria API.

   SELECT u.Id as Id, 
    u.Login as Login, 
    u.FirstName as FirstName, 
    u.LastName as LastName, 
    gm.UserGroupId_FK as UserGroupId,
    inner.Data1,
    inner.Data2,
    inner.Data3
    FROM dbo.User u inner join 
    dbo.GroupMember gm on u.Id = gm.UserAnchorId_FK 
    left join
    (
        SELECT
        di.UserAnchorId_FK,
        sum(di.Data1) as Data1, 
        sum(di.Data2) as Data2, 
        sum(di.Data3) as Data3
        FROM
        dbo.DailyInfo di 
        WHERE di.Date between '2009-04-01' and '2009-06-01' 
        GROUP BY di.UserAnchorId_FK
    ) inner ON inner.UserAnchorId_FK = u.Id
    WHERE gm.UserGroupId_FK = 195

Attempts so far have included mapping 'User' and 'DailyInfo' classes (my entities) and making a DailyInfo object a property of the User object. However, how to map the foreign key relationship between them is still a mystery, ie

<one-to-one></one-to-one>

<one-to-many></one-to-many>

<generator class="foreign"><param name="property">Id</param></generator> (!)

Solutions on the web are generally to do with subqueries within a WHERE clause, however I need to left join on this subquery instead to ensure NULL values are returned for rows that do not join.

I have the feeling that I should be using a Criteria for the outer query, then forming a 'join' with a DetachedCriteria to represent the subquery?

© Stack Overflow or respective owner

Related posts about nhibernate

Related posts about icriteria