How to call a procedure using NHibernate that returns a result from multiple tables?

Question

Usually we create a 1: 1 mapping for each table class.

Ex (tables):
[users]
user_id - PC
name

[transaction]
user_id - FK
item_id
quantity


Display example:
public class User
{
public string ID {get; set;}
public string Name {get; set;}
}


public class Transaction
{
public string UserID {get; set;}
public string ItemID {get; set;}
public Decimal Amount {get; set;}
}


But due to concerns about optimization, and sometimes there is a need to perform operations when requesting results; we usually use stored procedures that return results from multiple tables. If we use the example above; how can we call a procedure that returns results from joined tables? Is this possible without creating a new class and binding only for these combined records?

Thanks!

Answer 1

In this case, you can use the stored procedure using the display construct, such as:

<sql-query name="LoadUsersAndTransactions" xml:space="preserve"> <return class="User" alias="u"> <return-property name="ID" column="user_id" /> <return-property name="Name" column="name" /> </return> <return-join property="u.Transactions" alias="t"> <return-property name="key" column="user_id" /> <return-property name="element" column="item_id" /> <return-property name="element.id" column="item_id" /> <return-property name="element.Amount" column="amount" /> </return-join> EXEC dbo.SelectUsersAndTransactions :param_1, ..., :param_N </sql-query> 

This example assumes that transactions are displayed as a package in the User class. You should use this query as follows from C #:

 IList<User> users = session .GetNamedQuery("LoadUsersAndTransactions") .SetString("param_1", parameterValue1) ... .SetString("param_N", parameterValueN) .List<User>(); 

NHibernate documentation on using custom SQL queries here .

Cheers, Gerka.