Entity framework group by date and return new class

.net, c#, entity-framework

Solution

It is much simpler to use EntityFunctions* here:

var result =
    from query in DbManager.GetTable<SomeTableModel>()
    group query by EntityFunctions.TruncateTime(query.CreationDate)
    into list1
    select list1;

*DbFunctions as of Entity Framework 6.

Problem

I'm using the .NET and the Entity Framework. I want to group a table by a date without the time part. I've got the class: ``` public sealed class QueryItem { public int Year { get; set; } public int Month { get; set; } public int Day { get; set; } } ``` And the method (I'm using the BLToolkit, if you want to know what is DbManager): ``` protected override IQueryable<QueryItem> InitiateQuery() { return from query in DbManager.GetTable<SomeTableModel>() group query by new { Year = query.CreationDate.Year, Month = query.CreationDate.Month, Day = query.CreationDate.Day } into list1 select new QueryItem {Year = list1.Key.Year, Month = list1.Key.Month, Day = list1.Key.Day}; } ``` In this method linq translated into this SQL query: ``` SELECT [query].[CreationDate] FROM [SomeDatabase.SomeTable] [query] GROUP BY DatePart(Year, [query].[CreationDate]), DatePart(Month, [query].[CreationDate]), DatePart(Day, [query].[CreationDate]), [query].[CreationDate] ``` And this is wrong query because it doesn't group only by a date. But if I use this code (it's not usable, I need to return a class object): ``` var result = from query in DbManager.GetTable<SomeTableModel>() group query by new { Year = query.CreationDate.Year, Month = query.CreationDate.Month, Day = query.CreationDate.Day } into list1 select list1; ``` It will be translated into this: ``` SELECT [t1].[c1] as [c11], [t1].[c2] as [c21], [t1].[c3] as [c31] FROM ( SELECT DatePart(Year, [selectParam].[CreationDate]) as [c1], DatePart(Month, [selectParam].[CreationDate]) as [c2], DatePart(Day, [selectParam].[CreationDate]) as [c3] FROM [SomeDatabase.SomeTable] [selectParam] ) [t1] GROUP BY [t1].[c1], [t1].[c2], [t1].[c3] ``` And this is the right query. I suppose that Entity Framework trying to optimise my query or something like this and this is why I've got the wrong query. Am I right? Am I doing it in the right way? What should I do (maybe use direct SQL query or something else)?

Original source