NHibernate的:SQL查询结果映射 [英] NHibernate: SQL query results mapping
问题描述
我有一个SQL查询结果的NHibernate 3.1.0.4000(我使用MS SQL Server 2005数据库)中的问题。我有两个表:广告 code>和
投资
,其分别对应的实体:广告
和投资
。 广告 code>与
投资
通过 InvestmentId
连接柱(和外键)。
I have a problem with a SQL query results in NHibernate 3.1.0.4000 (I'm using MS SQL Server 2005 database). I have two tables: Adverts
and Investments
, which are mapped to entities: Advert
and Investment
. Adverts
are connected with Investments
via InvestmentId
column (and a foreign key).
要救我的查询结果我创建了以下内部类:
To save results of my query I created a following inner class:
class InvestmentWithAdvertsCount
{
public Investment inv { get; set; }
public int cnt { get; set; }
}
和查询是如下:
var investementsWithAdvertCounts = _session.CreateSQLQuery(
"select {inv.*}, (select count(1) from Adverts where InvestmentId = inv.Id) cnt from Investments inv")
.AddScalar("cnt", NHibernateUtil.Int32)
.AddEntity("inv", typeof(Investment))
.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(InvestmentWithAdvertsCount)))
.List<InvestmentWithAdvertsCount>();
当我运行此查询我收到 InvestmentWithAdvertsCount
实例具有正确填写 CNT
属性集合,但在 INV
属性设置为空
。我花了一些时间挖掘到NHibernate的来源,它似乎是的 INV 的别名,以某种方式从查询丢失和NHibernate甚至没有尝试,填补了 INV
属性。如果我删除 .SetResultTransformer
部分我收到阵列的列表(类型目标[2]
)与第一要素设置为投资
实体,并设置其相应的广告数第二个元素。你能告诉我如何改变这个查询,使 InvestmentWithAdvertsCount
填写正确?或者,也许有一种方法可以重写此查询到QueryOver,标准或HQL(保持生成的SQL code高效)?
When I run this query I receive a collection of InvestmentWithAdvertsCount
instances that have a correctly filled cnt
property, but the inv
property is set to null
. I spent some time digging into the NHibernate source and it seems that the inv alias is somehow lost from the query and NHibernate does not even try to fill the inv
property. If I remove the .SetResultTransformer
part I receive a list of arrays (of type object[2]
) with first element set to Investment
entity and second element set to its corresponding adverts count. Could you please tell me how to change this query to make the InvestmentWithAdvertsCount
filled correctly? Or maybe there is a way to rewrite this query to QueryOver, Criteria or HQL (keeping the generated SQL code efficient)?
我会很感激在这个问题上的任何帮助。谢谢
I would be really greatful for any help on this issue. Thanks
推荐答案
下面是一个干净的方式做到这一点,结果非常简单的LINQ处理的对象:
Here's a clean way to do it, processing the results with very simple LINQ to objects:
session.CreateSQLQuery(
"select {inv.*}, (select count(1) ... inv")
.AddScalar("cnt", NHibernateUtil.Int32)
.AddEntity("inv", typeof(Investment))
.List<object[]>()
.Select(x => new InvestmentWithAdvertsCount {inv = x[0], cnt = x[1]})
.ToList();
这篇关于NHibernate的:SQL查询结果映射的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!