Microsoft says that the Entity Framework Core 2.1 generates a GROUPBY
statement to the server. I have an Azure Function using the Entity Framework Core 2.1.4 but still got:
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'Sum()' could not be translated and will be evaluated locally.
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'Count()' could not be translated and will be evaluated locally.
My query is:
var q = await db.TblRainGauging5minutes
.Where(x => x.G_ID == 1)
.GroupBy(x => new { x.G_ID })
.Select(g => new {
Id = g.Key.G_ID,
Total = g.Sum(v => v.R_Value ?? 0),
Rows = g.Count()
})
.ToListAsync();
Here's my project setting:
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<TargetFramework>netstandard2.0</TargetFramework>
<AzureFunctionsVersion>v2</AzureFunctionsVersion>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="AutoMapper.Extensions.ExpressionMapping" Version="2.0.0" />
<PackageReference Include="AutoMapper.Extensions.Microsoft.DependencyInjection" Version="6.0.0" />
<PackageReference Include="AzureFunctions.Autofac" Version="3.0.6" />
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="2.1.4" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.1.4" />
<PackageReference Include="Microsoft.Extensions.Logging.Debug" Version="2.1.1" />
<PackageReference Include="Microsoft.NET.Sdk.Functions" Version="1.0.24" />
<PackageReference Include="Newtonsoft.Json" Version="11.0.2" />
</ItemGroup>
Is anything wrong in my setting or code? Maybe it just doesn't work in Azure function 2? I have changed the target framework from netstandard2.0
to netcoreapp2.1.4
and I still get the problem.
I was able to reproduce the problem with pure .NetCore project, so Azure Function is not the issue. The real problem may be the sql server. When I target sql server 2012, I can always get the problem; but when I target sql server 2016, the GROUP BY statement shows and I don't see the warning anymore. SQL profiler shows the same result.