Replace Entity Framework FromSql() with clean EF Core query

asp.net-core c# entity-framework entity-framework-core sql

Question

I'm wondering how to replace my EF FromSql() extension with clean Entity Framework Core task.

My query looks like this:

 activities = await _context.SubActivities
            .FromSql(SubActivityQueryLibrary.GetRouteCombustionForDevices(deviceIds, dateFrom))
            .Select(dict => new { Key = dict.StartTime, Value = Convert.ToDouble(dict.ConsumptionFuel) })
            .OrderBy(o => o.Key)
            .ToDictionaryAsync(x => x.Key, x => x.Value);

and the SQL query:

SELECT 
    CONVERT(DATE, StartTime) AS [StartTime], 
    SUM(ConsumptionFuel) AS [ConsumptionFuel]
FROM 
    [Flota24].[dbo].[ACTIVITIES]
WHERE 
    DeviceId IN ({string.Join(",", deviceIds)}) 
    AND StartTime >= '{startDate:yyyy-MM-dd}'
GROUP BY 
    CONVERT(DATE, StartTime)

Just would like to convert hard to maintain SQL code to EF Core. I tried to create something with SUM, GROUP and CONVERT() but always had some troubles.

1
0
4/10/2018 8:50:50 AM

Accepted Answer

EF Core task should be replaced with code below:

 activities = await _context.SubActivities
            .Where(d => deviceIds.Contains(d.DeviceId) && d.StartTime >= dateFrom)
            .GroupBy(o => o.StartTime.Date)
            .Select(dict => new { Key = dict.Key, Value = Convert.ToDouble(dict.Sum(x => x.ConsumptionFuel)) })
            .OrderBy(o => o.Key)
            .ToDictionaryAsync(x => x.Key, x => x.Value);

SQL Query can be removed.

1
4/10/2018 9:42:37 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow