C# nhibernate criteria nhibernate-projections

NHibernate 投影查询按日期分组

发布于 2024-10-06 09:23:29 字数 440 浏览 3 评论 0原文

我想在 NHibernate 中编写一个投影查询，该查询按日期对记录进行分组并计算这些记录的“税”字段值。我的问题是数据库的值为 DateTime，我如何仅按日期而不是时间对记录进行分组。下面是我的代码

template.Criteria.SetProjection(
                Projections.ProjectionList()
                .Add(Projections.GroupProperty("IssueDatetime"), "DateVal")
                .Add(Projections.Sum("Tax"), "TotalFare")
            );

数据库将 IssueDatetime 字段存储为 DateTime 类型。我想计算每个日期的税费并忽略时间部分。有人可以帮我解决上述要求吗？

原文

I want to write a Projection query in NHibernate that groups records by date and counts "Tax" field value for those records. My question is that the database has the value as DateTime, how will I group records just by date and not time.Below is my code

template.Criteria.SetProjection(
                Projections.ProjectionList()
                .Add(Projections.GroupProperty("IssueDatetime"), "DateVal")
                .Add(Projections.Sum("Tax"), "TotalFare")
            );

The database stores the IssueDatetime field as DateTime type. I want to count the Tax per date and ignore the time part. Can anybody help me out with the above requirement?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

近箐 2024-10-13 09:23:29

对第一个投影使用以下内容：

Projections.GroupProperty(
    Projections.SqlFunction("date",
                            NHibernateUtil.Date,
                            Projections.GroupProperty("IssueDateTime")))

对于 NH 2.x：

Projections.GroupProperty(
    Projections.SqlFunction(new SQLFunctionTemplate(
                                NHibernateUtil.Date,
                                "dateadd(dd, 0, datediff(dd, 0, ?1))"),
                            NHibernateUtil.Date,
                            Projections.GroupProperty("IssueDateTime")))

Use the following for the first projection:

Projections.GroupProperty(
    Projections.SqlFunction("date",
                            NHibernateUtil.Date,
                            Projections.GroupProperty("IssueDateTime")))

For NH 2.x:

Projections.GroupProperty(
    Projections.SqlFunction(new SQLFunctionTemplate(
                                NHibernateUtil.Date,
                                "dateadd(dd, 0, datediff(dd, 0, ?1))"),
                            NHibernateUtil.Date,
                            Projections.GroupProperty("IssueDateTime")))

回复收藏 0 原文

如日中天 2024-10-13 09:23:29

假设 SQL Server 和 T-SQL，这个 ICriteria 就可以做到。

IList results = Session.CreateCriteria(typeof(Record), "record")
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.SqlGroupProjection("CONVERT(date, {alias}.[IssueDatetime]) AS [DateVal]", "CONVERT(date, {alias}.[IssueDatetime])", new[] { "DateVal" }, new IType[] { NHibernateUtil.Date }))
        .Add(Projections.Sum("Tax"), "TotalFare"))
    .List();

生成以下 SQL。

SELECT CONVERT(date, this_.[IssueDatetime]) AS [DateVal], sum(this_.Tax) as y1_ FROM IgnoreTime_Record this_ GROUP BY CONVERT(date, this_.[IssueDatetime])

Assuming SQL Server and T-SQL, this ICriteria will do it.

IList results = Session.CreateCriteria(typeof(Record), "record")
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.SqlGroupProjection("CONVERT(date, {alias}.[IssueDatetime]) AS [DateVal]", "CONVERT(date, {alias}.[IssueDatetime])", new[] { "DateVal" }, new IType[] { NHibernateUtil.Date }))
        .Add(Projections.Sum("Tax"), "TotalFare"))
    .List();

The following SQL is generated.

SELECT CONVERT(date, this_.[IssueDatetime]) AS [DateVal], sum(this_.Tax) as y1_ FROM IgnoreTime_Record this_ GROUP BY CONVERT(date, this_.[IssueDatetime])

回复收藏 0 原文

~没有更多了~