2

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

Ex(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!

eSPiYa
  • 882
  • 1
  • 12
  • 29

1 Answers1

3

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.

Gerke Geurts
  • 632
  • 4
  • 9
  • And more examples of fairly complex custom SQL mappings can be found And more examples of fairly complex custom SQL mappings can be found on http://nhibernate.svn.sourceforge.net/viewvc/nhibernate/trunk/nhibernate/src/NHibernate.Test/NHSpecificTest/NH1612/Mappings.hbm.xml?revision=3974 – Gerke Geurts Jan 09 '11 at 08:32
  • Thanks! But how can I get the result in list of objects(User and Transaction classes)? Sorry, still new in NHibernate. I think I can use GetNamedQuery but what method I need to call to get the result? – eSPiYa Jan 11 '11 at 01:13
  • Sorry for very late response. I just tried it with a slight modification from my post; instead of Transaction, I used Profile. I also used a joined query instead of a procedure(for the meantime). The query works fine by only using return xml element but not when I included the return-join xml element. It just crash when it calls the method BuildSessionFactory. – eSPiYa Jan 26 '11 at 10:33
  • Can you give exception details? – Gerke Geurts Jan 28 '11 at 15:01
  • I made a work-aroud with it but I'm not sure if it is the proper way. Will re-create the project, I just started here in my new work. – eSPiYa Feb 01 '11 at 06:36
  • Ok, I'm finished rebuilding the project, it almost works fine now but there's some kind of issue in returning results. Let's return to my 1st example; the users and transactions tables. I added a filter(amount < 30) which supposed to return only two records so I got two users but when I look at each's Transactions(IList) property it still return all of the transactions of the user disregarded of the filter. – eSPiYa Feb 02 '11 at 06:19
  • Filters and custom SQL do not work together in NHibernate. If you want to limit the maximum number of transactions loaded you would have to do that by modifying the SQL of the LoadUsersAndTransactions query. – Gerke Geurts Mar 21 '12 at 08:16