Contains on a tuple collection doesn't work

[fiseni]

Hello,

This is not a new topic. I see there are closed issues back in 2017, version 2.1.0. Issues: #9424, #10384. The PR is #9522.
But, this doesn't seem to work anymore. Did we drop the support for it?

Sample App

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net7.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="7.0.3" />
  </ItemGroup>

</Project>

using Microsoft.EntityFrameworkCore;

var connectionString = "Server=(localdb)\\mssqllocaldb;Database=EFCoreTuple;Trusted_Connection=True;";
var dbContextOptions = new DbContextOptionsBuilder<AppDbContext>().UseSqlServer(connectionString).Options;

using (var dbContext = new AppDbContext(dbContextOptions))
{
    await dbContext.Database.EnsureDeletedAsync();
    await dbContext.Database.EnsureCreatedAsync();

    var customers = new List<Customer>
    {
        new Customer {Id1 = 1, Id2 = 1, X1 = 1, X2 = 1},
        new Customer {Id1 = 2, Id2 = 2, X1 = 2, X2 = 2},
        new Customer {Id1 = 3, Id2 = 3, X1 = 3, X2 = 3},
    };

    dbContext.Customers.AddRange(customers);
    await dbContext.SaveChangesAsync();
}

using (var dbContext = new AppDbContext(dbContextOptions))
{
    var filter = new[]
    {
        Tuple.Create(1, 1),
        Tuple.Create(2, 2)
    };

    // For composite keys
    var result1 = await dbContext.Customers
        .Where(x => filter.Contains(new Tuple<int, int>(x.Id1, x.Id2)))
        .ToListAsync();

    Console.WriteLine($"Found {result1.Count} records!");

    // For simple props
    var result2 = await dbContext.Customers
        .Where(x => filter.Contains(new Tuple<int, int>(x.X1, x.X2)))
        .ToListAsync();

    Console.WriteLine($"Found {result2.Count} records!");
}

class Customer
{
    // Keys
    public int Id1 { get; set; }
    public int Id2 { get; set; }

    // Simple props
    public int X1 { get; set; }
    public int X2 { get; set; }
}

class AppDbContext : DbContext
{
    public DbSet<Customer> Customers => Set<Customer>();

    public AppDbContext(DbContextOptions<AppDbContext> options)
        : base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>().HasKey(x=> new { x.Id1, x.Id2 });
    }
}

Stack trace

System.InvalidOperationException
  HResult=0x80131509
  Message=The LINQ expression 'DbSet<Customer>()
    .Where(c => __filter_0
        .Contains(new Tuple<int, int>(
            c.Id1, 
            c.Id2
        )))' 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.
  Source=Microsoft.EntityFrameworkCore
  StackTrace:
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   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__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.<ToListAsync>d__65`1.MoveNext()
   at Program.<<Main>$>d__0.MoveNext() in D:\Projects\NET\Temp\EFCoreTuple\EFCoreTuple\Program.cs:line 30

[ErikEJ]

Maybe it worked in 2.1 due to the query running client side?

[fiseni]

I suspect that was the case. I'm checking the tests in the PR. There are tests for the expression. As for tests for the generated SQL, it seems it is expected not to be translated, but evaluated client side? To be honest I don't get what we're asserting in these tests.

        public override void Contains_with_local_tuple_array_closure()
        {
            base.Contains_with_local_tuple_array_closure();

            AssertSql(
                @"SELECT [o].[OrderID], [o].[ProductID], [o].[Discount], [o].[Quantity], [o].[UnitPrice]
FROM [Order Details] AS [o]",
                //
                @"SELECT [o].[OrderID], [o].[ProductID], [o].[Discount], [o].[Quantity], [o].[UnitPrice]
FROM [Order Details] AS [o]");
        }

[roji]

Yeah, it's very likely this was simply client-evaluating in EF Core 2.1.

var filter = new[]
{
   Tuple.Create(1, 1),
   Tuple.Create(2, 2)
};

var result2 = await dbContext.Customers
    .Where(x => filter.Contains(new Tuple<int, int>(x.X1, x.X2)))
    .ToListAsync();

Some relational databases (e.g. PG) support expressing this via WHERE (x1, x2) IN ((1, 1), (2, 2)), but IIRC SQL Server does not. So I don't think this is translatable there.

Note that #26822 would allow expressing row values via C# value tuples - that issue is for comparison purposes but this could be made to work for the scenario above, on databases where that's supported.

[fiseni]

Thanks, @roji

I assume the only way is to dynamically generate the expression and use OR (at least for a small list with few items). Does anyone have a better suggestion on how to accomplish it in EF?

Btw, I have no further questions here, so the issue can be closed. Thanks.

[roji]

Yes, you'll need to manually create the expression tree for the Where operator via the factory APIs on the Expression class, and then pass that as the argument to Where. One good way to start is to inspect the expression tree on the IQueryable that's produced from your target query (look at it with a debugger without calling e.g. ToListAsync), and to reproduce the same thing.