The other day I wanted to create an HQL query to retrieve data from one object (WorkOrderFault) that has many-to-many relation with another. so I created the following:

ISession session = NHibernateOrmSessionFactory.CurrentNHibernateSession;

IQuery query = session.CreateQuery(
    "select wof from WorkOrderFault wof join wof.WorkOrderTechnicians as tech where tech.Id = 43334");

IList<WorkOrderFault> objects = query.List<WorkOrderFault>();

The query ran successfully, and I got my results.

Then I wanted to use paging and get certain amount of results starting from certain record, so I added these two lines directly after I instantiated the IQuery object:

query.SetMaxResults(10);
query.SetFirstResult(0);

Simple and nice, but instead I got the following error:

System.Data.SqlClient.SqlException : The column ‘FaultId10_’ was specified multiple times for ‘query'.

When I checked my mapping file of WorkOrderFault, it had the following lines (I am including the lines we are interested in only):

<property name="FaultId" type="System.Int32" column="FaultID" not-null="false" access="field.camelcase-underscore" />

<many-to-one name="Fault" class="GRP.Maintenance.Domain.Settings.Faults" column="FaultId"
             fetch="select" insert="false" update="false" not-found="exception"
             access="field.camelcase-underscore" />

Ok, I know it’s wrong to map the same column for two different properties (don’t ask about the reason), but this is the current situation; one property to hold the Id (as an integer), and another property to hold everything. There might be more justifying situations where you want to map two properties to one column, so let’s assume it’s ok.

NHibernate is smart enough, when using queries, to query the database field only once; in situations like this NHibernate figures out that there are two properties mapped to one column so it should not retrieve it twice (for example: select columnx as x1, columnx as x2 …). But not in this case. It just did not work.

I had no explanation for this, except when I looked closely to the map file, I noticed that the field causing the problem, “FaultID”, was written once with capital D and the other with small d.

So as it appears, NHibernate behaves inconsistently when it comes to database column case sensitivity; SQL itself is case-insensitive, but NHibernate code distinguishes between uppercase and lowercase.

Keep an eye on your map files. Try to make them match the database casing exactly, and unify that through all your map files.

UPDATE: The effect produced by SetMaxResults() is that it wraps the original SQL sentence with “WITH query AS (…)”. Only then SQL refused the duplicate columns in the result, so the SqlException took place.

Original SQL:

select workorderf0_.RecID as RecID10_, workorderf0_.WorkOrderID....

SQL after SetMaxResults:

WITH query AS (
  SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__,
         workorderf0_.RecID as RecID10_...
)