如何使用 NHibernate 的 ICriteria 进行分组、获取关联和 T-SQL 函数

Question

我想创建以下 T-SQL 语句：

SELECT  SUM (sa.Amount) as 'SumAmount',
        SUM(sa.Cost) as 'SumCost', 
        gg.[Description] as 'Goodsgroup', Month(sa.[Date]) as 'Month' 
FROM SalesmanArticle sa
INNER JOIN Article a
    ON a.ArticleId = sa.ArticleId
INNER JOIN GoodsGroup gg
    ON gg.GoodsGroupId = a.GoodsGroupId
GROUP BY gg.[Description], Month(sa.[Date])
ORDER BY 'Month', 'Goodsgroup'

Is this possible with NHibernates ICriteria?

如何使用 Month-T-SQL-Function？

我是否必须手动加入，或者 ICriteria API 是否知道当我使用 propetyName“SalesmanArticle.Article.Goodsgroup.Description”时，它必须加入文章和商品组？

编辑：

现在我已经在这里编写了这段代码：

// typesafe properties
string article = typeof(Article).Name;
string goodsGroup = typeof(GoodsGroup).Name;
string salesmanArticle = typeof(SalesmanArticle).Name;

string amount = Reflector.GetPropertyName<SalesmanArticle>(x => x.Amount);
string cost = Reflector.GetPropertyName<SalesmanArticle>(x => x.Cost);
string description = string.Format("{0}.{1}",
    goodsGroup, Reflector.GetPropertyName<SalesmanArticle>(x => x.Article.GoodsGroup.Description));
string date = Reflector.GetPropertyName<SalesmanArticle>(x => x.Date);

string formatedDate = string.Format("MONTH([{0}])", date);

return GetSession()
    // FROM
    .CreateCriteria(typeof(SalesmanArticle), salesmanArticle)
    // JOIN
        .CreateCriteria(article, article, JoinType.InnerJoin)
        .CreateCriteria(goodsGroup, goodsGroup, JoinType.InnerJoin)
    // SELECT
        .SetProjection(Projections.ProjectionList()
                           .Add(Projections.Sum(amount))
                           .Add(Projections.Sum(cost))
    // GROUP BY
                           .Add(Projections.GroupProperty(description))
                           .Add(Projections.SqlGroupProjection(formatedDate, formatedDate, new[]{"MyDate"} , new[] { NHibernateUtil.Int32 })))
        .List();

但是抛出了 AdoException：

无法执行查询[ SELECT sum(this_.Amount) 作为 y0_, sum(this_.Cost) 作为 y1_, 货物组2_。描述为y2_， 月（[日期]）来自 [销售员文章] this_内连接【文章】article1_ 关于这个_.ArticleId=article1_.ArticleId 内部联接 [GoodsGroup]goodsgroup2_ 在 Article1_.GoodsGroupId=goodsgroup2_.GoodsGroupId GROUP BYgoodsgroup2_.描述， 月（[日期]）]

[SQL: 选择 sum(this_.Amount) 作为 y0_, sum(this_.Cost) 作为 y1_, 货物组2_。描述为y2_， 月（[日期]）来自 [销售员文章] this_内连接【文章】article1_ 关于这个_.ArticleId=article1_.ArticleId 内部连接 ​​[GoodsGroup]goodsgroup2_ 在 Article1_.GoodsGroupId=goodsgroup2_.GoodsGroupId GROUP BYgoodsgroup2_.描述， 月（[日期]）]

奇怪的是 NHibernate 尝试创建 2 个查询？！

他们都是正确的！

代码行

.Add(Projections.SqlGroupProjection(formatedDate, formatedDate, new[]{"MyDate"} , new[] { NHibernateUtil.Int32 })))

而不是我使用的

.Add(Projections.SqlFunction("MONTH", NHibernateUtil.Int32, Projections.GroupProperty(date))))

SqlFunction 的问题是它创建了一个 GROUP BY sa.Date 而不是 MONTH(sa.Date)。但这种方法在语法上是正确的。

所以我切换到SqlGroupProjection方法。

但无论如何它都不起作用。

有人可以帮助我吗？

Answer 1

我解决了。这是正确的代码：

public class SalesmanArticleRepository : Repository<SalesmanArticle>, ISalesmanArticleRepository
{
    public IList GetAllAll()
    {
        // typesafe properties
        string article = typeof(Article).Name;
        string goodsGroup = typeof(GoodsGroup).Name;
        string salesmanArticle = typeof(SalesmanArticle).Name;

        string amount = Reflector.GetPropertyName<SalesmanArticle>(x => x.Amount);
        string cost = Reflector.GetPropertyName<SalesmanArticle>(x => x.Cost);
        string description = string.Format("{0}.{1}",
            goodsGroup, Reflector.GetPropertyName<SalesmanArticle>(x => x.Article.GoodsGroup.Description));
        string date = Reflector.GetPropertyName<SalesmanArticle>(x => x.Date);

        string formatedDateSql = string.Format("month({{alias}}.[{0}]) as mydate", date);
        string formatedDateGroupBy = string.Format("month({{alias}}.[{0}])", date);

        return GetSession()
            // FROM
            .CreateCriteria(typeof(SalesmanArticle), salesmanArticle)
            // JOIN
                .CreateCriteria(article, article, JoinType.InnerJoin)
                .CreateCriteria(goodsGroup, goodsGroup, JoinType.InnerJoin)
            // SELECT
                .SetProjection(Projections.ProjectionList()
                                   .Add(Projections.Sum(amount))
                                   .Add(Projections.Sum(cost))
            // GROUP BY
                                   .Add(Projections.GroupProperty(description))
                                   .Add(Projections.SqlGroupProjection(formatedDateSql, formatedDateGroupBy, new[] { "mydate" }, new[] { NHibernateUtil.Int32 })))
                .List();
    }
}