Efcore: Max and Average evaluated locally

[mo-esmp]

Here is my query

```c#
var stats = (from team in context.Teams
let teamCount = context.Teams.Count()
let roundTeamCount = context.RoundTeams.Count(rt => rt.RoundId == roundId)
let averagePoint = context.Teams.Select(p => p.Point).Average()
let maxPoint = context.Teams.Max(t => t.Point)
select new { averagePoint, maxPoint, teamCount, roundTeamCount }
) .FirstOrDefault();

public class Team
{
public int Id{ get; set; }

  public string Name { get; set; }

    public int Point { get; set; }

}

public class RoundTeamPlayer
{
public int Id{ get; set; }

public int Point { get; set; }

public long RoundId { get; set; }

public Round Round { get; set; }

public long TeamId { get; set; }

public Team Team { get; set; }

}

After running query VS hangs for several seconds and get these messages:

Microsoft Visual Studio

Evaluating the function '<>f__AnonymousType35<j__TPar, j__TPar, j__TPar, j__TPar>.averagePoint.get' timed out and needed to be aborted in an unsafe way. This may have corrupted the target process.

If the problem happens regularly, consider disabling the Tools->Options setting "Debugging->General->Enable property evaluation and other implicit function calls" or change the code to disable evaluation of this method. See help for information on doing this.

warn: Microsoft.EntityFrameworkCore.Query[20500]
The LINQ expression 'Average()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20502]
Possible unintended use of a potentially throwing aggregate method (Min, Max, Average) in a subquery. Client evaluation will be used and operator will throw if no data exists. Changing the subquery result type to a nullable type will allow full translation.
warn: Microsoft.EntityFrameworkCore.Query[20500]
The LINQ expression 'Max()' could not be translated and will be evaluated locally.

Translated sql queries:

Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (51ms) [Parameters=[@__8__locals1_roundId_0='?'], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) (
SELECT COUNT()
FROM [Teams] AS [t2]
) AS [teamCount], (
SELECT COUNT()
FROM [RoundTeams] AS [rt]
WHERE [rt].[RoundId] = @__8__locals1_roundId_0
) AS [roundTeamCount]

FROM [Teams] AS [team]
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (39ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

SELECT AVG(CAST([p1].[Point] AS float))
FROM [Teams] AS [p1]

Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (32ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT MAX([t1].[Point])
FROM [Teams] AS [t1]
```

When I use FirstOrDefaultAsync() there no VS hangs but still get The LINQ expression 'Average()' ... warns

[ajcvickers]

@mo-esmp What versions of EF Core are you using and with which database provider?

[mo-esmp]

Microsoft.EntityFrameworkCore.SqlServer provider and same on both EF Core 2.0.1 and 2.0.2.

[smitpatel]

In linq/C# when you call aggregate methods like Average/Min/Max on an enumerable of non-nullable type which is empty, it throws exception. Since you are calling such methods and we don't know if there will be elements or not, we force client eval to align with linq behavior, hence you are seeing multiple queries. Empty enumerable of nullable type with such methods return null. So if you are calling such method on nullable types, it should translate to server in single query.

warn: Microsoft.EntityFrameworkCore.Query[20502]
Possible unintended use of a potentially throwing aggregate method (Min, Max, Average) in a subquery. Client evaluation will be used and operator will throw if no data exists. Changing the subquery result type to a nullable type will allow full translation.

Above warning summerizes well, why it is happening.

[mo-esmp]

@smitpatel I changed the query and I checked for null scenario but still same thing is happening.
so the only way is changingPoint property to nullable int ?

c# var stats = (from team in context.Teams let teamCount = context.Teams.Count() let roundTeamCount = context.RoundTeams.Count(rt => rt.RoundId == roundId) let averagePoint = context.Teams.Count() == 0 ? null : (double?)context.Teams.Select(p => p.Point).Average() let maxPoint = context.Teams.Count() == 0 ? null : (int?)context.Teams.Max(t => t.Point) select new { averagePoint, maxPoint, teamCount, roundTeamCount }) .FirstOrDefault();

[smitpatel]

@mo-esmp - While having a check, removes need for throwing behavior, it is pretty difficult pattern to recognize in code (rather than human-read), so EF Core does not account for it and still goes for throwing behavior causing client eval.

Putting a cast outside of the aggregate operator has no effect. You are still computing result over non-nullable type and saving it into nullable property. You need to introduce cast inside the operator. e.g.
let maxPoint = context.Teams.Max(t => (int?)t.Point)