Skip to content

Generate IN expression for tuples #898

Open

Description

@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)

Activity

  1. added this to the Backlog milestone on Jun 3, 2019
  2. smitpatel commented on Jun 4, 2019

    @smitpatel

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

  3. toddams commented on Jul 29, 2020

    @toddams

    Is there any plan for this feature?

  4. roji commented on Jul 29, 2020

    @roji
    MemberAuthor

    @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.

  5. roji commented on Oct 6, 2023

    @roji
    MemberAuthor

    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,

  6. malix0 commented on Oct 6, 2023

    @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.

  7. TwentyFourMinutes commented on Jan 7, 2025

    @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();
  8. added theissue type on Jun 14, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions