How to map native SQL query result to a list of custom value types in NHibernate?

Question

I have a value type called FiscalYear which essentially is a wrapper for an integer. I have also implemented the NHibernate.UserTypes.IUserType for it. Now I can map properties of type FiscalYear directly to SQL Types and NHibernate will convert them automatically.

It all works fine. But now I would like to perform a native SQL query to get a list of fiscal years from the database. Here is the current code:

public IEnumerable<FiscalYear> FiscalYears
{
    get
    {
        var result = new List<FiscalYear>();
        foreach (var fiscalYear in Session.CreateSQLQuery(FiscalYearsQuery).List<Int16>())
        {
            result.Add(new FiscalYear(fiscalYear));
        }
        return result;
    }
}

Is works, but my question is: since I have already implemented the IUserType for FiscalYear, is it possible to simplify this code to something like this:

public IEnumerable<FiscalYear> FiscalYears
{
    get
    {
        return Session
            .CreateSQLQuery(FiscalYearsQuery)
            .List<FiscalYear>();
    }
}

Unfortunately this doesn't work. Using Transformers.AliasToBean doesn't help either, since FiscalYear is a value type and not an entity.

I think this is really something that should be easy to do, but all my attempts so far failed. I guess I'm just missing something out?

Answer 1

When you use CreateSQLQuery you must add the types of the returning values, i have never used native sql queries with custom types, but you can try:

return Session
    .CreateSQLQuery(FiscalYearsQuery)
    .AddScalar("*QueryColumnName*", FiscalYear)
    .List<FiscalYear>();

If this not works you can implement IResultTransformer for more elegant way to convert your data.

Answer 2

Have a look at this post:

NHibernateUtil.Custom()

There it is described, that you can use NHibernateUtil.Custom(IUserType)