Multi Query Selects with EF Core

Often there is a situation where you need to load several pieces of information from a database or table at the same time in order to display certain content. The prime example of such a situation is paging:

• you need the total number of entries, so that the number of pages can be calculated
• you need the current page of entries, e.g. page 7 with 50 entries per page

With a manual select this is no problem, it’s super easy e.g. Dapper - a simple object mapper for .NET .

 1// sql query - please dont use * in your prod code, this is just a sample.
 2const string query = @"
 3    SELECT * FROM Users WHERE IsActive = @isActive
 4                        ORDER BY Name
 5                        OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY;
 6
 7    SELECT COUNT(*) FROM Users WHERE IsActive = @isActive;";
 8
 9// load
10var sqlParams = new { isActive = true, skip = 10, take = 5 };
11using var multiResult = await myConnection
12                            .QueryMultipleAsync(query, sqlParams)
13                            .ConfigureAwait(false);
14
15// read
16var users           = multi.Read<User>().ToList();
17var totalUserCount  = multi.ReadSingle<int>();
18
19// return
20return new(users, totalUserCount);

Here the query is performing two selects, the first retrieves all the users which are active and the second one retrieves the count of all users which are active. myConnection.QueryMultiple is used to execute the query, which returns multiple result sets. You can use multi.Read<T>() method to retrieve the results. multiResult.Read<User>().ToList(); retrieves all the rows from the first query and returns it as a list of ‘User’ objects. multiResult.ReadSingle<int>(); retrieves the single integer from the second query.

Not supported in EF Core by default

The problem is: this scenario, although it is a totally simple everyday scenario, was never supported in EF Core and is not until today (January 2023). For years people ask for this feature, for years people ask for it - but for years there is also the answer: it is on the roadmap: Execute multiple LINQ queries in a single round-trip (aka Expose batching read API to users) And although this scenario should affect almost every application, in my eyes, features that don’t affect so many applications and should be worlds less important in terms of priority have been coming lately.

The Community

Thanks to the community - and especially the developers behind ZZZ Projects - we have the possibility to run several queries in one round trip:

 1IOrderedQueryable<UserEntity> queryUsers = myDbContext.Users.OrderBy(u => u.Name);
 2
 3// queries
 4QueryFutureEnumerable<UserEntity> usersQuery = queryUsers.Skip(skip).Take(take)
 5                            .Future(); // take care of this!
 6
 7QueryFutureValue<int> totalUsersCountQuery = queryUsers
 8                            .DeferredCount().FutureValue(); // take care of this!
 9
10// execute query
11List<UserEntity> users = await usersQuery.ToListAsync(ct) // this executes both queries!
12                                    .ConfigureAwaitFalse();
13
14int totalUserCount =     await totalUsersCountQuery.ValueAsync(ct)
15                                    .ConfigureAwaitFalse();
16
17// return
18return new(users, totalUserCount);

This Query Future is an extension for Entity Framework Core (EF Core) that provides additional features for querying data in a database. One of the main features of EF Core Plus Query Future is the ability to perform multiple queries asynchronously and combine the results into a single queryable object. The Future() and FutureValue() (and more!) methods are available in the Z.EntityFramework.Plus.EFCore NuGet package.