NHibernate issues redundant queries with composite keys

For the sake of the example, let's say that I have to model the "person" entity of the database of my country's revenue service, and that in my very small country the first name and the last name of a person are enough to uniquely identify the person. Additionally, the revenue service's database does not use surrogate keys, and adding a surrogate key to it would zero out the GDP of the country for the next 10 years.

The Persons table has three fields:

• FirstName
• LastName
• CurrentAddress

And, given the size of my country, the table has a unique constraint on the <FirstName, LastName> pair of columns.

Given this schema, my very simple Person class has the following members:

• Key: an instance of a PersonKey class, which in turn has FirstName and LastName members, and of course implements Equals() and GetHashCode();
• CurrentAddress: a simple string.

The NHibernate mapping looks as follows:

<class name="Person" table="Persons" lazy="false">    

  <composite-id name="Key" class="PersonKey">
    <key-property name="FirstName" type="string" column="FirstName"/>
    <key-property name="LastName" type="string" column="LastName"/>
  </composite-id开发者_开发技巧>

  <property name="CurrentAddress" type="string" column="CurrentAddress" not-null="true" />

</class>

So far so good, this mapping works fine and I can happily load Person entities from the DB.

However, when I look under the hood, I can see that when loading the entire set of persons, NHibernate does the following:

1. Opens a recordset to load key properties only (i.e. exclusively the FirstName and LastName fields) from the Persons table;
2. For each <FirstName, LastName> pair loaded from Persons, it issues a SELECT - of course against Persons as well - to load the CurrentAddress for the person having that FirstName and LastName.

In other words, NHibernate is first loading the keys, and then it issues a series of SELECT's to load each Person separately providing the key in the WHERE clause.

Provided that I am not interested in writing to the database, is there a way to tell NHibernate that it could use a single recordset to retrieve both key and non-key properties from the table?

IQuery.Enumerable has the behavior you mentioned in your comment (loads the keys first, the elements on MoveNext)

In any case, NH is not designed for the mass-processing scenario you are trying to create.

You'll have much better performance by using a raw DataReader.