SQL Query in NHibernate diction

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?

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

The latter creates HQL queries.

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.

You should verify your mapping file properties to see if you're using Embedded Resourse, otherwise, you'll keep getting this error

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 + ")"