nhibernate 使用 icriteria 与 group by 和having 并获取总行数（c#）

Question

所以我有一个 nhibernate icriteria 查询来获取我想要的结果列表。

但是我不知道如何获得总行数。

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。

谢谢！！

Answer 1

据我所知，在 ICriteria 中，您不能在 FROM 子句中指定子查询。

要么返回子计数并在客户端求和，要么诉诸 HQL 或 SQL。

Answer 2

我的一位同事给出的答案是：

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"));
        }

我以为我已经掌握了标准和预测之类的窍门。但这个 fullcountquery 却打破了这一点:)