EntityFramework-Plus: Query Future does not seem to work as expected. Two DB calls being made

1. Description

I followed the documentation on Query Future in order to optimize my pagination queries. However, I am not able to get the desired behavior to work.

The expectation was that both queries would be executed together in a single execute step.

2. Example code

app.MapGet("/people", (AppDbContext dbContext, 
    [FromQuery] int page = 1, 
    [FromQuery] int pageSize = 10) =>
{
    var query = dbContext.People.AsQueryable();

    var futureItems = query.Skip((page - 1) * pageSize)
        .Take(pageSize)
        .Future();
    
    var futureCount = query.DeferredCount().FutureValue();
    
    var items = futureItems.ToList();
    var count = futureCount.Value;
    
    return new { count, items };
});

3. Issue

However, I see two separate database calls and executions that waterfall one after the other.

I have tested with both ~MSSQL~ and MariaDB (MySql)

To aid in debugging, I added .NET Aspire to a minimal example, and the following is the database call trace.

MariaDB image

~MSSQL~ image image

As you can see, despite following the documentation, two separate calls are still being made.

4. Project

I’ve tried to produce a minimal example. Here is the GitHub project. Might require the latest VS2022 preview for aspire preview 3 bits.

https://github.com/OliverRC/QueryFutureIssue

Further technical details

  • EF version: [EF Core v8.0.2]
  • EF Plus version: [EF Plus Core v8.102.1]
  • Database Server version: [~SQL Server 2022~ / MariaDB 11.2.3]
  • Database Provider version (NuGet): [~Microsoft.EntityFrameworkCore.SqlServer v8.0.2~ / Pomelo.EntityFrameworkCore.MySql v8.0.0]

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Reactions: 2
  • Comments: 15 (7 by maintainers)

Most upvoted comments

Hello @OliverRC ,

The v8.102.2.2 is now available.

Could you try it and let us know if everything is now working as expected?

Best Regards,

Jon

Hello @OliverRC ,

My bad, this request was a little bit lost on our side.

We were waiting for the answer about the SQL Server provided, which you confirmed last week was working.

The fix for Pomelo in the future has been merged with our master. We will support it only starting from EF Core 5+ (EF Core 2 and EF Core 3 still have an issue).

However, the new version will only be released in around 2 or 3 weeks (Should be April 9 or April 16).

Best Regards,

Jon

Hi @JonathanMagnan

[1] I ran SQL Server Profiler on my test project and I can verify I am seeing the same results as you from the trace

exec sp_executesql N'-- EF+ Query Future: 1 of 2
SELECT [p].[Id], [p].[Email], [p].[Name], [p].[ShirtSize]
FROM [People] AS [p]
ORDER BY (SELECT 1)
OFFSET @Z_1___p_0 ROWS FETCH NEXT @Z_1___p_1 ROWS ONLY
;

-- EF+ Query Future: 2 of 2
SELECT COUNT(*)
FROM [People] AS [p]
;

',N'@Z_1___p_0 int,@Z_1___p_1 int',@Z_1___p_0=0,@Z_1___p_1=10

So it look like the MSSQL integration is indeed working. However, the way EF “sees” it’s execution threw me off because in the console it still “looks” to be two separate commands See below

image

I am going to dig a little deeper into tracing/profiling MariaDB/MySQL.

[2] Regarding Pomelo support, I would be interested in this. We are paid customers to your EF6 and EF Core Bulk Extensions library which DOES support Pomelo and so just assumed the EntityFramework-Plus library would as it’s from the same vendor and EF wizards 😃

Hello @OliverRC ,

It worked great on our side for SQL Server:

exec sp_executesql N'-- EF+ Query Future: 1 of 2
SELECT [p].[Id], [p].[Email], [p].[Name], [p].[ShirtSize]
FROM [People] AS [p]
ORDER BY (SELECT 1)
OFFSET @Z_1___p_0 ROWS FETCH NEXT @Z_1___p_1 ROWS ONLY
;

-- EF+ Query Future: 2 of 2
SELECT COUNT(*)
FROM [People] AS [p]
;

',N'@Z_1___p_0 int,@Z_1___p_1 int',@Z_1___p_0=0,@Z_1___p_1=10

Only 1 SQL has been executed.

Hello @OliverRC ,

Thank you for reporting. Looking at the code, Pomelo has been disabled for the QueryFuture feature. However, Pomelo was disabled years ago, so we will look for the reason, as perhaps we had to do it when we started this library with EF Core 2 and since then, a lot of time has passed.

Best Regards,

Jon