nHibernate collections and alias criteria

Question

I have a simple test object model in which there are schools schools have a collection of students.

I would like to retrive a school and all its students who are above a certain age.

I carry out the following query in the following manner :

    public School GetSchoolAndStudentsWithDOBAbove(int schoolid, DateTime dob)
    {
        var school = this.Session.CreateCriteria(typeof(School))
                    .CreateAlias("Students", "students")
                    .Add(Expression.And(Expression.Eq("SchoolId", schoolid), Expression.Gt("students.DOB", dob)))
                    .UniqueResult<School>();
        return school;
    }

Which obtains a given school and the children which are above a certain age.

This all works fine and I can see the query going into SQL, which I run and returns the expected number of rows.

However when I carry out either of the following

        foreach (Student st in s.Students)
        {
            Console.WriteLine(st.FirstName);
        }

        Assert.AreEqual(s.Students.Count, 3);  

It gives me the total number of students in the given school regardless of the preceeding request by running another query.

Answer 1

Unfortunately s.Students will not contain your "queried" results. You will have to create a separate query for Students to reach your goal.

foreach(var st in s.Students.Where(x => x.DOB > dob))
     Console.WriteLine(st.FirstName);

Warning: That will still make second trip to the db depending on your mapping, and it will still retrieve all students.

I'm not sure but you could possibly use Projections to do all this in one query, but I am by no means an expert on that.

Answer 2

You do have the option of filtering data. If it there is a single instance of the query mxmissle option would be the better choice.

Nhibernate Filter Documentation

Filters do have there uses, but depending on the version you are using there can be issues where filtered collections are not cached correctly.

Answer 3

You made your query on the School class and you restricted your results on it, not on the mapped related objects.

Now there are many ways to do this. You can make a static filter as IanL said, however its not really flexible. You can just iterate the collection like mxmissile but that is ugly and slow (especially considering lazy loading considerations)

I would provide 2 different solutions: In the first you maintain the query you have and you fire a dynamic filter on the collection (maintaining a lazy-loaded collection) and doing a round-trip to the database:

var school = GetSchoolAndStudentsWithDOBAbove(5, dob);
IQuery qDob = nhSession.CreateFilter(school.Students, "where DOB > :dob").SetDateTime("dob", dob);
IList<Student> dobedSchoolStudents = qDob.List<Student>();

In the second solution just fetch both the school and the students in one shot:

object result = nhSession.CreateQuery(
    "select ss, st from School ss, Student st 
    where ss.Id = st.School.Id and ss.Id = :schId and st.DOB > :dob")
    .SetInt32("schId", 5).SetDateTime("dob", dob).List();

ss is a School object and st is a Student collection.

And this can definitely be done using the criteria query you use now (using Projections)