dotnet / dotnet/efcore

Multiple non-related dbset counts without filter executes as separate queries

Open
#27,728 11 comments 1 reaction 0 assignees View on GitHub
area-query customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

Including multiple non-related dbset counts in one query will perform multiple SQL queries if the dbsets are not filtered by a dummy where clause. `AsSingleQuery` does not help. I would prefer not having to remember to add the `.Where(x => true)` part to prevent multiple queries.

[See full gist here](https://gist.github.com/joakimriedel/35cfad1e71afe795c42c1a2b17ccab58).

### Include your code

```C#
// info: Microsoft.EntityFrameworkCore.Database.Command[20101]
// Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
// SELECT COUNT(*)
// FROM [Users] AS [u]
// info: Microsoft.EntityFrameworkCore.Database.Command[20101]
// Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
// SELECT COUNT(*)
// FROM [Animals] AS [a]
// info: Microsoft.EntityFrameworkCore.Database.Command[20101]
// Executed DbCommand (8ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
// SELECT TOP(1) 1
// FROM [Users] AS [u]

var bad = await context.Users.Select(u => new {
userCount = context.Users.Count(),
animalCount = context.Animals.Count()
}).FirstAsync();

// info: Microsoft.EntityFrameworkCore.Database.Command[20101]
// Executed DbCommand (13ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
// SELECT TOP(1) (
// SELECT COUNT(*)
// FROM [Users] AS [u0]) AS [userCount], (
// SELECT COUNT(*)
// FROM [Animals] AS [a]) AS [animalCount]
// FROM [Users] AS [u]

var good = await context.Users.Select(u => new {
userCount = context.Users.Where(x => true).Count(),
animalCount = context.Animals.Where(x => true).Count()
}).FirstAsync();
```

### Include provider and version information

EF Core version: 6.0.3
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.