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 https://github.com/aspnet/EntityFrameworkCore/issues/14661#issuecomment-497919043

[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: https://github.com/dotnet/efcore/issues/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.

[TwentyFourMinutes]

For anyone else having the same issue, this is a possible work around. However, please do note that this method is not really sufficient for all scenarios and should be thoroughly tested before using it.

public static class DatabaseFacadeExtensions
{
    private static readonly ConcurrentDictionary<Type, string> DatabaseTypeNames = [];

    static DatabaseFacadeExtensions()
    {
        // Initially populate the global type mapper as described in https://github.com/npgsql/npgsql/issues/5508#issuecomment-1872212391.
        _ = new NpgsqlSlimDataSourceBuilder();
    }

    public static IQueryable<T> FromCollection<T>(this DatabaseFacade database, ICollection<T> collection)
        => FromCollection(database, collection, serializerOptions: null);

    public static IQueryable<T> FromCollection<T>(this DatabaseFacade database, ICollection<T> collection, JsonSerializerOptions? serializerOptions = null)
    {
        if (!database.IsNpgsql())
            throw new NotSupportedException("Only Npgsql is supported.");

        /*
         * EF-Core currently does not support generation of IN expressions for tuples https://github.com/npgsql/efcore.pg/issues/898
         * This workaround passes the collection as json to the database which we can then use to create our temporary inline table.
         * 
         * Note: Sort order of the JsonProperties and the columns in the query must match. 
         * This is by default given as described in
         * - https://learn.microsoft.com/en-us/dotnet/api/system.type.getproperties
         * - https://learn.microsoft.com/en-us/dotnet/standard/serialization/system-text-json/customize-properties
         */

        var json = JsonSerializer.Serialize(collection, serializerOptions);

        var columns = typeof(T).GetProperties()
                               .Select(p => $"\"{p.Name}\" {GetDbTypeName(p.PropertyType)}");

#pragma warning disable EF1002 // Risk of vulnerability to SQL injection.
        var query = database.SqlQueryRaw<T>(
           $$"""
           SELECT 
               *
           FROM jsonb_to_recordset({0}::jsonb) 
               AS item({{string.Join(", ", columns)}})
           """,
           parameters: json
        );
#pragma warning restore EF1002 // Risk of vulnerability to SQL injection.

        return query;
    }

    private static string GetDbTypeName(Type type)
    {
        var name = DatabaseTypeNames.GetOrAdd(
            type,
            static type =>
            {
                // We are querying the database type name by abusing the NpgsqlParameter and its DataTypeName. However, it only works if the value provided is not null.
                type = Nullable.GetUnderlyingType(type) ?? type;

                var defaultValue = type.IsValueType
                    ? Activator.CreateInstance(type)
                    : string.Empty;

                var parameterType = typeof(NpgsqlParameter<>).MakeGenericType([type]);

                var parameter = Activator.CreateInstance(parameterType, args: [string.Empty, defaultValue])!;

                var name = ((NpgsqlParameter)parameter).DataTypeName
                    ?? throw new InvalidOperationException($"The database type name could not be resolved for '{type}'.");

                return name;
            }
        );

        return name;
    }
}

This can then be used like seen blow:

var blogsSearch = new[]
{
    new Blog { Id = 1, Type = 1 },
    new Blog { Id = 2, Type = 1 }
};

var blogs = (from search in ctx.Datanase.FromCollection(blogsSearch)
             join blog in ctx.Blogs on new { search.Id, search.Type } equals new { blog.Id, blog.Type }
             select blog).ToList();