如何使用NHibernate ICriteria统计Group By分组后的每组数量

问题描述 #

尝试统计Group By子句查询结果的分组数量，但无法通过NHibernate ICriteria获取每组的统计数。

实体定义 #

public class PageView
{
    #region public fields
    public virtual int PagelogID { get; set; }

    public virtual string PageTitle { get; set; }
    public virtual string UserID { get; set; }

    public virtual DateTime DateViewed{get;set;}  // 映射至Date_Viewed列
    public virtual string SessionID { get; set; }
    public virtual string UserGroup { get; set; }
    #endregion
}

期望的SQL查询 #

select extract(year from DATE_VIEWED) "year", extract(month from DATE_VIEWED) "month", count(*) 
from PAGEVIEW_LOG 
where date_viewed > to_date(add_months(sysdate,-12)) 
group by extract(year from DATE_VIEWED), extract(month from DATE_VIEWED)
order by "year", "month" desc;

当前ICriteria代码 #

ProjectionList pl = Projections.ProjectionList();
this.crit.Add(Expression.Gt("DateViewed", DateTime.Now.AddMonths(-12)));

pl.Add(Projections.SqlGroupProjection(
    "Extract(month from Date_Viewed) MONTH",
    "Extract(month from Date_Viewed)",
    new String[] { "MONTH" },
    new NHibernate.Type.IType[] { NHibernateUtil.Int32 }), "MONTH");

pl.Add(Projections.SqlGroupProjection(
    "Extract(year from Date_Viewed) YEAR",
    "Extract(year from Date_Viewed)",
    new String[] { "YEAR" },
    new NHibernate.Type.IType[] { NHibernateUtil.Int32 }), "YEAR");
//pl.Add(Projections.Count("*"));
this.crit.SetProjection(pl);
this.crit.AddOrder(Order.Desc("YEAR"));
this.crit.AddOrder(Order.Desc("MONTH"));

这段代码已实现分组，但缺少Count(*)部分。尝试过的方法均报错或无效：

• 添加pl.Add(Projections.Count("YEAR"))：NHibernate无法识别YEAR别名
• 添加pl.Add(Projections.Count("*"))：报错
• 将年份的SqlGroupProjection存入变量后添加pl.Add(Projections.Count(yearProjection))：报错“length has to be greater than 0”
• 添加pl.Add(Projections.Count(Projections.RowCount()))：无效

解决方案 #

方案一：合并分组投影+SqlProjection实现Count(*) #

将年月的分组条件合并到一个SqlGroupProjection中，再通过SqlProjection直接添加count(*)：

ProjectionList pl = Projections.ProjectionList();
this.crit.Add(Expression.Gt("DateViewed", DateTime.Now.AddMonths(-12)));

// 合并年月的分组投影
var yearMonthProjection = Projections.SqlGroupProjection(
    "Extract(year from Date_Viewed) YEAR, Extract(month from Date_Viewed) MONTH",
    "Extract(year from Date_Viewed), Extract(month from Date_Viewed)",
    new string[] { "YEAR", "MONTH" },
    new NHibernate.Type.IType[] { NHibernateUtil.Int32, NHibernateUtil.Int32 });

pl.Add(yearMonthProjection);
// 添加Count(*)投影
pl.Add(Projections.SqlProjection("count(*) as COUNT", new string[] { "COUNT" }, new NHibernate.Type.IType[] { NHibernateUtil.Int32 }), "COUNT");

this.crit.SetProjection(pl);
this.crit.AddOrder(Order.Desc("YEAR"));
this.crit.AddOrder(Order.Desc("MONTH"));

方案二：使用RowCountInt64()替代Count(*) #

Projections.RowCountInt64()会直接生成count(*)的SQL，配合单独设置分组条件即可：

ProjectionList pl = Projections.ProjectionList();
this.crit.Add(Expression.Gt("DateViewed", DateTime.Now.AddMonths(-12)));

// 定义年月的字段投影
var yearProjection = Projections.SqlProjection("Extract(year from Date_Viewed) YEAR", new string[] { "YEAR" }, new NHibernate.Type.IType[] { NHibernateUtil.Int32 });
var monthProjection = Projections.SqlProjection("Extract(month from Date_Viewed) MONTH", new string[] { "MONTH" }, new NHibernate.Type.IType[] { NHibernateUtil.Int32 });

pl.Add(yearProjection, "YEAR");
pl.Add(monthProjection, "MONTH");
// 添加行计数（等价于count(*)）
pl.Add(Projections.RowCountInt64(), "COUNT");

// 单独设置分组条件
this.crit.SetProjection(pl);
this.crit.SetGroupBy("Extract(year from Date_Viewed), Extract(month from Date_Viewed)");

this.crit.AddOrder(Order.Desc("YEAR"));
this.crit.AddOrder(Order.Desc("MONTH"));

关键说明 #

1. 避免为单个分组字段单独创建SqlGroupProjection，建议合并分组条件或单独调用SetGroupBy
2. Projections.Count("*")报错是因为NHibernate期望传入实体映射的属性名，而非SQL通配符；RowCountInt64()是专门生成count(*)的投影
3. 使用SqlProjection时需确保别名和数据类型与NHibernate映射一致

内容的提问来源于stack exchange，提问作者AngryOtter