.NET LINQ to entities group by date (day)

Question

I have the same problem posted here: LINQ to Entities group-by failure using .date

However, the answer is not 100% correct. It works in all cases except when different timezones are used. When different timezones are used, it also groups on timezones. Why? I managed to bypass this by using many entity functions.

int localOffset= Convert.ToInt32(
    TimeZone.CurrentTimeZone.GetUtcOffset(DateTime.Now).TotalMinutes);

var results = (
    from perfEntry in db.entry
    where (....)
    select new { 
        perfEntry.Operation, perfEntry.Duration, 
        localTime = EntityFunctions.AddMinutes(perfEntry.Start, 
            localOffset - EntityFunctions.GetTotalOffsetMinutes(
                perfEntry.Start)) 
    }
).GroupBy(x => new { 
    Time = EntityFunctions.TruncateTime(
        EntityFunctions.CreateDateTime(x.localTime.Value.Year,
            x.localTime.Value.Month, x.localTime.Value.Day, 0, 0, 0)),
    x.Operation }
).OrderByDescending(a => a.Key).
Select(g => new {
    Time = g.Key.Time,
    ...
});

Is there someone out there who knows how to do this the proper way? This code is so ugly and probably very inefficient.

UPDATE (warning):
I also realised that the function EntityFunctions.CreateDateTime suffers from a bug. Its not compatible with leap years, such as this year. 29 feb 2012 will throw an exception.

Answer 1

You can use UTC DateTime and trunkate the time after it.