NHibernate version: 2.0.1.4
Name and version of the database you are using: SQL Server 2005

I have a Product entity that is mapped to a view in SQL Server - it has several properties - int ID, string Name, string Brand, string Category.

I have a stored procedure which searches the view in SQL Server - it is performing some logic such as assigning search relevance, and is not suitable for converting to NHibernate criteria API.

The stored procedure returns three result sets:

A list of the current page of Product entities,
A list of distinct Brands in the search results (not just the current page),
A list of distinct Categories in the search results (not just the current page)

I would rather not call two or three separate procedures since that would duplicate the work the SQL server is doing to generate the search result list.

Is there some way to send NHibernate a data reader, and have it return back a list of entities?

If not, is there same way to have NHibernate call a stored procedure, and process multiple resultsets returned into multiple lists of entities? (with different entity types)

I would appreciate any help anyone could offer here. If I have not provided enough information please let me know and I will give any additional information I can.

Thanks,

Scott

My first try would be a SQL query with hibernate (CreateSQLQuery("exec ...")). Look what you get as a result set. Then I would have a look at result set transformers (query.SetResultSetTransformer(...)). There should be an interface for writing your own one.

You can also have a look at multi criteria/query. Maybe there is something you can use:

http://ayende.com/Blog/archive/2007/05/20/NHibernate-Multi-Criteria.aspx.