C# – NHibernate – joining on a subquery using ICriteria

cicriterianhibernatesqlsubquery

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?

Best Answer

Same problem I encountered and i didn't get any solutions.So i made a hack with interceptor

Query Generated by Criteria

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 
InnerJoin inner ON inner.UserAnchorId_FK = u.Id
WHERE gm.UserGroupId_FK = 195

Here InnerJoin is a dummy table which has 1-1 relation with User. Columns in InnerJoin are return values of inner join sub query

Criteria for User and InnerJoin

DetachedCriteria forUser = DetachedCriteria.For<User>();
forUser.CreateCriteria("InnerJoin");

Now you can make interceptor to edit the query

public interface CustomInterceptor : IInterceptor, EmptyInterceptor
{    
 SqlString IInterceptor.OnPrepareStatement(SqlString sql)
 {
    string query = sql.ToString();
     if (query.Contains("InnerJoin "))
     {
        sql = sql.Replace("InnerJoin ", "(select [vals] form dbo.DailyInfo [where conditions])");
     }
     return sql;
 }
}

For Return values from multiple tables including joined sub query you can have to use NHibernate DTO And the session is like this

CustomInterceptor custonInterceptor=new CustomInterceptor(); 

sessionFactory.OpenSession(custonInterceptor);

And the final query will be like what exactly you want to get

Related Topic