Entity Framework. SQL Group By to EF Group By

Question

I am trying to define a standard way to group by year, by month, by day, by hour, etc ...

By reading some SQL documentation it seems the most efficient way would be to use:

GROUP BY dateadd(month, datediff(month, 0, CreatedDate), 0)

Note the "month", "year", ...

I then tried to replicate this using:

.GroupBy(x => SqlFunctions.DateAdd("month", SqlFunctions.DateDiff("month", 0, x.Created), 0))

.GroupBy(x => EntityFunctions.AddMonths(EntityFunctions.DiffMonths(0, x.Created)))

However, both expression failed to compile ...

And I am not sure if this is the way to do it?

How can I correctly replicate that SQL code line in EF?

Answer 1

I don't understand Why would you want to use the standard sql to do that any way, however this can be done easily using LINQ and it would be more standard, grouping by an anonymous type for example like this:

.GroupBy( x => new 
               {
                  month = x.CreatedDate.Month,
                  year = x.CreatedDate.Year,
                  ...
               });