EF Core 6: .Any() in query filter produce "could not be translated. Either rewrite the query in a form that can be translated"

Question

I have Product entity and write a method in a repository class (using npgsql). But I get error :

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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information. System.InvalidOperationException: The LINQ expression 's => s.ProductId == EntityShaperExpression:

public class Product:IEntity
{
  public string Id { get; set; }
  public string SerialNo { get; set; }
  public string Imei { get; set; }
  public string Name { get; set; }
}   

public class ProductInput
{
  public string ProductId { get; set; }
  public string SerialNo { get; set; }
  public string Imei { get; set; }
}

public async Task<List<Product>> GetProducts(List<ProductInput> input) //EfCoreProductRepository.cs
{
      var result = (from product in (await GetDbContextAsync()).Products
                    where input.Any(s => s.ProductId == product.Id && s.SerialNo == product.SerialNo && s.Imei == product.Imei)
                    select product).ToList();
    return result;
}

How should I edit my query to avoid getting this error?

Answer 1

It looks like you're trying to retreieve DB rows that match your input rows by multiple criteria. There are multiple ways to do this, such as:

• Shoving the input data into a table-valued (SQL Server) or typed-array (Postgres) parameter, or even a JSON string blob (horrible) and doing an INNER JOIN with that parameter.
  • I may be wrong, but I don't believe Entity Framework Core supports this except for single-column filtering.
• Alternatively (and more reliably), you can build-up a new Linq query dynamically that defines the desired matches in the WHERE part.
  • The only real downside to this is that dynamic queries can't take advantage of cached-query plans or things like the Query Store.
• The failsafe approach would be to do a RBAR query by looping over input in C# and executing individual queries
  • Ew. No.

In this author's opinion, Option 2 (Dynamically-built Linq Query) is the "best" overall here, so do it like so:

First, get PredicateBuilder - it's a tiiiiiny dependency that makes it significantly easier to build-up Or predicates with Linq. It's literally a class you copy-and-paste from Albahari's website.

Second, use it like so:

public async Task<List<Product>> GetProductsAsync( List<ProductInput> input, CancellationToken cancellationToken )
{
    DbSet<Product> dbSet = await this.GetDbSetAsync();

    var predicate = PredicateBuilder.False<Product>();
    foreach( ProductInput pi in input )
    {
        predicate = predicate.Or( p => (
            p.ProductId == pi.Id
            &&
            p.SerialNo == pi.SerialNo
            &&
            p.Imei == pi.Imei
        ) );
    }

    IQueryable<Product> query = dbSet.Where( predicate );

    List<Product> results = await query.ToListAsync(cancellationToken);
    return results;
}

PredicateBuilder

For posterity, I've reproduced class PredicateBuilder here:

using System;
using System.Linq;
using System.Linq.Expressions;
using System.Collections.Generic;
 
public static class PredicateBuilder
{
  public static Expression<Func<T,Boolean>> True<T> ()  => ( f => true );
  public static Expression<Func<T,Boolean>> False<T> () => ( f => false );
 
  public static Expression<Func<T,Boolean>> Or<T>(this Expression<Func<T,Boolean>> expr1, Expression<Func<T,Boolean>> expr2)
  {
    var invokedExpr = Expression.Invoke(
      expr2,
      expr1.Parameters.Cast<Expression>()
    );

    return Expression.Lambda<Func<T,Boolean>>(
      Expression.OrElse(expr1.Body, invokedExpr),
      expr1.Parameters
    );
  }
 
  public static Expression<Func<T,Boolean>> And<T>(this Expression<Func<T,Boolean>> expr1, Expression<Func<T,Boolean>> expr2)
  {
    var invokedExpr = Expression.Invoke(
      expr2,
      expr1.Parameters.Cast<Expression>()
    );
    
    return Expression.Lambda<Func<T,Boolean>>(
      Expression.AndAlso(expr1.Body, invokedExpr),
      expr1.Parameters
    );
  }
}