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 shouldn’t retrieve it twice (i.e select columnx as x1, columnx as x2…..).
But not in this case!! It just didnt’ work!
I had no explanation for this, except when I looked closely to the map file, I noticed that the field that was causing the problem “FaultID” was written once with capital d (D), and the other with small d (d)!
So as it appears, NHibernate has schizophrenia when it comes to database columns case sensitivity; because SQL itself is case insensitive, but NHibernate code distinguishes between the uppercase and lowercase.
Keep an eye on your map files, try to make them EXACTLY the case like the database is, and unify that through all your map files.
UPDATE:
the effect SetMaxResults() produced is it wrapped the original SQL sentace with “WITH query AS (…“, and only then the SQL refused the duplicate columns in the result, hence 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_…“