CODEWITHSUNDEEP
c#sqlnhibernatesubqueryicriteria

Reputation:

NHibernate - joining on a subquery using ICriteria

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?

Upvotes: 2

Views: 2403

Answers (2)

Anand
Anand

Reputation: 757

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

Upvotes: 1

Stuart Childs
Stuart Childs

Reputation: 3305

As far as mapping the relationship, it sounds like you have a one-to-many relationship between User and DailyInfo:

<!-- User mapping -->
<bag name="DailyInfos" inverse="true">
    <key column="UserAnchorId_FK" />
    <one-to-many class="Namespace.To.DailyInfo, Namespace" />
</bag>

<!-- DailyInfo mapping -->
<many-to-one name="User" column="UserAnchorId_FK" />

As for the rest, I'm not entirely sure at the moment... it sounds like you may have a many-to-many between User and Group via the GroupMember table, which may be a complicating factor. Keep in mind that you can do .CreateCriteria("Association path", jointype) on a criteria to create a sub-criteria with the specified join type. Posting a rundown of your class/table relationships might help.

Upvotes: 0

Related Questions