I am trying to convert the following sql query to EF
sql Query
Select Sum([KiloWatt]) as 'Sum',
Min([KiloWatt]) as 'Min',
Max([KiloWatt]) as 'Max',
Sum([KiloWatt])/COUNT([KiloWatt]) as 'Average',
CONVERT(date, [DateTime])
from OneHourElectricitys
where [DateTime] < SYSDATETIME()
group by CONVERT(date, [DateTime])
EF Code
var analytics = await _analyticsRepository.Query().Where(x => x.DateTime.Subtract(DateTime.Today).Days < 0)
.GroupBy(x => x.DateTime.Date)
.Select(x => new
{ DateTime = x.Key,
Max = x.Max(y => y.KiloWatt),
Min = x.Min(y => y.KiloWatt),
Avg = x.Sum(y => y.KiloWatt)/ x.Count(),
Sum = x.Sum(y => y.KiloWatt)
})
.ToListAsync();
Stack Trace :-
System.ArgumentException: must be reducible node at System.Linq.Expressions.Expression.ReduceAndCheck() at System.Linq.Expressions.Expression.ReduceExtensions()
Am I doing coming wrong?
-- Update --
The following 2 EF queries run fine but combining them gives the error.
var analytics = await _analyticsRepository.Query().Where(x => x.DateTime.Subtract(DateTime.Today).Days < 0)
var analytics = await _analyticsRepository.Query().GroupBy(x => x.DateTime.Date)
.Select(x => new
{ DateTime = x.Key,
Max = x.Max(y => y.KiloWatt),
Min = x.Min(y => y.KiloWatt),
Avg = x.Sum(y => y.KiloWatt)/ x.Count(),
Sum = x.Sum(y => y.KiloWatt)
})
.ToListAsync();
The correct solution to this problem is to upgrade to EF Core 2.1.3 as mentioned by Ivan Stoev
This is my solution and it works.
var analytics = await (_analyticsRepository.Query()
.Where(x => x.PanelId.Equals(panelId, StringComparison.CurrentCultureIgnoreCase)
&& DateTime.Compare(x.DateTime, new DateTime()) < 0))
.GroupBy(x => x.DateTime.Date)
.Select(x => new
{
DateTime = x.Key,
Max = x.Max(y => y.KiloWatt),
Min = x.Min(y => y.KiloWatt),
Avg = x.Sum(y => y.KiloWatt) / x.Count(),
Sum = x.Sum(y => y.KiloWatt)
}).ToListAsync();
Thanks,