EFCore编译查询无法使用LINQ排序语句

huangapple go评论72阅读模式
英文:

EFCore compiled query can't use orderby linq statements

问题

当我尝试编译这个测试代码时:

    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();
    }

它告诉我“System.Func<DatabaseTest.InventoryContext, System.Threading.Tasks.Task<System.Linq.IOrderedQueryable<DatabaseTest.Item>>>” 无法隐式转换为 “System.Func<DatabaseTest.InventoryContext, System.Collections.Generic.IAsyncEnumerable<DatabaseTest.Item>>”。

当将第10行更改为

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

也不起作用,只有在添加 .Select(x=>x) 或删除 orderby 部分时才能起作用。能否有人解释为什么(至少对我来说是这样)有序语句不能在编译查询中使用?

英文:

When i try to compile this test code:

using Microsoft.EntityFrameworkCore;

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

Console.WriteLine(&quot;Showing all entries:&quot;);

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

IAsyncEnumerable&lt;Item&gt; 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 ? &quot;*no description*&quot; : 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&lt;InventoryContext, IAsyncEnumerable&lt;Item&gt;&gt; compiled = EF.CompileAsyncQuery((InventoryContext db) =&gt; db.Items.OrderBy(x =&gt; 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?

答案1

得分: 2

问题与IOrderedQueryable、IAsyncEnumerable和CompileQueryAsync之间的不兼容性无关。

EF.CompileQueryAsync没有接受IOrderedQueryable的重载,因此选择了一个返回TResult的重载,可能是这个:

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

也有一个接受DbSet<T>的重载:

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

我们需要的重载是这个:

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

Func<>Expression<Func<>>两种重载,最好的选择是明确地分开定义表达式,以确保使用正确的重载。这样做的代码更清晰:

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

var compiled = EF.CompileAsyncQuery(queryExpression);

整个片段编译没有问题:

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);
}
英文:

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&lt;TContext,Task&lt;TResult&gt;&gt; CompileAsyncQuery&lt;TContext,TResult&gt; (
    Expression&lt;Func&lt;TContext,TResult&gt;&gt; queryExpression)

There's an overload that accepts a DbSet&lt;T&gt; too:

CompileAsyncQuery&lt;TContext,TResult&gt;(Expression&lt;Func&lt;TContext,DbSet&lt;TResult&gt;&gt;&gt;)

The overload we need is this:

CompileAsyncQuery&lt;TContext,TResult&gt;(Expression&lt;Func&lt;TContext,IQueryable&lt;TResult&gt;&gt;&gt;)

There are both Func&lt;&gt; and Expression&lt;Func&lt;&gt;&gt; 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&lt;Func&lt;InventoryContext,IQueryable&lt;Item&gt;&gt;&gt; queryExpression = 
    db =&gt; from item in db.Items 
        orderby item.Id 
        select item;

var compiled =EF.CompileAsyncQuery(queryExpression);

This entire snippet compiles without problems:

IQueryable&lt;Item&gt; ItemsQuery(InventoryContext db) =&gt; 
    from item in db.Items 
    orderby item.Id 
    select item;

Expression&lt;Func&lt;InventoryContext,IQueryable&lt;Item&gt;&gt;&gt; queryExpression = 
    db =&gt; 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);
}

答案2

得分: 1

确保结果是IQueryable。只需将AsQueryable()添加到查询末尾即可解决问题:

Func&lt;InventoryContext, IAsyncEnumerable&lt;Item&gt;&gt; compiled = 
   EF.CompileAsyncQuery((InventoryContext db) =&gt; 
      (from item in db.Items orderby item.Id select item).AsQueryable());
英文:

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

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

huangapple
  • 本文由 发表于 2023年4月6日 21:16:30
  • 转载请务必保留本文链接:https://go.coder-hub.com/75949984.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定