Generate IN expression for tuples

[roji]

We could translate to the following PostgreSQL-specific syntax:

SELECT *
FROM Table
WHERE (Id, Type) IN ((1, 1), (2, 1))

LINQ for this could be:

var blogs = ctx.Blogs
   .Where(p => (new[] { new { Id = 1, Type = 1, Title = "New" } })
      .Contains(new { p.Id, p.Type, p.Title }))
   .ToList();

Or for entity equality:

var blogs = ctx.Blogs.Where(b => new[]
{
    new Blog { Id = 1, Type = 1 },
    new Blog { Id = 2, Type = 1 }
}.Contains(b)).ToList();

We could even translate Any/All to this.

Originally requested by @dmitryshunkov in dotnet/efcore#14661 (comment)

[smitpatel]

This could work by overriding VisitNew in SqlTranslator and returning appropriate bracket notation printing expression and compose on top of that.

[toddams]

Is there any plan for this feature?

[roji]

@toddams this is in the backlog, so there aren't immediate plans to tackle this... But it's definitely something I'd like to do at some point. More people upvoting this could help prioritize it, and of course anyone is free to work on it.

[roji]

Note that ideally we'd do this both for inline collections (where the new array expression is inside the query) and for parameter collections. The latter is a bit tricky in terms of the type of the array element being used, and how we match its properties to the Contains item.

Also, check which databases support WHERE (1,2) IN ((3, 4), (5, 6)) syntax.. IIRC most/all do except for SQL Server, if that's the case we should do something at the EF level. Relevant issue there: dotnet/efcore#11799,

[malix0]

I add my vote to implement this feature.

It will be critical to query the partitioned tables to properly access the data using the partitioned indexes.