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.

1
0
12/19/2017 1:46:14 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Popular Answer

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)
            });
0
12/19/2017 1:45:51 PM

Related Questions

Entity Framework - Group By Sum

Try this instead:...var catgroup = db.CATEGORies.GroupBy(c => c.CATNAME). Select(g => new { g.Key, SUM = g.Sum(s => s.Inqueries.Select(t=>t.TotalTimeSpent).Sum()) });
entity-framework entity-framework-6
asked by Giorgi Nakeuri

Entity framework : Return grouped by value and sum of some columns in the same query

Try this query:...var query = context.tables.GroupBy(r=>new{r.Col3, r.Col4, r.Col5}) .Select(g=>{ Col1 = g.Sum(c=>c.Col1), Col2 = g.Sum(c=>c.Col2), Col3 = g.Key.Col3, ...
c# entity-framework entity-framework-6 iqueryable sql
asked by Kirill Bestemyanov

Entity Framework Core 2.0: Error when enumerating over query with group

This seems to be a bug in EF Core 2.0 which got addressed in this issue (Contains workaround):...https://github.com/aspnet/EntityFrameworkCore/issues/9551...You can get the testfeed 2.0.3 here (Contains fix):...https://github.com/aspnet/Announcements/issues/274
entity-framework entity-framework-core
asked by Yush0

Use SQL Query for Dropdown - ASP.NET Core 2.0; RazorPages; VS 2017; Entity Framework Core

There are multiple issues in your code. ...PmeFundData... should have ...Id... property, otherwise, you will receive error while running ...update-database... command. ...public partial class PmeFundData { public int Id { get; set; } public string Firm { get; set; } } ...ViewBag... is not supported in RazorPage, you could trace this is...
asp.net-core-2.0 entity-framework-core razor
asked by Edward

LINQ does not produce COUNT() query on group using EF Core

The LINQ expression 'GroupBy([x.Device], [x])' could not be translated and will be evaluated locally....The LINQ expression 'Count()' could not be translated and will be evaluated locally....The above logs are from my later test results, which show that ...GroupBy...& ...Count()... are not supported yet to translate full LINQ to SQL, then direc...
c# entity-framework-core
asked by Dongdong

Entity Framework Core + Count with Group By

I had to upgrade EF Core version to 2.1 and LINQ is now translated properly into SQL.
entity-framework-core linq
asked by ironcurtain

EF Core Group By with Deferred Execution and SQL side grouping

The only way I was able to get SQL translation in EF Core 2.1 (and 2.2) was to group by composite ...anonymous... type conditionally including the desired data parts:...bool includeYear = ...; bool includeMonth = ...; bool includeDay = ...; bool includeHour = ...; var query1 = series .GroupBy(e => new { Year = includeYear ? e.Creat...
c# ef-core-2.1 entity-framework entity-framework-core linq
asked by Ivan Stoev

Entity Framework Core don't generate Group By SQL instruction?

As @gert-arnold wrote in the comment, your usage of GroupBy cannot be translated to SQL - an aggregate operator must be applied for that. Read ...the documentation... to better understand which cases can be translated by EF Core....You can still perform the GroupBy in the client side by inserting an AsEnumerable before the GroupBy operator.
c# entity-framework-core linq npgsql
asked by Shay Rojansky

One LINQ query to get counts from several entities when using entity framework core

That's a one LINQ query solution, i hope this will be helpful,...var result = Context.UserAccounts.Select(x => new List<int>() { 1, 0, 0, 0 }).Union(Context.GuestAccounts.Select(y => new List<int>() { 0, 1, 0, 0 })) .Union(Context.WorkGroups.Where(x => x.IsActive).Select(z => new List<int>() { 0, 0, 1, 0 })).Union(Context.WorkItems.Sele...
.net-core c# entity-framework-core linq
asked by Furkan Öztürk

SQL query to LINQ expression - Entity Core Framework 3 + SQL Server

You need to use the singleton ...GroupBy... trick:...var ans = ThisAwesomeTable.GroupBy(r => 1) .Select(rg => new { lessThan30 = rg.Sum(r => r.CreatedWhen >= thirtyDaysAgo ? 1 : 0), GreaterThan30 = rg.Sum(r => r.CreatedWhen < thirtyDaysAgo ? 1 : 0) ...
entity-framework-core fluent linq sql-server
asked by NetMage

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

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