Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

NHibernate Projection queries group by date

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?

like image 617
developer Avatar asked Dec 28 '22 04:12

developer


2 Answers

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")))
like image 130
Diego Mijelshon Avatar answered Jan 09 '23 16:01

Diego Mijelshon


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])
like image 37
Adam Boddington Avatar answered Jan 09 '23 16:01

Adam Boddington