.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.

yes, but such a simple operation should be able to write in a easier way? — Zeezer, Jun 14 '12 at 06:09
Not necessarily. Remember, that Linq to Entities must convert C# into SQL, that means it has many restrictions on what you can do to it because there is no SQL that supports the operation. The EntityFunctions is there specifically to deal with these limitations, so you have to use them if you want what they do. — Erik Funkenbusch, Jun 14 '12 at 20:05
Using four EntityFunctions for one single task seams like overkill. Surely there must be an easier way... — Zeezer, Jun 15 '12 at 06:12
IMO, sometimes the easiest way to to just write a sproc. Up to you to decide when you're hitting that point. — Kenneth Ito, Jun 24 '12 at 18:54

score 0 · Accepted Answer · answered Jul 12 '12 at 09:13

0

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

answered Jul 12 '12 at 09:13

Anton Sivov

404
6
16

You mean the DateTime function ToUniversalTime()? This does not work in linq to entities. You can only use a few functions and properties in linq to entities. – Zeezer Jul 13 '12 at 06:44
LinqToEntity try to convert your request to sql query. It can't translate ToUniversalTime() to Sql. I'd suggest you get all data first (.ToList() for example) and make any things with collection of the data. In the case you can use ToUTCTime() function in Linq query. – Anton Sivov Jul 13 '12 at 08:05
I am dealing with huge tables and complex statistics. To get all data first will have an impact on performance which im not willing to take. – Zeezer Jul 13 '12 at 09:11
Ok. In the case you have to make some sql work and align you date to UTC in SQL. You can use view to create the work. Sure, you'll keep your performance up. Additionally you can truncate time in the view. It'll be much more faster if you you use Dateadd and datediff functions. – Anton Sivov Jul 13 '12 at 09:48
ok, that seams to be a better solution, although i was hoping this could be solved without modifying the database. Are there sqlfunctions that can translate to UTC then? – Zeezer Jul 16 '12 at 12:34
i used that approach, however, thats not really an answer to my question, it should be possible to do in the business layer of the application. – Zeezer Jul 18 '12 at 06:03
Hope the topic will help you convert date to utc date by SQL query http://www.opsvault.com/how-to-convert-sql-server-datetime-data-to-utc-time/ – Anton Sivov Jul 22 '12 at 11:29

.NET LINQ to entities group by date (day)

1 Answers1

Linked