Geek Answers Handbook

EntityFramework GroupBy with DBFunctions or alternative

I have 100 rows of data with different dates. I would like to group the result by the same dates, every 30 minutes.

Instead:
Result 1, 2016-02-02 13:00:24
Result 1, 2016-02-02 13:01:24
Result 1, 2016-02-02 13:02:24
Result 1, 2016-02-02 13 : 33: 24

Necessary:

Result 1, 2016-02-02 13:00:24
Result 1, 2016-02-02 13:33:24

Original request:

    return await loc.Where(p => p.ReadTime >= df && p.ReadTime <= dt)
        .OrderBy(p => p.ReadTime)
        .ProjectTo<LocationModel>().ToListAsync();

What I tried:

    return await loc.Where(p => p.ReadTime >= df && p.ReadTime <= dt)
        .GroupBy(p => DbFunctions.TruncateTime(p.ReadTime))
        .Select(p => new LocationModel
        {
            Lng = p.FirstOrDefault().Lng,
            Lat = p.FirstOrDefault().Lat
        })
        .ToListAsync();

Stack Trace Error:

.ThrowForNonSuccess( )\r\n System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification( )\r\n System.Runtime.CompilerServices.TaskAwaiter 1.GetResult()\r\n at System.Web.Http .Dispatcher.HttpControllerDispatcher.<SendAsync>d__1.MoveNext()","innerException":{"message":"An error has occurred.","exceptionMessage":"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.","exceptionType":"System.Data.SqlClient.SqlException" ,"stackTrace":" at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__167_0(Task 1 )\r\n at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()\\ System.Threading.Tasks.Task.Execute()\r\n --- , -\r\n System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task )\r\n System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task )\r\n System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.

d__c.MoveNext() "," innerException ": {" message ":" . "," exceptionMessage ":"    "," ExceptionType ":" System.ComponentModel.Win32Exception "," StackTrace": }}}

+4
1

, , , - "" .

, 30- , :

Result 1, 2016-02-02 13:00:24, 2016-02-02 13:00:00 (bucket 1)
Result 1, 2016-02-02 13:01:24, 2016-02-02 13:00:00 (bucket 1)
Result 1, 2016-02-02 13:02:24, 2016-02-02 13:00:00 (bucket 1)
Result 1, 2016-02-02 13:33:24, 2016-02-02 13:30:00 (bucket 2)

- , . , , - , (), :

from l in loc
join bucket in (
    from l in loc
    group l by new DateTime(l.ReadTime.Year, l.ReadTime.Month, l.ReadTime.Day, l.ReadTime.Hour, l.ReadTime.Minute < 30 ? 0 : 30, 0) into g
    select g.Min(m => m.ReadTime)
) on l.ReadTime equals bucket
select new LocationModel
{
    Lng = l.FirstOrDefault().Lng,
    Lat = l.FirstOrDefault().Lat
}

, , . / , /, , :

from l in loc
join bucket in (
    from l in loc
    group l by new DateTime(l.ReadTime.Year, l.ReadTime.Month, l.ReadTime.Day, l.ReadTime.Hour, l.ReadTime.Minute < 30 ? 0 : 30, 0) into g
    select g.Min(m => m.Id)
) on l.Id equals bucket
select new LocationModel
{
    Lng = l.FirstOrDefault().Lng,
    Lat = l.FirstOrDefault().Lat
}

. , . , .

, Linq to SQL, DbFunctions DateTime EF, - (, DbFunctions.CreateDateTime).

+1

More articles:


All Articles