How to make NHibernate QueryOver with not in?

Question

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

For understanding, this is a query that I want to create using NHibernate:

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

And this 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 an easier way to get the same result.

Answer 1

If you prefer INNER SELECT , NHiberante has the solution for you. It is called DetachedCriteria . (try checking here for a similar example)

So, first create an internal selection:

 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'm not sure about your model and name, e.g. task_id vs TaskId ... but the intent should be clear)

With the help of DetachedCritera we can do a lot, we can join other reference objects / tables, filter them ... as with the standard QueryOver. The only difference is that we have to return Projection (SELECT TaskId), which will 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 basically, it is ready for paging and sorting. Therefore, even in cases where the internal selection returns more “similar” results (the same identifiers), the top selection will return each task only once ...

For more information: 15.8. Separate queries and subqueries

Answer 2

You can use left join :

 Select t.* from Task t left join UserTask ut on t.Id = ut.TaskId and ut.UserId = 1 and ut.solved = 1 where ut.id is null