Efcore: Different query behaviour linqpad and application. Pretty sure in EF BUG

[ekzarov]

• Linqpad executes this query for me in 0.018 seconds (immediately). Sql profiler shows pretty good query and only one.
• In EF Core (ASP.NET Core app or unit test) it executes in 1 minute. Sql profiler shows that query executes for every record in result.

Queries are the same. Below you will find actuall query in C#. In case if you need DB I can upload it somewhere for you, so you can reproduce issue very easy. It is very crucial for our project, so please assist in this issue.
Thank you.

PS: One of quries before had the same issue and there was check on null for object in WHERE clause. It helped before but don`t with this query.

Steps to reproduce

Run this query in LinqPad and from VS (asp.net core app):

var query = from s in ApplicationFormStatuses
  join a in ApplicationForms on s.Id equals a.ApplicationFormStatusId into join1
  from join2 in join1.DefaultIfEmpty()
  group join2 by new {s.Id, s.Title}
  into g
  select new
  {
    StatusId = (int) g.Key.Id,
    StatusName = g.Key.Title,
    Count = g.Count(),
    Amount = (int)g.Key.Id == 1
       ? g.Sum(x => x.Patients.Where(y => y.TotalOutstandingBalanceOwedToTennant.HasValue).Sum(p => p.TotalOutstandingBalanceOwedToTennant.Value))
       : 0
  };

  query.Dump(); // In C# it will be await query.ToListAsync()

Further technical details

EF Core version: 2.1.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer (Azure)
Operating system: Azure
IDE: (e.g. Visual Studio 2017 15.9.5)

[tuespetre]

This is the dreaded Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Can you try this instead:

var query = from s in ApplicationFormStatuses
  join a in ApplicationForms on s.Id equals a.ApplicationFormStatusId into join1
  from join2 in join1.DefaultIfEmpty()
  let c = join2.Patients.Where(y => y.TotalOutstandingBalanceOwedToTennant.HasValue).Sum(p => p.TotalOutstandingBalanceOwedToTennant.Value)
  group new { c } by new {s.Id, s.Title}
  into g
  select new
  {
    StatusId = (int) g.Key.Id,
    StatusName = g.Key.Title,
    Count = g.Count(),
    Amount = (int)g.Key.Id == 1
       ? g.Sum(x => x.c)
       : 0
  };

[ekzarov]

The same result :(

[tuespetre]

Aw man... what about something like this?

var query = 
    from s in ApplicationFormStatuses
    join a in ApplicationForms on s.Id equals a.ApplicationFormStatusId into join1
    from join2 in join1.DefaultIfEmpty()
    from p in join2.Patients
    where p.TotalOutstandingBalanceOwedToTennant.HasValue
    group new { a.Id, p.TotalOutstandingBalanceOwedToTennant.Value } by new {s.Id, s.Title}
    into g
    select new
    {
        StatusId = (int) g.Key.Id,
        StatusName = g.Key.Title,
        Count = g.Select(x => x.Id).Distinct().Count(),
        Amount = (int)g.Key.Id == 1 ? g.Sum(x => x.Value) : 0
    };

[ekzarov]

Thank you. Decided to make this query via Dapper + TSQL, but anyway thanks for help.