SQLite sample

Broken.cs

using Microsoft.EntityFrameworkCore;

var customers = new List<Customer>();
for (int i = 0; i < 20; i++)
{
    customers.Add(new Customer(i, i % 5, i * 2));
}

using (var context = new AppDbContext())
{
    context.Database.EnsureCreated();
    context.Customers.AddRange(customers);
    context.SaveChanges();
}

var customersToFind = new List<(int Id, int TenantId)>
{
    {(8, 4)},
    {(9, 4)},
    {(14, 4)}
};

using (var context = new AppDbContext())
{
    var customerFromDb = context.Customers.Where(i => customersToFind.Any(x => i.TenantId == x.TenantId && (x.Id == i.Id || x.Id == i.AlternateId))).ToArray();
    Console.WriteLine($"Found {customerFromDb.Length} customers");
}

public record Customer(int Id, int TenantId, int AlternateId);

public class AppDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("DataSource=file:memdb1?mode=memory&cache=shared");
    }

    override protected void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>().HasKey(i => new { i.Id, i.TenantId });
        modelBuilder.Entity<Customer>().HasAlternateKey(i => new { i.AlternateId, i.TenantId });
    }
}

exception

Unhandled exception. System.InvalidOperationException: The LINQ expression '__customersToFind_0
    .Any(e => StructuralTypeShaperExpression:
        Customer
        ValueBufferExpression:
            ProjectionBindingExpression: EmptyProjectionMember
        IsNullable: False
    .TenantId == e.Item2 && e.Item1 == StructuralTypeShaperExpression:
        Customer
        ValueBufferExpression:
            ProjectionBindingExpression: EmptyProjectionMember
        IsNullable: False
    .Id || e.Item1 == StructuralTypeShaperExpression:
        Customer
        ValueBufferExpression:
            ProjectionBindingExpression: EmptyProjectionMember
        IsNullable: False
    .AlternateId)' 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.
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.TranslateSubquery(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Sqlite.Query.Internal.SqliteSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression, Boolean applyDefaultTypeMapping)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression, Boolean applyDefaultTypeMapping)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateExpression(Expression expression, Boolean applyDefaultTypeMapping)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateLambdaExpression(ShapedQueryExpression shapedQueryExpression, LambdaExpression lambdaExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateWhere(ShapedQueryExpression source, LambdaExpression predicate)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at Program.<Main>$(String[] args)

Working.cs

using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore;

var customers = new List<Customer>();
for (int i = 0; i < 20; i++)
{
    customers.Add(new Customer(i, i % 5, i * 2));
}

using (var context = new AppDbContext())
{
    context.Database.EnsureCreated();
    context.Customers.AddRange(customers);
    context.SaveChanges();
}

var customersToFind = new List<(int Id, int TenantId)>
{
    {(8, 4)},
    {(9, 4)},
    {(14, 4)}
};

using (var context = new AppDbContext())
{
    // Broken
    // var customerFromDb = context.Customers.Where(i => customersToFind.Any(x => i.TenantId == x.TenantId && (x.Id == i.Id || x.Id == i.AlternateId))).ToArray();
    // Working
     var customerFromDb = context.Customers.Where(BuildQueryExpression(customersToFind)).ToArray();
    Console.WriteLine($"Found {customerFromDb.Length} customers");
}

static Expression<Func<Customer, bool>> BuildQueryExpression(List<(int Id, int TenantId)> customersToFind)
{
    var expressions = new List<Expression>();

    foreach (var item in customersToFind)
    {
        Expression<Func<Customer, bool>> exp = x => x.TenantId == item.TenantId && (x.Id == item.Id || x.AlternateId == item.Id);
        expressions.Add(exp);
    }

    Expression combined = expressions.Aggregate((left, right) => left is LambdaExpression lambda ? Expression.OrElse(lambda.Body, ((LambdaExpression)right).Body) : Expression.OrElse(left, ((LambdaExpression)right).Body));

    var param = Expression.Parameter(typeof(Customer), "x");
    ParameterReplacer replacer = new(param);
    // Replace the x => parameter in all the lambda to use the same parameter instance
    combined = replacer.Visit(combined);
    return Expression.Lambda<Func<Customer, bool>>(combined, param);
}

public record Customer(int Id, int TenantId, int AlternateId);

class ParameterReplacer : ExpressionVisitor
{
    private readonly ParameterExpression _parameter;

    internal ParameterReplacer(ParameterExpression parameter) => _parameter = parameter;

    protected override Expression VisitParameter(ParameterExpression node) => _parameter;
}

public class AppDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("DataSource=file:memdb1?mode=memory&cache=shared");
    }

    override protected void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>().HasKey(i => new { i.Id, i.TenantId });
        modelBuilder.Entity<Customer>().HasAlternateKey(i => new { i.AlternateId, i.TenantId });
    }
}