NHibernate - Execute SQL to populate DTO

Question

I have some instances for reporting where executing sprocs is easier and simpler than complicated QueryOver statements.

I have a DTO, not an entity, that represents the data returned from the query and want to populate the results of the query into the DTO. I am using named queries and session.GetNamedQuery() to execute the query.

1. Do I have to create mapping files for the DTO?
2. If so, is is possible to let NHibernate/FluentNHibernate know that it should not create tables for the DTO? My units tests drop and create the schema using NH's SchemaExport tool and don't want to create tables for the DTO's

Please note I don't want to project a QueryOver/Linq query using Projections and AliasToBean - I need to execute the stored procedure.

Cheers

Answer 1

With CreateSQLQuery, the following would work without any mapping file. Maybe you can give it a try with named queries :

public class YourDto
{
    public int YourDtoId { get; set; }
    public string YourDtoTitle { get; set; }
}

then

var result = yourNhSession
    .CreateSQLQuery("select yourColumn1 as YourDtoId, yourColumn2 as YourDtoTitle from YOUR_TABLE")
    .SetResultTransformer(Transformers.AliasToBean<YourDto>())
    .List<YourDto>();

Answer 2

If you want the simplest solution I suggest you add in your architecture a micro / orm like Dapper to do this. It is just dropping a single file in your project and follow the sample. In order to me it is the best solution to pair to NH when you have to do the thinghs that are for some reason out of the entity logic.