2

How could I replace the HQL query below using QueryOver API?

var sql = "from Role r, Action a where r.Active = :active and a.Active = :active";
var result = manager.Session.GetISession().CreateQuery(sql)
            .SetBoolean("active", true).List();
Andrew Whitaker
  • 124,656
  • 32
  • 289
  • 307
tilonthuduc
  • 45
  • 9

1 Answers1

3

I don't believe there's a way to do this in QueryOver, since both JoinAlias and JoinQueryOver require an expression describing a path to the related entity.

However, this is easy to accomplish in LINQ-to-NHibernate:

var result = 
    (from role in manager.Session.GetISession().Query<Role>()
    from action in manager.Session.GetISession().Query<Action>()
    where role.Active == true && action.Active == true).ToList();

With NH 3.2, here's the SQL I get:

select role0_.Id    as col_0_0_,
       action1_.Id as col_1_0_
from   [Role] role0_,
       [Action] action1_
where  role0_.IsActive = 1 /* @p0 */
       and action1_.IsActive = 1 /* @p1 */
Andrew Whitaker
  • 124,656
  • 32
  • 289
  • 307
  • Though the solution above avoids magic-strings issue of projections but I dont belive it is the good one. This is because the above statement will lead the NHibernate to querry the database many times. Let's say Role table has 4 records, Action table has 6 records. The first query NHibernate executes is to return 4 records from Role table. Then for each returned record, NHibernate will read the database to get 6 records from Action table. In other word, the number of routes to the database are equal to the number of returned records from the first query and adding to one. – tilonthuduc Mar 14 '12 at 03:38
  • Andrew, I tried and the result is what I described above. Could you do the same? – tilonthuduc Mar 14 '12 at 03:46
  • @tilonthuduc: I posted the SQL I get--Could it be the NH version? – Andrew Whitaker Mar 14 '12 at 12:56