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?
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")))
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])
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With