2

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.

sliminytim
  • 21
  • 2
  • Evaluating the query client side (as it was in pre EFC 3.0)? – Ivan Stoev Apr 05 '22 at 17:17
  • Evaluating it client side is no good, it's far too slow and can run into memory limitations. – sliminytim Apr 06 '22 at 20:24
  • Seriously? With Access database which is not server anyway? You are using a combination of obsolete client side database engine with provider limitations, not sure what "solution" are you looking for. Good luck. – Ivan Stoev Apr 07 '22 at 01:18
  • I tried client evaluation and it used all the memory in my 32bit application and crashed. This Access database is a big boi. – sliminytim Apr 07 '22 at 09:43
  • Then you have the worst of the worst - big obsolete client side database with provider limitations :) Is your problem with this specific query only, or in general? I mean, if it is only for this specific code, to look for working workaround (probably with some minimal client side buffering, like HashSet for keys and List for results, everything else just no tracking query result enumeration). Also are you sure you can store the results of the desired query (let assume it is possible to generate it) in array or list in your 32bit app memory? – Ivan Stoev Apr 07 '22 at 13:22
  • Client evaluation btw doesn't mean to store all the data in memory. It has to read all the data, create short lived objects and store only the needed ones. The others will be garbage collected. This also means not using buffering client side (`Enumerable` class extensions) LINQ methods like `GroupBy`, `Distinct`, `OrderBy` etc. And in case `OrderNumber` column has index and database can easily return the results ordered by it, then everyting can happen on a single enumeration pass w/o additional storage except the list for the results. – Ivan Stoev Apr 07 '22 at 13:29

2 Answers2

0

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);
  }
}
ArwynFr
  • 1,383
  • 7
  • 13
  • Thank you for your reply, I am currently away from my work machine but will give this a try in a the morning. – sliminytim Apr 06 '22 at 20:24
  • Sadly I got "Enumerator failed to MoveNextAsync" System.InvalidOperationException when trying this code. Only difference I made was to add a where clause to the first half of the query and ordered it like stated. – sliminytim Apr 07 '22 at 09:45
  • Sounds like your database does not support simultaneous query enumerations ; I've updated the code to enumerate the order numbers before sub-querying. – ArwynFr Apr 07 '22 at 13:34
0

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!

sliminytim
  • 21
  • 2