CreateSqlQuery on entity with formula property

Question

When I try to query a table using CreateSqlQuery and convert it to an entity containing formula property, I get the below Adoexception.

"Value cannot be null. Parameter name: fieldName"

In the first place is it possible to use the createsqlquery on entity that has formula mapping ?

Answer 1

It's possible to use CreateSQLQuery with formulas.

Here's a proof of concept. Mapping (class is not hard to guess):

<class name="Foo">
  <id name="Id">
    <generator class="hilo"/>
  </id>
  <property name="Data"/>
  <property name="DataX2" formula="Data * 2"/>
</class>

And here's a query:

var foos = session.CreateSQLQuery(
                   @"
                   select Id          as {foo.Id},
                          Data        as {foo.Data},
                          Data + Data as {foo.Data2}
                   from   Foo
                   ")
                  .AddEntity("foo", typeof(Foo))
                  .List<Foo>();

If you look closely, you'll see that I'm using a different formula than the one declared in the mapping. NHibernate allows anything as long as all the properties are in the query.

I suggest that you read 17.1.2. Entity queries and the following points.