I am trying to write an HQL statement with a subquery. However, when I attempt to use an alias, I get a "could not resolve property" error. Could someone please take a look and see what I'm missing? Thanks.

The error I'm getting is this:

{"could not resolve property:EmployeeID of :PerformanceManagement.ProductionLaborTransaction [from PerformanceManagement.ProductionLaborTransaction plt where plt.EmployeeID = 244 and plt.Day = '3/28/2006 12:00:00 AM' and plt.TransactionID in(select max(plt2.TransactionID) from PerformanceManagement.ProductionLaborTransaction plt2 where plt2.EmployeeID = 244 and plt2.Day = '3/28/2006 12:00:00 AM' group by plt2.ProductionTaskID, plt2.ProductID)]" }

Here is the query I'm trying to build:

IList list = session.Find(string.Format( "from ProductionLaborTransaction plt where plt.EmployeeID = {0} and plt.Day = '{1}' and plt.TransactionID in" +
"(select max(plt2.TransactionID) from ProductionLaborTransaction plt2 where " +
"plt2.EmployeeID = {2} and plt2.Day = '{3}' group by plt2.ProductionTaskID, plt2.ProductID)",
employeeID, day.ToString(), employeeID, day.ToString())
);

Here is the mapping file:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">

<class name="PerformanceManagement.ProductionLaborTransaction, PerformanceManagement" table="ProductionLabor">
<id name="id" access="field" column="TransactionID" type="Int32" unsaved-value="0">
<generator class="identity" />
</id>

<many-to-one
name="Employee"
access="nosetter.camelcase"
column="EmployeeID"
class="PerformanceManagement.Employee, PerformanceManagement"
cascade="none"
not-null="true" />

<property name="Day" access="nosetter.camelcase" column="`Day`" type="DateTime" />

<many-to-one
name="Task"
access="nosetter.camelcase"
column="ProductionTaskID"
class="PerformanceManagement.ProductionTask, PerformanceManagement"
cascade="none"
not-null="true" />

<many-to-one
name="Product"
access="nosetter.camelcase"
column="ProductID"
class="PerformanceManagement.Product, PerformanceManagement"
cascade="none"
not-null="true" />


<property name="TransactionDate" access="nosetter.camelcase" column="TransactionDate" type="DateTime" />
<property name="Submitter" access="nosetter.camelcase" column="Submitter" type="String" length="50" />
<property name="Minutes" access="nosetter.camelcase" column="Minutes" type="Int32" />
<property name="Units" access="nosetter.camelcase" column="Units" type="Int32" />
<property name="Comments" access="nosetter.camelcase" column="Comments" type="String" length="1024" />

</class>

</hibernate-mapping>

What does the ProductionLaborTransaction class look like? Perhaps the property is spelled differently or something.

Here's the class:

HQL is an object oriented query langugage, so you need to reference the property name (i.e Employee) not the database column name(EmployeeID) in your query.

Since Employee is an object, you actually want to do something like where plt.Employee.Id = {0} ... etc where Id is the property name of the Identifier property of the Employee class.

You can also do an inner join on the Employee object

from PerformanceManagement.ProductionLaborTransaction plt inner join plt.Employee as emp where emp.Id = {0}

I have tried using "plt.Employee.Id" and that gives me the same error.

I have also tried running each query by itself without the aliases and everything is fine. I can reference the EmployeeID column with no problem. It is when I use the aliases that it falls apart.

This works fine:


but this doesn't: