nhibernate使用icriteria与一组和有,并获得总行数(C#)
问题描述:
所以我有一个nhibernate icriteria查询获取我想要的结果列表。nhibernate使用icriteria与一组和有,并获得总行数(C#)
但是我不知道如何获得总行数。
public IEnumerable<SpecialismCombo> List(SpecialismListCriteria criteria, out int total)
{
//run the sub query
ICriteria countAgencies = m_SpecialismComboRepository.QueryAlias("sc");
if (!String.IsNullOrEmpty(criteria.AgencyIds) && !criteria.AgencyIds.Equals("0"))
{
List<int> AgencyIds = criteria.AgencyIds.Split(new char[] { ',' }).ToList<int>();
countAgencies.CreateAlias("Agencies", "a", NHibernate.SqlCommand.JoinType.InnerJoin);
countAgencies.Add(Restrictions.In("a.AgencyId", AgencyIds));
}
total = Convert.ToInt32(
countAgencies.SetProjection(
Projections.CountDistinct("a.AgencyId")
).UniqueResult()
);
//create query
ICriteria query = m_SpecialismComboRepository.QueryAlias("sc");
if (!String.IsNullOrEmpty(criteria.AgencyIds) && !criteria.AgencyIds.Equals("0"))
{
List<int> AgencyIds = criteria.AgencyIds.Split(new char[] { ',' }).ToList<int>();
query.CreateAlias("Agencies", "a", NHibernate.SqlCommand.JoinType.InnerJoin);
query.Add(Restrictions.In("a.AgencyId", AgencyIds));
}
query.Add(Restrictions.Eq(Projections.Count("SpecialismComboId"),total));
ProjectionList pl = Projections.ProjectionList()
.AddPropertyAlias("SpecialismComboId", "SpecialismComboId")
.AddPropertyAlias("SpecialismComboDisplayText", "SpecialismComboDisplayText")
.AddPropertyAlias("SpecialismComboDisciplineDisplayText", "SpecialismComboDisciplineDisplayText")
.AddPropertyAlias("SpecialismComboIdText", "SpecialismComboIdText")
.Add(Projections.GroupProperty("SpecialismComboId"));
// set the right total for records being returned
total = query.ToRowCount();
// Return the query results
return query
.SetProjection(Projections.Distinct(
pl
))
.SetResultTransformer(new KnownPropertyAliasToBeanResultTransformer(typeof(SpecialismCombo)))
.SetFirstResult(criteria.FirstRecord)
.SetMaxResults(criteria.PageSize)
.List<SpecialismCombo>() as List<SpecialismCombo>;
}
我试图用一个ICriteriaExtension我们:
public static int ToRowCount(this ICriteria query)
{
return query.ToRowCountQuery().UniqueResult<int>();
}
但它返回null。
所以在SQL生成的查询是:
exec sp_executesql N'SELECT distinct top 15 this_.SpecialismComboId as y0_,
(select dbo.fn_GetSpecialismComboDisplayText(this_.SpecialismComboId,''/'')) as y1_,
(select dbo.fn_GetSpecialismComboDisciplineDisplayText(this_.SpecialismComboId,'', '')) as y2_,
(select dbo.fn_GetSpecialismComboIdText(this_.SpecialismComboId,''|'')) as y3_,
this_.SpecialismComboId as y4_
FROM dbo.SpecialismCombo this_
inner join AgencySpecialismCombo agencies3_ on this_.SpecialismComboId=agencies3_.SpecialismComboId
inner join dbo.Agency a1_ on agencies3_.AgencyId=a1_.AgencyId WHERE a1_.AgencyId in (@p0)
GROUP BY this_.SpecialismComboId HAVING count(this_.SpecialismComboId) = @p1',N'@p0 int,@p1 int',@p0=4741,@p1=1
行数根据我上面的代码是
exec sp_executesql N'SELECT top 2147483647 count(*) as y0_
FROM dbo.SpecialismCombo this_
inner join AgencySpecialismCombo agencies3_ on this_.SpecialismComboId=agencies3_.SpecialismComboId
inner join dbo.Agency a1_ on agencies3_.AgencyId=a1_.AgencyId WHERE a1_.AgencyId in (@p0)
HAVING count(this_.SpecialismComboId) = @p1',N'@p0 int,@p1 int',@p0=4741,@p1=1
但我需要它是象是这样的:
exec sp_executesql N'SELECT sum(idcount) from
(select count(*) idcount
FROM dbo.SpecialismCombo this_
inner join AgencySpecialismCombo agencies3_ on this_.SpecialismComboId=agencies3_.SpecialismComboId
inner join dbo.Agency a1_ on agencies3_.AgencyId=a1_.AgencyId WHERE a1_.AgencyId in (@p0)
GROUP BY this_.SpecialismComboId HAVING count(this_.SpecialismComboId) = @p1) as sub
',N'@p0 int,@p1 int',@p0=4741,@p1=1
但我再次无法弄清楚如何使用icriteria(这是我们的数据访问标准)来得到这个查询和公司正确的计数正在返回!
任何想法,将不胜感激:)
使用功能NHibernate 2.0,我相信。
谢谢!
答
回答我的一个同事想出了:
public IEnumerable<SpecialismCombo> List(SpecialismListCriteria criteria, out int total)
{
//create query
DetachedCriteria query = DetachedCriteria.For<SpecialismCombo>("sc");
if (!String.IsNullOrEmpty(criteria.AgencyIds) && !criteria.AgencyIds.Equals("0"))
{
List<int> agencyIds = criteria.AgencyIds.Split(new char[] { ',' }).ToList<int>();
query.CreateAlias("Agencies", "a", NHibernate.SqlCommand.JoinType.InnerJoin);
query.Add(Restrictions.In("a.AgencyId", agencyIds));
// Subquery
var subqueryCount = query
.SetProjection(Projections.CountDistinct("a.AgencyId"))
.GetExecutableCriteria(NHibernateSessionManager.Instance.GetSession())
.UniqueResult<int>();
query.Add(Restrictions.Eq(Projections.Count("SpecialismComboId"), subqueryCount));
}
ProjectionList pl = Projections.ProjectionList()
.AddPropertyAlias("SpecialismComboId", "SpecialismComboId")
.AddPropertyAlias("SpecialismComboDisplayText", "SpecialismComboDisplayText")
.AddPropertyAlias("SpecialismComboDisciplineDisplayText", "SpecialismComboDisciplineDisplayText")
.AddPropertyAlias("SpecialismComboIdText", "SpecialismComboIdText")
.Add(Projections.GroupProperty("SpecialismComboId"));
total = FullCountQuery(query)
.GetExecutableCriteria(NHibernateSessionManager.Instance.GetSession())
.ToRowCount();
// Return the query results
return query
.SetProjection(Projections.Distinct(pl))
.SetResultTransformer(new KnownPropertyAliasToBeanResultTransformer(typeof(SpecialismCombo)))
.SetFirstResult(criteria.FirstRecord)
.SetMaxResults(criteria.PageSize)
.GetExecutableCriteria(NHibernateSessionManager.Instance.GetSession())
.List<SpecialismCombo>() as List<SpecialismCombo>;
}
private DetachedCriteria FullCountQuery(DetachedCriteria query)
{
ProjectionList pl = Projections.ProjectionList()
.Add(Projections.GroupProperty("SpecialismComboId"));
var subquery = CriteriaTransformer.Clone(query).SetProjection(Projections.Distinct(pl));
return DetachedCriteria.For<SpecialismCombo>("sc")
.Add(Subqueries.PropertyIn("sc.SpecialismComboId", subquery))
.SetProjection(Projections.CountDistinct("SpecialismComboId"));
}
我想我得到的ICriteria和预测之类的东西的窍门。但这fullcountquery吹走了:)
答
AFAIK in ICriteria你不能在FROM子句中指定子查询。
要么返回子计数和客户端总数或求助于HQL或SQL。
感谢您的答复。看起来令人失望的是,我有一个查询返回我想要的记录..但我无法轻松获得查询记录的数量,而无需编写其他函数或代码。但也许这只是icriteria的限制! – Jen