Entity Framework Core 2.2 Compiled Query Struct Parameter Evaluating Locally

Question

I've been looking into compiled queries using Entity Framework Core. I'm on the current latest stable version 2.2.2. I was reading through this article (https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/compiled-queries-linq-to-entities) to understand Compiled Queries, and am trying to understand if this is a bug in EF Core or just something they haven't completed yet. I recognize that the article is written for EF6, but was expecting that the compiled queries would work the same way and couldn't find anything to the contrary.

Here's my DbContext setup and a simple struct for paging options:

public class BuggyDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
}

public struct PagingOptions
{
    public int Skip;
    public int Take;
}

[Table("User")]
public class User
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int UserId { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }
}

Here are my compiled queries. The first one selects a "page" of users based on a struct parameter (very similar to example from article). The second one does the same thing but accepts "skip" and "take" as individual parameters of a basic int32 type.

var badQuery = EF.CompileQuery<BuggyDbContext, PagingOptions, IEnumerable<User>>((context, paging) =>
     context.Users
         .OrderBy(u => u.LastName)
         .Skip(paging.Skip)
         .Take(paging.Take));

var goodQuery = EF.CompileQuery<BuggyDbContext, int,int, IEnumerable<User>>((context, skip, take) =>
     context.Users
         .OrderBy(u => u.LastName)
         .Skip(skip)
         .Take(take));

And here is the usage to demonstrate the problem:

 using (var db = new BuggyDbContext())
 {

     var pagingOptions = new PagingOptions {
         Skip = 0,
         Take = 25
     };
     var firstPage = badQuery.Invoke(db, pagingOptions).ToList();
     var alternateFirstPage = goodQuery.Invoke(db, pagingOptions.Skip, pagingOptions.Take).ToList();
 }

When the goodQuery runs, everything works as expected. The following shows up in the logs as the generated SQL that I'd expect:

SELECT [u].[UserId], [u].[FirstName], [u].[LastName]
FROM [User] AS [u]
ORDER BY [u].[LastName]
OFFSET @__skip ROWS FETCH NEXT @__take ROWS ONLY

However, when the badQuery runs it select ALL of the records and then evaluates the Skip and Take in memory, which would result in horrible performance.

SELECT [u].[UserId], [u].[FirstName], [u].[LastName]
FROM [User] AS [u]
ORDER BY [u].[LastName]

warn: Microsoft.EntityFrameworkCore.Query[20500]
  => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
  The LINQ expression 'Skip(__paging.Skip)' could not be translated 
and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
  => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
  The LINQ expression 'Take(__paging.Take)' could not be translated 
and will be evaluated locally.

I'd prefer to use a complex type (reference or value struct, don't care) as parameters to compiled queries for couple of very important reasons:

1. Lambda Functions have a maximum number of input parameters. If I had a query with some complex filtering, sorting, and grouping that needed multiple inputs, I'd be forced to go by some other route.
2. The input parameters are much clearer to the developer who is calling the query. Even in this example the dev calling the query would start typing query.Invoke and then be staring at 2 unnamed integer arguments in intellisense. The only way they'd know what they meant was by looking at the query. Changes to the query would be extremely dangerous if input parameters changed order or meaning.

The EF Core 3.0 Roadmap (https://learn.microsoft.com/en-us/ef/core/what-is-new/roadmap) does say they are working on their LINQ Query strategy in general (to avoid such horrible running queries, or at least making you aware before runtime or happening to catch a warning in your logs) but I expected the struct parameter to work.

Anyone have any insight here if I'm doing something wrong or if this is something that is in the works? Would you consider this a bug as well?

Answer 1

I submitted a bug report to EF team at https://github.com/aspnet/EntityFrameworkCore/issues/14857 It was closed and marked as duplicate of https://github.com/aspnet/EntityFrameworkCore/issues/13976

Was moved to backlog. Here's the response: "Based on normal triage, this is a feature for which there is a reasonable workaround and for which we have not, as yet, seen significant demand, so we're moving it to the backlog for now."