How to do an NHibernate QueryOver with "not in"?

Question

Is there a way to make this query easier with NHibernate?

For the understanding, this is the query which I want to create with NHibernate:

Select * from Task
Where task_id not in
(Select task_id from UserTask
Where solved = 1 AND [user_id] = 1)

And this one is my Code in C# with NHibernate

public IList<Task> RandomTasks(List<int> subject_ids, int userId)
{
    //Gets all Ids where the User has solved the Task
    List<int> task_ids = new List<int>(_session.Query<UserTask>()
                       .Where(x => x.User.user_id == userId)
                       .Where(x => x.solved)
                       .Select(x => x.Task.task_id));

    //Gets all Tasks except the task_ids from the result of the query befor
    var query = _session.QueryOver<Task>()
                       .Where(s => s.Subject.subject_id.IsIn(subject_ids))
                       .Where(t => !t.task_id.IsIn(task_ids))
                       .Take(10)
                       .List();

    return query;
}

The query returns the correct result but I think there is maybe an easier way to get the same result.

Answer 1

If you would prefer INNER SELECT, NHibernate does have the solution for you. It is called DetachedCriteria. (try to check here for similar example)

So firstly we will create the inner select:

var sub = DetachedCriteria
  .For<UserTask>()
  // WHERE 
  .Add(Restrictions.In("UserId", new int[] { 1, 2 })) // example of filtering 'user_id'
  .Add(Restrictions.Eq("solved", true))               // 'solved'
  .SetProjection(Projections.Property("TaskId")); // TaskId the SELECT clause

(I am not sure about your model and naming e.g. task_id vs TaskId... but the intention should be clear)

With DetachedCritera we can do a lot, we can join other referenced objects/tables, filter them... as with standard QueryOver. The only difference is, that we should return Projection (SELECT TaskId) to be used as a filter in another query:

var criteria = session.CreateCriteria<Task>();
criteria
  . Where(...                             // your filters applied on Task
  .Add(Subqueries.PropertyIn("ID", sub)); // Task.ID in (select...

var result = criteria.List();

NOTE. This solution will not only work ;), but mostly, it is ready for paging and sorting. So even in cases, that the inner select will return more 'similar' results (same ids), the upper select will return each task only once...

For more information: 15.8. Detached queries and subqueries