SQL Query in NHibernate diction

Question

I have a SQL Query which works in SQL Management Studio:

Select Id From table t Where t.Date= (Select Max(Date) From ( Select * From table where ReferenceId = xy) u)

Reason is, from all entries with a certain foreign key, I want to receive the one with the highest date.

I tried to reform this Query for use in NHibernate, and I got

IQuery query = session.CreateQuery(String.Format(
            @"Select t.Id  
            From table t
            Where t.Date =
                (Select Max(Date)
                From (Select * 
                      From table t where t.ReferenceItem.Id = " + item.ReferenceItem.Id + ")u)"));

I get the error message: "In expected"

How do I have to form the NHibernate query? What does the "In" mean?

Answer 1

To execute SQL queries, you must use CreateSQLQuery instead of CreateQuery.

The latter creates HQL queries.

Answer 2

the "in expected" means that there is a mismatch on the mapping and the query trying to run.

HQL queries expect tokens as they are mapped to your classes. As such they are very limited to their SQL capabilities: they only allow what the selected Dialect allows.

Answer 3

Ok, I could build the query correctly, it should be

"Select t.Id  
            From table t
            Where t.Date =
                (Select Max(u.Date)
                From table u where u.ReferenceItem.Id = " + item.ReferenceItem.Id + ")"