How to do subqueries in nhibernate?

Question

I need to do a subquery on a sub collection but I can't get it to work.

I tried this

 Task tAlias = null;
        List<Task> result = session.QueryOver<Task>(() => tAlias)
                                   .Where(Restrictions.In(Projections.Property(() => tAlias.Course.Id), courseIds))
                                   .WithSubquery.WhereExists(QueryOver.Of<CompletedTask>().Where(x => x.Student.StudentId == settings.StudentId))                                     
().ToList();

Yet I get

Cannot use subqueries on a criteria without a projection.

Spontoon

Answer 1

session.QueryOver<Task>(() => tAlias)
    .WhereRestrictionsOn(x => x.Course.Id).IsIn(courseIds)
    .WithSubquery.WhereExists(QueryOver.Of<CompletedTask>()
        .Where(x => x.id == tAlias.id) //not sure how you need to link Task to CompletedTask
        .Where(x => x.Student.StudentId == settings.StudentId)
        .Select(x => x.id)) //exists requires some kind of projection (i.e. select clause)
    .List<Task>();

or if you only want the completedtask then just...

Task taskAlias = null;

session.QueryOver<CompletedTask>()
    .JoinAlias(x => x.Task, () => taskAlias)
    .WhereRestrictionsOn(() => taskAlias.Course.Id).IsIn(courseIds)
    .Where(x => x.Student.StudentId == settings.StudentId)
    .List<CompletedTask>();

or look into setting up a student filter on the Task.CompletedTasks collection. I've never used this feature before. I believe you have to enable the filter and set the student parameter before you run the query. Then your Task object would only contain completedTasks by that student...

http://nhibernate.info/doc/nh/en/index.html#filters

Locket

@Locket - I want to return the whole Task object so do I do (x => x.Task)? – Spontoon

@Locket - why do I need to do a where clause on id's? Why do I need to link Task To completedTask. I just was hoping to query the completedTask down so it only has record with the student. – Spontoon

updated answer. I think that error is only because the WhereExists detached query did not have any projections aka Select(). The main query can just List<Task>() – Locket

You don't have too use the id's in the exists clause. I thought you only wanted task that were completed by the student? If you don't link task to completed task then you'd get all tasks if the student completes at least one task. – Locket

@Locket - I want to select the whole CompletedTask object how do I do that? – Spontoon

@Locket - I thought the first where clause would get all the Tasks that the student has the course Id too. Then the subquery would query filter out all completed tasks where the student id = student id(a task can have many completed tasks from different students). I just want the ones from that student. – Spontoon

@Locket - I still need the task object as well. The completed Task object maybe a collection but if I do my subquery right it should always just have a count of zero in it or a count of one. – Spontoon

@Locket - Sorry it must be confusing trying to figure out everything from the one query I put up. I need to return the task always(as long as the student is part of that course). With your query it only gets the ones back the ones that are completed. I need to show both uncompleted and completed. My plan was to see if the completedTask count is equal 1 and that would mean that the student had completed the task. If it was zero then they did not. I however need all the task information back reguardless if they completed or not since that is just a visual thing(a checkmark) – Spontoon

I see...how is the student linked to the course? Course.Students maybe? Or, do you already have the student's course ids in that array? – Locket

@Locket - Yes I have all the courses the student belong in that array. I tried to do a left join but then the where statement with the Student == student does not work – Spontoon

oh I see the problem now...I think you need to use a NH filter to put extra conditions on a join. NH only wants to join by id but you want to join by id and student? – Locket

Maybe I am not sure how would I make a NH filter? – Spontoon

@Locket - so I can't make a subquery. I really thought that is what I needed to use. – Spontoon

You could use a subquery in the projection...but then you'd need to project into a DTO instead of returning the List<Task>()... – Locket

@Locket - could you show me an example I tried to do that on different query but it kept erroring out(#5923507) – Spontoon

@Spontoon I added answer for that question. Check this answer for how to project a subquery #2479358 You could count the completed tasks for the student and project into an int property on the DTO. The view could then check if this is greater than 0. – Locket