Nhibernate HQL where IN query

Question

Im trying to return a SimpleQuery list that queries a single table and uses IN. I can get this to work using

return new List<Jobs>(
    ActiveRecordMediator<Jobs>.FindAll(Expression.In("ServiceId", ids))
);

However this is really really really slow. So id like to do something like this

SimpleQuery<Job> query = 
    new SimpleQuery<Job>(@"from Job as j where ? in (j.ServiceId)", ids);

return new List<Job>(query.Execute());

However I cant get the SimpleQuery to work. I cant find any documentation covering this and was hoping someone out there would be able to help.

Thanks

Answer 1

Have a look at the NHibernate HQL documentation here.

I'm guessing from your code, that you're after a HQL query to return all jobs where the job.ServiceID in a list of ids.

Maybe something along the lines,

IQuery q = s.CreateQuery("from Job as j where j.ServiceId in (:serviceIds)");
q.SetParameterList("serviceIds", ids); 

BTW, have you heard of the NHibernate Lambda Extensions project? Below is an example of the IN query done using the the mentioned library. Might be something interesting to look at as an alternative to using HQL.

DetachedCriteria after =
    DetachedCriteria.For<Person>()
        .Add(SqlExpression.In<Person>(p => p.Name, 
          new string[] { "name1", "name2", "name3" }));