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

Normally we create 1:1 mapping per table-class.

Ex(开发者_JS百科Tables):

[users]

user_id - PK

name

[transactions]

user_id - FK

item_id

amount

Example mapping:

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 optimization concern and sometimes there are operations needed to be done while querying for results; we usually use stored procedures that returns result from multiple tables. If we use the example above; how can we call a procedure that returns results from the joined tables? Is it possible without creating a new class and binding just for the sake of this combined records?

Thanks!

It is possible to use a stored procedure in this case, using a mapping construct like the following:

<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 is mapped as a bag on the User class. You would 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 usage of custom SQL queries is here.

Cheers, Gerke.