Test for CreateSQLQuery

I use NHibernate CreateSQLQuery method to get DTO objects of my departments.

string query = @"SELECT * FROM Departments";
IList<Object[]> resultList = Session.CreateSQLQuery(query)...

and it works well. But also I want to write a test and it fails when I write:

 [Test]
 public void CanGetTreeDto()
  {

            IList<DepartmentDto> resultList =  _departmentRepository.GetTreeLi开发者_如何学PythonstDto(idContract);
...
        }

It throws SQLiteException and writes "could not execute query". Could anybody help?

You need ad-hoc mapping:

IList<DepartmentDto> result = Session.CreateSQLQuery(query)
                                     .SetResultTransformer(Transformers.AliasToBean(typeof(DepartmentDto)))
                                     .List<DepartmentDto>();

Also, you might need to be explicit about the columns as well as its names.