Linq to SQL cannot translate because RowNumberExpression isn't supported

Question

I have been trying to run a query where I want to group all the records by a specific property, then select the first from each list. I've been through a few iterations of this but it's safe to say the EF Core 3.0 onwards makes this a very hard activity to achieve. I think I might be hitting up against limitations of the JET provider however as I get errors about RowNumberExpression not being able to be translated. The database in question is a very old Access 97 database, which I know is very old and shouldn't be using but I don't have a choice here.

Anyway the query I have ended up with for the time being is as follows:

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

I have been through a few other iterations using GroupBy and then taking the first from each group but it yields similar results.

The error I get is as follows:

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

I have seen other solutions to this problem on stack overflow but none of them seem to help at all. It seems like a simple thing to me but it also seems like it's an impossible task, but maybe I am being deluded when I think it's a simple task.

Any help with this would be much appreciated.

Answer 1

So I worked with a DBA on my team and we managed to come up with a SQL version of what I needed, which worked really well when injecting directly into EF as raw SQL. The next step I took was to download a program called Linqer. I created a SQL Server version of the Access database so that Linqer could connect to it and test, then I put the SQL version of the query into Linqer and it automatically converted the SQL to Linq. With a few tweaks it has given me exactly what I wanted. The Linq if anyone is interested is here:

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

This runs very quickly on that huge Access database, especially with the date constraints that I've added in. I think the key here is using the Min function, which Access is able to understand. Thanks to those who attempted to help me!

Answer 2

This is a known limitation of EF core 6.

Unless your database has a relationship that allows an OrderNumber->FirstDespatch navigation, you need to make subqueries. Also you should add an order by clause to make the result consistent.

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