NHibernate could not execute query[SQL: SQL not available] in C#

I was writing some new code that would use NHibernate a few days back and while I was testing it I received thie error:

  • Could not execute query[SQL: SQL not available]
  • The value xxx is not of type xxx.xxx.xxx and cannot be used in this generic collection. Parameter name: value
  • de-DE: xxx ist kein Wert des Typs xxx und kann in dieser generischen Auflistung nicht verwendet werden. Parametername: value

I received this error while running this code:

[sourcecode language="csharp" padlinenumbers="true" autolinks="false" gutter="false" toolbar="false"]
IQuery query = session.CreateQuery("select id from company");
IList list = query.List();
int count = list.Count;
[/sourcecode]

It failed on line 2 where I called the List method on the query. The IQuery object looked like the below image. Notice that the ReturnAliases is a string and the ReturnTypes is an int. In my case, the id in the above HQL query is an int. I believe it is failing because of the attempted conversion from int to string.

image

When I changed the code to look like the below, the code executed as expected. Simply because we let the compiler make the decision on the variable type by using the var type.

[sourcecode language="csharp" padlinenumbers="true" autolinks="false" gutter="false" toolbar="false"]
var query = session.CreateQuery("select id from company").List();
int count = query.Count;
[/sourcecode]

As well, this code segment executed as expected:

[sourcecode language="csharp" padlinenumbers="true" autolinks="false" gutter="false" toolbar="false"]
IQuery query = session.CreateQuery("from company");
IList list = query.List();
[/sourcecode]

image

In this case, the IQuery objects’ ReturnAlias was null and the ReturnTypes contained an instance of my class.

The point here is that if you want to select only a subset of your class (projection) using HQL, then you need to capture the results in a var variable.


Leave a Comment

Your email address will not be published.