I got a taf SQL query that should be run in the latest version of entity-framework core (dotnet core 2) using the latest postgres provider:
SELECT
date_trunc('day', "StartTime") AS DAY
, sum("AnswerTime" - "StartTime")
, count("AnswerTime" != "ReleaseTime")
FROM "Cdrs"
GROUP BY DAY
ORDER BY DAY;
I partly could solve it but I'm unable to complete the entire query. mainly the sum of a calculated value I got no idea how to solve.
also count wit a condition, no idea how to in EF core
im trying to solve it with linq to sql like this (not sure wheter this is gonna work anyways):
[SuppressMessageAttribute("Microsoft.Usage", "CA1801:ReviewUnusedParameters", MessageId = "dateValue")]
[DbFunctionAttribute("date_trunc")]
public extern static DateTimeOffset? date_trunc(string what, DateTimeOffset? dateValue);
var x = (from c in ef.Cdrs
group c by date_trunc("day", c.StartTime) into day
//group c by (c.AnswerTime - c.StartTime) into g
select new { day.Count( })
any hints how to bring the SQL above into entity-framework core (dotnet 2) is appreciated.
Try something like that;
var query = ef.Cdrs
.GroupBy(x => new { Day = date_trunc("d", x.StartTime) })
.Select(x => new
{
Day = x.Key.Day,
Sum = x.Sum(s => s.AnswerTime - s.StartTime),
Count = x.Count(c => c.AnswerTime != c.ReleaseTime)
});