EFCore compiled query can't use orderby linq statements

When i try to compile this test code:

using Microsoft.EntityFrameworkCore;

using var db = new InventoryContext();
await db.AddAsync<Item>(new() { Name = "Table", Description = "A wooden office Table with a cup holder", Type = ItemType.Furniture });
await db.SaveChangesAsync();

Console.WriteLine("Showing all entries:");

Func<InventoryContext, IAsyncEnumerable<Item>> compiled = EF.CompileAsyncQuery((InventoryContext db) => from item in db.Items orderby item.Id select item);

IAsyncEnumerable<Item> items = compiled(db);

await foreach (Item item in items)
{
    Console.WriteLine(item.Id);
    Console.WriteLine(item.Name);
    Console.WriteLine(item.Type);
    Console.WriteLine(item.Description is null ? "*no description*" : item.Description);
    Console.WriteLine();
}

it tells me that "System.Func<DatabaseTest.InventoryContext, System.Threading.Tasks.Task<System.Linq.IOrderedQueryable<DatabaseTest.Item>>>" cannot implicitely be converted to "System.Func<DatabaseTest.InventoryContext, System.Collections.Generic.IAsyncEnumerable<DatabaseTest.Item>>"

When changing line 10 to

Func<InventoryContext, IAsyncEnumerable<Item>> compiled = EF.CompileAsyncQuery((InventoryContext db) => db.Items.OrderBy(x => x.Id));

it won't work either, only when adding a .Select(x=>x) or removing the orderby part it will work. Can someone please explain why (at least it looks so for me) an ordered statement cannot be used in a compiled query?

The problem has nothing to do with incompatibilities between IOrderedQueryable , IAsyncEnumerable and CompileQueryAsync.

There's no overload of EF.CompileQueryAsync that accepts an IOrderedQueryable so one of the overloads that return a TResult is selected, perhaps this one:

Func<TContext,Task<TResult>> CompileAsyncQuery<TContext,TResult> (
    Expression<Func<TContext,TResult>> queryExpression)

There's an overload that accepts a DbSet<T> too:

CompileAsyncQuery<TContext,TResult>(Expression<Func<TContext,DbSet<TResult>>>)

The overload we need is this:

CompileAsyncQuery<TContext,TResult>(Expression<Func<TContext,IQueryable<TResult>>>)

There are both Func<> and Expression<Func<>> overloads though, so the best option is to explicitly define the expression separately, to ensure the correct overload is used. The code is a bit cleaner too.

Expression<Func<InventoryContext,IQueryable<Item>>> queryExpression = 
    db => from item in db.Items 
        orderby item.Id 
        select item;

var compiled =EF.CompileAsyncQuery(queryExpression);

This entire snippet compiles without problems:

IQueryable<Item> ItemsQuery(InventoryContext db) => 
    from item in db.Items 
    orderby item.Id 
    select item;

Expression<Func<InventoryContext,IQueryable<Item>>> queryExpression = 
    db => from item in db.Items 
        orderby item.Id 
        select item;

var compiled =EF.CompileAsyncQuery(queryExpression);
var db = new InventoryContext();

var items = compiled(db);

await foreach (Item item in items)
{
    Console.WriteLine(item.Id);
}

Just ensure that result is IQueryable. Simple adding AsQueryable() to the end of the query solves issue:

Func<InventoryContext, IAsyncEnumerable<Item>> compiled = 
   EF.CompileAsyncQuery((InventoryContext db) => 
      (from item in db.Items orderby item.Id select item).AsQueryable());