Entity framework 6 future is generating duplicate queries-CodePudding

Following code is written in entity framework 6 and entity framework plus library (futures)

using (var ctx = _contextCreator.PortalContext())
{
    var q = "";
    ctx.Database.Log = s => q =s;
    
    var a = ctx.ArticleIcons.Take(1).Future();
    var b = ctx.ArticleIcons.Take(3).Future();
    
    var aResult = a.ToList();
}

This is output of "q" variable, which is otputing generated SQL queries. Is it possible to avoid execution of 4 queries, because there are only 2 queries in the code?

SELECT TOP (1) 
    [c].[Id] AS [Id], 
    [c].[Name] AS [Name], 
    [c].[CssClass] AS [CssClass]
    FROM [dbo].[ArticleIcons] AS [c]
-- Executing at 25.3.2022. 9:41:47  01:00
-- Completed in 29 ms with result: null

SELECT TOP (3) 
    [c].[Id] AS [Id], 
    [c].[Name] AS [Name], 
    [c].[CssClass] AS [CssClass]
    FROM [dbo].[ArticleIcons] AS [c]
-- Executing at 25.3.2022. 9:41:47  01:00
-- Completed in 0 ms with result: null

Opened connection at 25.3.2022. 9:41:48  01:00
-- EF  Query Future: 1 of 2
SELECT TOP (1) 
    [c].[Id] AS [Id], 
    [c].[Name] AS [Name], 
    [c].[CssClass] AS [CssClass]
    FROM [dbo].[ArticleIcons] AS [c]
;

-- EF  Query Future: 2 of 2
SELECT TOP (3) 
    [c].[Id] AS [Id], 
    [c].[Name] AS [Name], 
    [c].[CssClass] AS [CssClass]
    FROM [dbo].[ArticleIcons] AS [c]
;

-- Executing at 25.3.2022. 9:41:48  01:00
-- Completed in 53 ms with result: SqlDataReader

Closed connection at 25.3.2022. 9:41:48  01:00

CodePudding user response：

The query is logged 2 times for the following reasons:

The first time, the library generates both queries (do not execute them, just generate them). However, to generate it correctly it must call the interceptor ReaderExecuting and ReaderExecuting which log both queries. This step is mandatory has some provider such as MySql/custom user code change the query and parameter.

The second time, after the queries have been combined, the library calls the the DbInterception.Dispatch.Command.Reader interceptor which log the combined query.

So while the query has been logged 2 times due to limitation, only the combined query has been executed in the database server.