Selecting only a few columns from a table using NHibernate and Native SQL

Question

I am developing a web app using NHibernate using C# language. However I can not build a native MySQL Query only with few fields and then mapping.

My hbm.xml looks like :

  <class name="Rule" table="rule">
    <id name="Id" column="id" type="int">
      <generator class="native"></generator>
    </id>
    <property name="Name" column="name" type="String" not-null="false"></property>
    <property name="Description" column="description" type="String" not-null="false"></property>
    <property name="Shops" column="shops" type="String" not-null="false"></property>
    <property name="Channels" column="channels" type="String" not-null="false"></property>
    <property name="Currency" column="currency" type="int" not-null="false"></property>
    <property name="Range" column="range" type="int" not-null="false"></property>
    <property name="Created" column="created" type="DateTime" ></property>
    <property name="Modified" column="modified" type="DateTime" ></property>
  </class>

My native query looks like :

 var session = this.GetFactory().OpenSession();
            var query = session.CreateSQLQuery("SELECT * FROM `rule` WHERE currency = :currency AND `range` = :range");
            query.SetParameter("currency", this.SearchRoot.Currency.Id);
            query.SetParameter("range", this.SearchRoot.Range.Id);
            query.AddEntity(typeof(Rule));

            var rules = query.List<Rule>();

When I run my app all is fine. However for this particular case I don't need all the fields I just need id, shops and channels so I did the following change:

 var session = this.GetFactory().OpenSession();
            var query = session.CreateSQLQuery("SELECT id, shops, channels FROM `rule` WHERE currency = :currency AND `range` = :range");
            query.SetParameter("currency", this.SearchRoot.Currency.Id);
            query.SetParameter("range", this.SearchRoot.Range.Id);
            query.AddEntity(typeof(Rule));

            var rules = query.List<Rule>();

Then I got the following error :

Exception Details: System.IndexOutOfRangeException: Could not find specified column in results: name

I understand that NHibernate always try to match class properties with table's fields. I read documentation about native query.

nhibernate native query

But I can't find any sample or something related to this particular case.

Any help?

score 2 · Accepted Answer · edited May 23 '17 at 10:33

It is surprisingly easy:

do NOT use AddEntity(typeof(MyEntity)
use Transformer Transformers.AliasToBean<MyEntity>()

BUT, now we have to change the SQL SELECT statement:

column aliases must match to Property name

A drafted solution:

var query = session.CreateSQLQuery("SELECT id as ID, shops as Shop ....");
...
//query.AddEntity(typeof(Rule));
query.SetResultTransformer(Transformers.AliasToBean<Rule>())

Summary... we can map to entity directly, if we provide all columns as they are defined in mapping. Match is driven by column name and column="" mapping. We can also use projectin, and select just few columns... in that case we have to provide alias equal to name="" mapping and use Transformer. (we can use even more complex projection )

Thank a lot. Transformers is the solution for this case. – afym Sep 09 '16 at 15:23 — afym, Sep 09 '16 at 15:23

Selecting only a few columns from a table using NHibernate and Native SQL

1 Answers1