How can I efficiently retrieve linked rows in NHibernate?

Assume I had these tables in my database that is queried by a web service (WCF) via NHibernate:

Table "Patients"
  Column "Id"
  Column "LastName"
  Column "FirstName"
  Column "Age"

Table "Treatments"
  Column "Id"
  Column "PatientId"
  Column "Name"
  Column "IsActive"

A pat开发者_开发百科ient can have any number of treatments. If I wanted to gather a list of patients with their currently active treatments, how could I most efficiently achieve that in NHibernate?

Currently, I'm doing this (with SetFirstResult() and SetMaxResults(), of course):

var patientsWithTreatments = new List<PatientWithTreatments>();

var patients = Session.CreateCriteria<Patient>().List<Patient>();
foreach(Patient patient in patients) {
  patientsWithTreatments.Add(
    new PatientWithTreatments(patient) {
      Treatments = Session.CreateCriteria<Treatment>()
        .Add(Restrictions.Eq("PatientId", patient.Id)).List<Treatment>()
    }
  );
}

Which involves a round-trip to the database per patient in the result set, which is a pretty bad idea. Is there some magic with plain NHibernate queries or will I have to use HQL (which I don't have a firm grasp of yet) :)

You basically need to have a collection in your Patient class called Treatments. In your nHibernate mapping you should have something like:

<many-to-one name="Treatments" column="TreatmentID" class="Treatment" fetch="select" cascade="none"/>

Then you can retrieve all the Treatments from the Patient by:

List<Treatment> treatments = yourPatient.Treatments;

NHibernate has the option to add relationships behind the scenes (meaning they're known to NHibernate but not exposed to code) via access="noop" in the mapping file.

These can then supposedly be used like any normally declared relationship in HQL.

As of NHibernate 3.1.0, for me this had the slightly inconvenient side-effect of completely deleting all of the referenced rows from the other table right when the query is run, so you might want to double-check before using access="noop" in production code.