Search code examples
nhibernatecomposite-keycomposite-id

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?

Solution

  • 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.