sql query on entity-framework core with sum(x-y), count() and group

Question

sql query on entity-framework core with sum(x-y), count() and group

.net-core entity-framework-core linq postgresql sql

Question

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.

Marcel Haldemann

View more on Stack Overflow

lucky · Accepted Answer · 12/19/2017 1:45:51 PM

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)
            });

sql query on entity-framework core with sum(x-y), count() and group

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related