如何使用 NHibernate 调用从多个表返回结果的过程? [英] How to call a procedure using NHibernate that returns result from multiple tables?
问题描述
通常我们为每个表类创建 1:1 映射.
Normally we create 1:1 mapping per table-class.
Ex(表格):
[用户]
user_id - PK
姓名
[交易]
user_id - FK
item_id
金额
示例映射:
公共类用户
{
公共字符串 ID {get;设置;}
公共字符串名称{get;设置;}
}
公共类事务
{
公共字符串用户 ID {get;设置;}
公共字符串 ItemID {get;设置;}
公共十进制数量{get;设置;}
}
但出于优化考虑,有时在查询结果时需要进行操作;我们通常使用从多个表返回结果的存储过程.如果我们使用上面的例子;我们如何调用从连接表返回结果的过程?是否可以不创建一个新的类并仅仅为了这个组合记录而绑定?
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?
谢谢!
推荐答案
在这种情况下可以使用存储过程,使用如下映射结构:
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>
这个例子假设 Transactions 被映射为 User 类上的一个包.您可以在 C# 中按如下方式使用此查询:
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>();
关于使用自定义 SQL 查询的 NHibernate 文档是 此处.
NHibernate documentation on usage of custom SQL queries is here.
干杯,格克.
这篇关于如何使用 NHibernate 调用从多个表返回结果的过程?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!