NHibernate Projection queries group by date

Question

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?

Diego Mijelshon · Accepted Answer · 2010-12-09T22:15:39.670

8

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")))

edited Dec 09 '10 at 22:15

answered Dec 08 '10 at 23:41

Diego Mijelshon

52,548
16
116
154

When I use the above projection I get the below error,Error 2 The name 'NHibernateUtil' does not exist in the current context. I tried including NHibernate.Util namespace but it still gives the same error.. – developer Dec 09 '10 at 15:07
1

It's in the NHibernate namespace, check your spelling. – Diego Mijelshon Dec 09 '10 at 16:07
I dont know why but now I am getting the below error,"Object reference not set to an instance of an object." – developer Dec 09 '10 at 17:11
Current dialect NHibernate.Dialect.MsSql2005Dialect doesn't support the function: date is what it shows.. I am using NHibernate 2 – developer Dec 09 '10 at 18:55
I suggest that you upgrade to 3.0, which adds that (just checked) – Diego Mijelshon Dec 09 '10 at 19:35
I tried to do something like .Add(Projections.GroupProperty(Projections.SqlFunction("SUBSTRING", NHibernateUtil.String, Projections.GroupProperty("IssueDatetime"),Projections.Constant(0), Projections.Constant(8))), "DateVal") but it still wont work.. – developer Dec 09 '10 at 19:40
Is there any other way around. Like in my above query where I am trying to create a substring of date and then grouping instead of using date function. But in above case it shows something GROUP BY substring(this_.IssueDatetime, ?, ?). – developer Dec 09 '10 at 19:57
You can use a different overload of SqlFunction that takes ISQLFunction and pass a SQLFunctionTemplate, just like the preregistered one in NH 3. – Diego Mijelshon Dec 09 '10 at 20:31
...which would be `new SQLFunctionTemplate(NHibernateUtil.Date, "dateadd(dd, 0, datediff(dd, 0, ?1))")` – Diego Mijelshon Dec 09 '10 at 21:38
Can you please show me full projection line code. I am not sure how will I use the above code that you just posted. And thanks for all the help.. – developer Dec 09 '10 at 22:07
I get the below exception,Type 'NHibernate.Dialect.Function.SQLFunctionTemplate+TemplateChunk' in Assembly 'NHibernate, Version=2.1.2.4000, Culture=neutral, PublicKeyToken=aa95f207798dfdb4' is not marked as serializable. – developer Dec 09 '10 at 22:36
I don't have a 2.x test project handy, but both forms work with 3.x. Just upgrade, man :-) – Diego Mijelshon Dec 09 '10 at 22:43

score 2 · Answer 2 · answered Dec 08 '10 at 23:57

2

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])

answered Dec 08 '10 at 23:57

Adam Boddington

6,750
2
21
12

1

By doing that you tie yourself both to the schema (i.e. it breaks if you rename a column) and the DBMS – Diego Mijelshon Dec 09 '10 at 00:42
Thanks for the heads up, I wasn't aware of that method. Running it on SQL Server, though, I get this error. "Current dialect NHibernate.Dialect.MsSql2005Dialect doesn't support the function: date" – Adam Boddington Dec 09 '10 at 02:31
That's odd, it's defined even for MsSql2000. What NH version are you using? (latest stable is 3.0.0) – Diego Mijelshon Dec 09 '10 at 16:09

NHibernate Projection queries group by date

2 Answers2