NHibernate: how to transform List<object> returned by a stored procedure to List<Entity> using IQuery?

Question

Following is my stored procedure:

CREATE PROCEDURE [dbo].[GetProducts_SP]
AS
BEGIN
    SELECT * FROM Products
END

And this is my code:

// This code works
IList<ProductsEntity> list = session.QueryOver<ProductsEntity>().List<ProductsEntity>();

// This also works; I get expected data in `object`
string sql = null;
sql = "EXEC GetProducts_SP";
IQuery query = session.CreateSQLQuery(sql);
IList<object> listSP = query.List<object>();

As session.QueryOver is working fine, there is no problem in entity definition or mapping. That is why, I am not posting table definition, entity class and mappings.

As shown in code above, I properly get the data in List<object> from the stored procedure.
Further, I want to convert this object data to entity.
To do that, I use this code:

//Following fail
IList<ProductsEntity> listSPEntity = query
    .List<ProductsEntity>();

This fails with an exception:

System.ArgumentException: The value "System.Object[]" is not of type "ProductsEntity" and cannot be used in this generic collection.

This seems obvious as NHibernate cannot on itself convert object to entity.
So I used transformer to instruct NHibernate how to do that with below code:

// This fails
IList<ProductsEntity> listSPEntity = query
    .SetResultTransformer(Transformers.DistinctRootEntity)
    .List<ProductsEntity>();

This fails with an exception:

System.ArgumentException: The value "True" is not of type "ProductsEntity" and cannot be used in this generic collection.

How to transform a List<object> returned by the stored procedure to List<Entity> using IQuery?

Answer 1

The Transformers.DistinctRootEntity is surely not right tool here. This transformer expects already prepared entities in results. And without AddEntity call, you have array of objects returned by your procedure like new object[]{10, "Name", true}.

With ISQLQuery you have to manually register returned entity via AddEntity call:

string sql = null;
sql = "EXEC GetProducts_SP";
var results = session.CreateSQLQuery(sql)
                .AddEntity(typeof(ProductsEntity).FullName)
                .List<ProductsEntity>();

Please note AddEntity is available in ISQLQuery returned by CreateSQLQuery. It's not available with IQuery which you use in example.