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.

score 25 · Accepted Answer · edited Mar 16 '15 at 20:35

25

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

edited Mar 16 '15 at 20:35

hazzik

13,019
9
47
86

answered May 11 '11 at 19:45

dotjoe

26,242
5
63
77

@dotjoe - I want to return the whole Task object so do I do (x => x.Task)? – chobo2 May 11 '11 at 19:46
@dotjoe - 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. – chobo2 May 11 '11 at 19:48
1

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()` – dotjoe May 11 '11 at 19:50
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. – dotjoe May 11 '11 at 19:51
@dotjoe - I want to select the whole CompletedTask object how do I do that? – chobo2 May 11 '11 at 19:53
@dotjoe - 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. – chobo2 May 11 '11 at 19:55
@dotjoe - 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. – chobo2 May 11 '11 at 20:12
@dotjoe - 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) – chobo2 May 11 '11 at 20:22
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? – dotjoe May 11 '11 at 20:37
@dotjoe - 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 – chobo2 May 11 '11 at 20:42
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? – dotjoe May 11 '11 at 21:01
Maybe I am not sure how would I make a NH filter? – chobo2 May 11 '11 at 21:10
@dotjoe - so I can't make a subquery. I really thought that is what I needed to use. – chobo2 May 11 '11 at 21:27
You could use a subquery in the projection...but then you'd need to project into a DTO instead of returning the List()... – dotjoe May 11 '11 at 21:34
@dotjoe - could you show me an example I tried to do that on different query but it kept erroring out(http://stackoverflow.com/questions/5923007/how-to-select-with-nhibernate-queryover) – chobo2 May 11 '11 at 21:54
@chobo2 I added answer for that question. Check this answer for how to project a subquery http://stackoverflow.com/questions/2478858/how-to-do-this-with-nhibernate-criteria/2479857#2479857 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. – dotjoe May 12 '11 at 13:42

How to do subqueries in nhibernate?

1 Answers1

Linked