public partial class Classroom
{
public Classroom()
{
this.Students = new List<Student>();
}
public virtual Long Id { get; set; }
public virtual Int MaxCapacity { get; set; }
public virtual IList<Student> Students { get; set; }
}

public partial class Student
{
public virtual Long Id { get; set; }
public virtual String FirstName { get; set; }
public virtual String LastName { get; set; }
public virtual StudentType StudentType { get; set; }
}

public partial class StudentType //Male/Female
{
public virtual Long Id { get; set; }
public virtual String Value { get; set; }
public virtual String Desciption { get; set; }
}

Above are the 3 classes that I have. I am trying to write an NHibernate query in .NET 4.0. I want the query to get a result of a new object that looks like the class below. I want to be able to do a search for a student in any classroom, and get any student that matches my search criteria, and for each student that matches, i want to get the opposite sex of that student thats in that specific Classroom, and the first in the sequence of the classroom that is the opposite sex.

public partial class Result
{
Classroom.Id
Student.Id //of the first Type
Student.Id //of the other type
}

Classroom
Id MaxCapacity
1 30
2 25
3 20

Student
Id FirstName LastName Sequence StudentType_Id Classroom_Id
1 krista Hope 1 2 1
2 jim smith 2 1 1
3 jamie smith 3 2 1
4 john joe 4 1 1
5 noah smith 1 1 2
6 julie blah 2 2 2

StudentType
Id Value Description
1 Male Male
2 Female Female

If I search for a student by the last name of smith the results i should get are as follows:

Result
Classroom_Id Student_Id OppositeStudent_Id
1 2 1
1 3 2
2 5 6

If you are confused in any way please ask, the query im trying to get is very advanced, and im not even sure if it can be done with NHibernate, but i really want some smarter people than me to give it a shot before i go the route of stored Procedures.

Thanks Everyone