Linq to SQL 无法转换，因为不支持 RowNumberExpression

Question

我一直在尝试运行一个查询，希望按特定属性对所有记录进行分组，然后从每个列表中选择第一个。我已经经历了几次迭代，但可以肯定地说，EF Core 3.0以后的版本使这成为一项非常难以实现的活动。然而，我认为我可能遇到了JET提供者的限制，因为我收到了关于RowNumberExpression无法翻译的错误。有问题的数据库是一个非常旧的Access 97数据库，我知道它非常旧，不应该使用，但我在这里别无选择。

不管怎样，我暂时结束的查询如下：

var sons = await _snContext.TDespatch
.Select(x => x.OrderNumber)
.Distinct()
.SelectMany(x => _snContext.TDespatch.Where(d => x == d.OrderNumber).Take(1))
.ToArrayAsync();

我已经使用GroupBy进行了其他几次迭代，然后从每组中取第一次，但它产生了类似的结果。

我得到的错误如下：

Microsoft.EntityFrameworkCore.Query: Error: An exception occurred while iterating over the results of a query for context type 'SNDBConnector.Contexts.SNDBContext'.
System.InvalidOperationException: The LINQ expression '[Microsoft.EntityFrameworkCore.Query.SqlExpressions.RowNumberExpression]' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
at EntityFrameworkCore.Jet.Query.Sql.Internal.JetQuerySqlGenerator.VisitRowNumber(RowNumberExpression rowNumberExpression)
at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitProjection(ProjectionExpression projectionExpression)
at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at EntityFrameworkCore.Jet.Query.Sql.Internal.JetQuerySqlGenerator.<VisitSelect>b__7_0(ProjectionExpression e)
at EntityFrameworkCore.Jet.Query.Sql.Internal.JetQuerySqlGenerator.GenerateList[T](IReadOnlyList`1 items, Action`1 generationAction, Action`1 joinAction)
at EntityFrameworkCore.Jet.Query.Sql.Internal.JetQuerySqlGenerator.VisitSelect(SelectExpression selectExpression)
at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at EntityFrameworkCore.Jet.Query.Sql.Internal.JetQuerySqlGenerator.VisitSelect(SelectExpression selectExpression)
at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitInnerJoin(InnerJoinExpression innerJoinExpression)
at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at EntityFrameworkCore.Jet.Query.Sql.Internal.JetQuerySqlGenerator.VisitSelect(SelectExpression selectExpression)
at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.GetCommand(SelectExpression selectExpression)
at Microsoft.EntityFrameworkCore.Query.Internal.RelationalCommandCache.GetRelationalCommand(IReadOnlyDictionary`2 parameters)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
at EntityFrameworkCore.Jet.Storage.Internal.JetExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
System.InvalidOperationException: The LINQ expression '[Microsoft.EntityFrameworkCore.Query.SqlExpressions.RowNumberExpression]' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
at EntityFrameworkCore.Jet.Query.Sql.Internal.JetQuerySqlGenerator.VisitRowNumber(RowNumberExpression rowNumberExpression)
at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitProjection(ProjectionExpression projectionExpression)
at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at EntityFrameworkCore.Jet.Query.Sql.Internal.JetQuerySqlGenerator.<VisitSelect>b__7_0(ProjectionExpression e)
at EntityFrameworkCore.Jet.Query.Sql.Internal.JetQuerySqlGenerator.GenerateList[T](IReadOnlyList`1 items, Action`1 generationAction, Action`1 joinAction)
at EntityFrameworkCore.Jet.Query.Sql.Internal.JetQuerySqlGenerator.VisitSelect(SelectExpression selectExpression)
at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at EntityFrameworkCore.Jet.Query.Sql.Internal.JetQuerySqlGenerator.VisitSelect(SelectExpression selectExpression)
at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitInnerJoin(InnerJoinExpression innerJoinExpression)
at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at EntityFrameworkCore.Jet.Query.Sql.Internal.JetQuerySqlGenerator.VisitSelect(SelectExpression selectExpression)
at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.GetCommand(SelectExpression selectExpression)
at Microsoft.EntityFrameworkCore.Query.Internal.RelationalCommandCache.GetRelationalCommand(IReadOnlyDictionary`2 parameters)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
at EntityFrameworkCore.Jet.Storage.Internal.JetExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
Exception thrown: 'System.InvalidOperationException' in System.Private.CoreLib.dll

我已经看到了关于堆栈溢出的其他解决方案，但似乎都没有帮助。对我来说，这似乎是一件简单的事情，但也似乎是一项不可能完成的任务，但当我认为这是一项简单的任务时，也许我被欺骗了。

如有任何帮助，我们将不胜感激。

Answer 1

这是EF核心6的已知限制。

除非您的数据库具有允许OrderNumber的关系->FirstDespatch导航，您需要进行子查询。此外，您还应该添加order by子句以使结果一致。

private async IAsyncEnumerable<TDespatch> GetFirstByOrderNumberAsync()
{
foreach(var number in await _snContext.TDespatch
.Select(x => x.OrderNumber)
.Distinct()
.ToListAsync()
.ConfigureAwait(false))
{
yield return await _snContext.TDespatch
.AsNoTracking()
.Where(despatch => number == despatch.OrderNumber)
// insert an order by clause here
.FirstAsync()
.ConfigureAwait(false);
}
}

Answer 2

因此，我与团队中的一位DBA合作，我们成功地提出了我所需要的SQL版本，当将其作为原始SQL直接注入EF时，效果非常好。我采取的下一步是下载一个名为Linqer的程序。我创建了Access数据库的SQL Server版本，以便Linqer可以连接到它并进行测试，然后我将查询的SQL版本放入Linqer中，它会自动将SQL转换为Linq。经过一些调整，它给了我想要的东西。如果有人感兴趣的话，Linq在这里：

await (from Despatch in context.Despatch
where Despatch.DespatchDate >= startDate && Despatch.DespatchDate <= endDate
group Despatch by new
{
Despatch.OrderNumber
} into g
select new Despatch
{
OrderNumber = g.Key.OrderNumber,
DespatchDate = g.Min(p => p.DespatchDate),
RepCalNumber = g.Min(p => p.RepCalNumber),
SerialNumber = g.Min(p => p.SerialNumber)
})
.OrderByDescending(x => x.DespatchDate)
.ToArrayAsync();

这在庞大的Access数据库上运行得非常快，尤其是在我添加了日期限制的情况下。我认为这里的关键是使用Min函数，Access能够理解它。感谢那些试图帮助我的人！