ActiveRecord custom SQL result automapping

Question

In Castle ActiveRecord, I'm querying against the database with session.CreateSQLQuery(MyQueryString).List()

When the result columns of a query do not match 100% to the fields of any of my database tables, I cannot use an ActiveRecord-based class to automap the results into a strongly-typed object.

Is there a way to get the results of an ad hoc query mapped into a custom type (perhaps through ActiveRecord attributes on my target class?) without having to map field-by-field at in the query executing/processing code? I'm thinking something similar to:

MyQueryResultType[] results = session.CreateSQLQuery(MyQueryString).List().MapFieldsAs<MyQueryResultType>();

score 1 · Accepted Answer · edited May 23 '17 at 12:11

I haven't used ActiveRecord in awhile, but from what I remember, it uses NHibernate underneath, which can handle this. The following is some code shamelessly ripped from another Stack Overflow question/answer:

IList<MyObj> reults = Session.CreateQuery("select r.feedname as feedname, count(r.feedurl) as feedcount from rsssubscriptions r group by r.feedname")
                                                .SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(MyObj)))
                                                .List<MyObj>();

While there isn't a "MapFieldAs" function, there is a "SetResultTransformer" function. This function allows you to pass an object that describes what should be done once you get the results back from the database. In order to tell the query to take the data and map the columns onto a list of strongly-typed objects, you would have to define a transformer object that does just that. Luckily, NHibernate also has a generic transformer object that you can use (NHibernate.Transform.Transformers.AliasToBean).

Here are some answers on stack overflow with examples:

NHibernate: returning a strongly typed list having applied an aggregate function

Using Unmapped Class with NHibernate Named Query

ActiveRecord custom SQL result automapping

1 Answers1