Multiple Subqueries With QueryOver

Question

I need help converting this sql query into QueryOver Nhibernate criteria.

select distinct * from event e where e.name like '%req%' 
or e.Id in (select r.eventId from requirement r where r.name like '%req%') 
or e.Id in (select r.eventId from requirement r where r.id 
in (select s.requirementId from solution s where s.name like '%sol%'))

var queryOver = session.QueryOver<Event>()
                       .Where(x => x.Name.IsInsensitiveLike("%"+searchTerms[1]+"%"))
                       .OrderBy(x => x.CreatedOn).Asc;

So far I have the main query but couldn't find enough reference material on how to add the subqueries. Haven't been successful using joinQueryOver.

Event has one-to-many rel with requirement and requirement has one-to-many rel with solution.

  Requirement reqAlias = null;
  Solution solAlias = null;

  var subQuery = QueryOver.Of<Event>()                                
      .JoinAlias(x => x.Requirements, () => reqAlias)
      .Where(x => x.Name.IsInsensitiveLike(searchTerms[2]))
      .JoinAlias(() => reqAlias.Solutions, () => solAlias)
      .Where(x => x.Name.IsInsensitiveLike(searchTerms[3]))
      .Select(Projections.Group<Event>(x => x.Id));

  var events = session.QueryOver<Event>()
      .Where(x => x.Name.IsInsensitiveLike(searchTerms[1]))
      .WithSubquery.WhereProperty(x => x.Id).In(subQuery)
      .List().ToList();

still not working.

rae1 rae1 · Accepted Answer · 2013-01-18T02:34:02

When you use IsInsensitiveLike NHibernate appends the % after parsing, and uses lower to do a lower case comparison. In your code, you are appending the % yourself, which results in,

select distinct * from event e where e.name like %lower('%req%')%

which in turn, doesn't work.

Also, you have 3 subqueries, no a big one, so you need to restructure your code to account for that:

select r.eventId from requirement r where r.name like '%req%'

to

var firstQuery = QueryOver.Of<Requirement>()                                
                          .Where(r => r.Name.IsInsensitiveLike(searchTerms[2]))
                          .Select(r => r.EventId);

then,

select s.requirementId from solution s where s.name like '%sol%'

to

var solutionQuery = QueryOver.Of<Solution>()                                
                           .Where(s => s.Name.IsInsensitiveLike(searchTerms[3]));

then,

select r.eventId from requirement r where r.id 
in (select s.requirementId from solution s where s.name like '%sol%')

to

var requirementQuery = QueryOver.Of<Requirement>()
                                .WithSubquery
                                .WhereProperty(r => r.Id).In(solutionQuery)
                                .Select(r => r.EventId);

Then you need to construct the main query using Restrictions.Or to include the 3 queries.

Multiple Subqueries With QueryOver

1 Answers