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?

Community · Accepted Answer · 2017-05-23 10:33:45Z

2

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 )

edited May 23, 2017 at 10:33

CommunityBot

11 silver badge

answered Sep 9, 2016 at 5:17

Radim Köhler

124k48 gold badges243 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

afym Over a year ago

Thank a lot. Transformers is the solution for this case.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related