How can I generate Nhibernate query using Criteria for the following SQL query

Question

I have a sql query as below:

SELECT * 
FROM   USER R 
       INNER JOIN userdepartment C 
               ON R.id = C.userid 
       INNER JOIN userdepartment K 
               ON C.userid = K.userid 
WHERE  C.departmentid = 155 
       AND K.departmentid = 157

User and Department have Many-To-Many relation and to maintain the relation, I have UserDepartment table. Now I want to fetch only those Users who have both Departments with Id 155 and 177. The above query does what I want.

Now I want to convert the above query into an Nhibernate query. I am using Criteria to generate queries. I have tried the following:

    var criteria =    Session.CreateCriteria<User>();
    var subcriteria = criteria
                      .CreateCriteria("UserDepartments","a", JoinType.InnerJoin);
    subcriteria = subcriteria
                       .CreateCriteria("UserDepartments", "b", JoinType.InnerJoin);
    var conjunction = Restrictions.Conjunction();
    conjunction.Add(Restrictions.Eq("DepartmentId", 155));
    conjunction.Add(Restrictions.Eq("DepartmentId", 177));
    subCriteria.Add(conjunction);

But this does not work. Please help me with the same. Thanks in advance for your help.

You should define the relation in your configuration, not try to recreate it in your query — Panagiotis Kanavos, Jul 11 '13 at 13:35
@PanagiotisKanavos Thanks for your reply. I will try defining the relation — Debabrata Paul, Jul 12 '13 at 05:52

score 0 · Answer 1 · answered Jul 11 '13 at 15:06

0

Your query not working because the userdepartment to userdepartment relation not exists in the userdepartment in the configuration. you have to create a children of userdepartment in the userdepartment. Then you can access that children. This is because nhibernate use relations defined in the configuration to traverse.

answered Jul 11 '13 at 15:06

Sebin Eapen George

205
1
8

I will try creating a children of UserDepartment in UserDepartment. Thanks for your reply. – Debabrata Paul Jul 12 '13 at 05:53
Please post how you defined the map of userdepartment entity – Sebin Eapen George Jul 19 '13 at 04:57

How can I generate Nhibernate query using Criteria for the following SQL query

1 Answers1