nHibernate collections and alias criteria

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

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

I carry out the following query, which obtains a given school and the children which are above a certain age:

    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)))开发者_Go百科
        .UniqueResult<School>();

      return school;
    }

This all works fine and I can see the query going to the database and returning the expected number of rows.

However, when I carry out either of the following, it gives me the total number of students in the given school (regardless of the preceding request) by running another query:

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

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

Can anyone explain why?

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)

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.

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.