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

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?

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