public SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList <SqlExpression> arguments)
        {
            // TODO: Fully support List<>

            if (arguments.Count == 0 || !arguments[0].Type.IsArray &&
                (!arguments[0].Type.IsGenericType || arguments[0].Type.GetGenericTypeDefinition() != typeof(List <>)))
            {
                return(null);
            }

            var arrayOperand = arguments[0];

            if (method.IsClosedFormOf(SequenceEqual) && arguments[1].Type.IsArray)
            {
                return(_sqlExpressionFactory.Equal(arrayOperand, arguments[1]));
            }

            // Predicate-less Any - translate to a simple length check.
            if (method.IsClosedFormOf(EnumerableAnyWithoutPredicate))
            {
                return
                    (_sqlExpressionFactory.GreaterThan(
                         _jsonPocoTranslator.TranslateArrayLength(arrayOperand) ??
                         _sqlExpressionFactory.Function("cardinality", arguments, typeof(int?)),
                         _sqlExpressionFactory.Constant(0)));
            }

            // Note that .Where(e => new[] { "a", "b", "c" }.Any(p => e.SomeText == p)))
            // is pattern-matched in AllAnyToContainsRewritingExpressionVisitor, which transforms it to
            // new[] { "a", "b", "c" }.Contains(e.SomeText).
            // Here we go further, and translate that to the PostgreSQL-specific construct e.SomeText = ANY (@p) -
            // this is superior to the general solution which will expand parameters to constants,
            // since non-PG SQL does not support arrays. If the list is a constant we leave it for regular IN
            // (functionality the same but more familiar).

            // TODO: The following does not work correctly if there are any nulls in a parameterized array, because
            // of null semantics (test: Contains_on_nullable_array_produces_correct_sql).
            // https://github.com/aspnet/EntityFrameworkCore/issues/15892 tracks caching based on parameter values,
            // which should allow us to enable this and have correct behavior.

            // We still apply this translation when it's on a column expression, since that can't work anyway with
            // EF Core's parameter to constant expansion

            if (method.IsClosedFormOf(Contains) &&
                arrayOperand is ColumnExpression &&
                //!(arrayOperand is SqlConstantExpression) &&   // When the null semantics issue is resolved
                _sqlExpressionFactory.FindMapping(arrayOperand.Type) != null)
            {
                return(_sqlExpressionFactory.ArrayAnyAll(arguments[1], arrayOperand, ArrayComparisonType.Any, "="));
            }

            // Note: we also translate .Where(e => new[] { "a", "b", "c" }.Any(p => EF.Functions.Like(e.SomeText, p)))
            // to LIKE ANY (...). See NpgsqlSqlTranslatingExpressionVisitor.VisitMethodCall.

            return(null);
        }
Beispiel #2
0
        public virtual SqlExpression Translate(
            SqlExpression instance,
            MethodInfo method,
            IReadOnlyList <SqlExpression> arguments,
            IDiagnosticsLogger <DbLoggerCategory.Query> logger)
        {
            if (instance != null && instance.Type.IsGenericList() && method.Name == "get_Item" && arguments.Count == 1)
            {
                return
                    // Try translating indexing inside json column
                    (_jsonPocoTranslator.TranslateMemberAccess(instance, arguments[0], method.ReturnType) ??
                     // Other types should be subscriptable - but PostgreSQL arrays are 1-based, so adjust the index.
                     _sqlExpressionFactory.ArrayIndex(instance, GenerateOneBasedIndexExpression(arguments[0])));
            }

            if (arguments.Count == 0)
            {
                return(null);
            }

            var array = arguments[0];

            if (!array.Type.TryGetElementType(out var elementType))
            {
                return(null); // Not an array/list
            }
            // The array/list CLR type may be mapped to a non-array database type (e.g. byte[] to bytea, or just
            // value converters). Make sure we're dealing with an array
            // Regardless of CLR type, we may be dealing with a non-array database type (e.g. via value converters).
            if (array.TypeMapping is RelationalTypeMapping typeMapping &&
                !(typeMapping is NpgsqlArrayTypeMapping) && !(typeMapping is NpgsqlJsonTypeMapping))
            {
                return(null);
            }

            if (method.IsClosedFormOf(SequenceEqual) && arguments[1].Type.IsArray)
            {
                return(_sqlExpressionFactory.Equal(array, arguments[1]));
            }

            // Predicate-less Any - translate to a simple length check.
            if (method.IsClosedFormOf(EnumerableAnyWithoutPredicate))
            {
                return(_sqlExpressionFactory.GreaterThan(
                           _jsonPocoTranslator.TranslateArrayLength(array) ??
                           _sqlExpressionFactory.Function(
                               "cardinality",
                               arguments,
                               nullable: true,
                               argumentsPropagateNullability: TrueArrays[1],
                               typeof(int)),
                           _sqlExpressionFactory.Constant(0)));
            }

            // Note that .Where(e => new[] { "a", "b", "c" }.Any(p => e.SomeText == p)))
            // is pattern-matched in AllAnyToContainsRewritingExpressionVisitor, which transforms it to
            // new[] { "a", "b", "c" }.Contains(e.Some Text).

            if (method.IsClosedFormOf(Contains) &&
                (
                    // Handle either parameters (no mapping but supported CLR type), or array columns. We specifically
                    // don't want to translate if the type mapping is bytea (CLR type is array, but not an array in
                    // the database).
                    array.TypeMapping == null && _typeMappingSource.FindMapping(array.Type) != null ||
                    array.TypeMapping is NpgsqlArrayTypeMapping
                ) &&
                // Exclude arrays/lists over Nullable<T> since the ADO layer doesn't handle them (but will in 5.0)
                Nullable.GetUnderlyingType(elementType) == null)
            {
                var item = arguments[1];

                switch (array)
                {
                // When the array is a column, we translate to array @> ARRAY[item]. GIN indexes
                // on array are used, but null semantics is impossible without preventing index use.
                case ColumnExpression _:
                    if (item is SqlConstantExpression constant && constant.Value is null)
                    {
                        // We special-case null constant item and use array_position instead, since it does
                        // nulls correctly (but doesn't use indexes)
                        // TODO: once lambda-based caching is implemented, move this to NpgsqlSqlNullabilityProcessor
                        // (https://github.com/dotnet/efcore/issues/17598) and do for parameters as well.
                        return(_sqlExpressionFactory.IsNotNull(
                                   _sqlExpressionFactory.Function(
                                       "array_position",
                                       new[] { array, item },
                                       nullable: true,
                                       argumentsPropagateNullability: FalseArrays[2],
                                       typeof(int))));
                    }

                    return(_sqlExpressionFactory.Contains(array,
                                                          _sqlExpressionFactory.NewArrayOrConstant(new[] { item }, array.Type)));

                // Don't do anything PG-specific for constant arrays since the general EF Core mechanism is fine
                // for that case: item IN (1, 2, 3).
                // After https://github.com/aspnet/EntityFrameworkCore/issues/16375 is done we may not need the
                // check any more.
                case SqlConstantExpression _:
                    return(null);

                // For ParameterExpression, and for all other cases - e.g. array returned from some function -
                // translate to e.SomeText = ANY (@p). This is superior to the general solution which will expand
                // parameters to constants, since non-PG SQL does not support arrays.
                // Note that this will allow indexes on the item to be used.
                default:
                    return(_sqlExpressionFactory.Any(item, array, PostgresAnyOperatorType.Equal));
                }
            }

            // Note: we also translate .Where(e => new[] { "a", "b", "c" }.Any(p => EF.Functions.Like(e.SomeText, p)))
            // to LIKE ANY (...). See NpgsqlSqlTranslatingExpressionVisitor.VisitArrayMethodCall.

            return(null);
        }
Beispiel #3
0
        public SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList <SqlExpression> arguments)
        {
            // TODO: Fully support List<>

            if (arguments.Count == 0)
            {
                return(null);
            }

            var operand = arguments[0];

            var operandElementType = operand.Type.IsArray
                ? operand.Type.GetElementType()
                : operand.Type.IsGenericType && operand.Type.GetGenericTypeDefinition() == typeof(List <>)
                    ? operand.Type.GetGenericArguments()[0]
                    : null;

            if (operandElementType == null) // Not an array/list
            {
                return(null);
            }

            // Even if the CLR type is an array/list, it may be mapped to a non-array database type (e.g. via value converters).
            if (operand.TypeMapping is RelationalTypeMapping typeMapping &&
                !(typeMapping is NpgsqlArrayTypeMapping) && !(typeMapping is NpgsqlJsonTypeMapping))
            {
                return(null);
            }

            if (method.IsClosedFormOf(SequenceEqual) && arguments[1].Type.IsArray)
            {
                return(_sqlExpressionFactory.Equal(operand, arguments[1]));
            }

            // Predicate-less Any - translate to a simple length check.
            if (method.IsClosedFormOf(EnumerableAnyWithoutPredicate))
            {
                return(_sqlExpressionFactory.GreaterThan(
                           _jsonPocoTranslator.TranslateArrayLength(operand) ??
                           _sqlExpressionFactory.Function("cardinality", arguments, typeof(int?)),
                           _sqlExpressionFactory.Constant(0)));
            }

            // Note that .Where(e => new[] { "a", "b", "c" }.Any(p => e.SomeText == p)))
            // is pattern-matched in AllAnyToContainsRewritingExpressionVisitor, which transforms it to
            // new[] { "a", "b", "c" }.Contains(e.SomeText).
            // Here we go further, and translate that to the PostgreSQL-specific construct e.SomeText = ANY (@p) -
            // this is superior to the general solution which will expand parameters to constants,
            // since non-PG SQL does not support arrays. If the list is a constant we leave it for regular IN
            // (functionality the same but more familiar).

            // Note: we exclude constant array expressions from this PG-specific optimization since the general
            // EF Core mechanism is fine for that case. After https://github.com/aspnet/EntityFrameworkCore/issues/16375
            // is done we may not need the check any more.
            // Note: we exclude arrays/lists over Nullable<T> since the ADO layer doesn't handle them (but will in 5.0)

            if (method.IsClosedFormOf(Contains) &&
                _sqlExpressionFactory.FindMapping(operand.Type) != null &&
                !(operand is SqlConstantExpression) &&
                Nullable.GetUnderlyingType(operandElementType) == null)
            {
                var item = arguments[1];
                // We require a null semantics check in case the item is null and the array contains a null.
                // Advanced parameter sniffing would help here: https://github.com/aspnet/EntityFrameworkCore/issues/17598
                return(_sqlExpressionFactory.OrElse(
                           // We need to coalesce to false since 'x' = ANY ({'y', NULL}) returns null, not false
                           // (and so will be null when negated too)
                           _sqlExpressionFactory.Coalesce(
                               _sqlExpressionFactory.ArrayAnyAll(item, operand, ArrayComparisonType.Any, "="),
                               _sqlExpressionFactory.Constant(false)),
                           _sqlExpressionFactory.AndAlso(
                               _sqlExpressionFactory.IsNull(item),
                               _sqlExpressionFactory.IsNotNull(
                                   _sqlExpressionFactory.Function(
                                       "array_position",
                                       new[] { operand, _sqlExpressionFactory.Fragment("NULL") },
                                       typeof(int))))));
            }

            // Note: we also translate .Where(e => new[] { "a", "b", "c" }.Any(p => EF.Functions.Like(e.SomeText, p)))
            // to LIKE ANY (...). See NpgsqlSqlTranslatingExpressionVisitor.VisitMethodCall.

            return(null);
        }
        public SqlExpression?Translate(SqlExpression instance, MemberInfo member, Type returnType, IDiagnosticsLogger <DbLoggerCategory.Query> logger)
        {
            if (instance is null)
            {
                return(null);
            }
            if (instance.TypeMapping is NpgsqlJsonTypeMapping mapping)
            {
                if (instance?.Type.IsGenericCollection() == true &&
                    member.Name == nameof(List <object> .Count) &&
                    instance.TypeMapping is null)
                {
                    return(_jsonPocoTranslator.TranslateArrayLength(instance));
                }

                if (member.DeclaringType !.IsGenericType &&
                    (typeof(IDictionary <,>).IsAssignableFrom(member.DeclaringType.GetGenericTypeDefinition()) ||
                     member.DeclaringType.GetInterfaces().Any(a => a.IsGenericType &&
                                                              a.GetGenericTypeDefinition() == typeof(IDictionary <,>)))
                    )
                {
                    if (member.Name == nameof(IDictionary.Keys))
                    {
                        var type           = (member as PropertyInfo) !.PropertyType.GetGenericArguments()[0];
                        var realReturnType = typeof(List <>).MakeGenericType(type);
                        var sub            = _sqlExpressionFactory.ApplyDefaultTypeMapping(_sqlExpressionFactory.Function(
                                                                                               mapping.IsJsonb ? "jsonb_each_text" : "json_each_text",
                                                                                               new[] { instance },
                                                                                               nullable: true,
                                                                                               argumentsPropagateNullability: TrueArrays[1],
                                                                                               typeof(string))
                                                                                           );

                        var binary = new PostgresUnknownBinaryExpression(_sqlExpressionFactory.Fragment("select key"), sub, "from", typeof(string), _stringTypeMapping);
                        var r      = _sqlExpressionFactory.ApplyDefaultTypeMapping(_sqlExpressionFactory.Function(
                                                                                       "ARRAY",
                                                                                       new SqlExpression[] { binary },
                                                                                       nullable: true,
                                                                                       argumentsPropagateNullability: TrueArrays[1],
                                                                                       typeof(string[])));
                        if (type != typeof(string))
                        {
                            r = _sqlExpressionFactory.ApplyDefaultTypeMapping(_sqlExpressionFactory.Convert(r, realReturnType));
                        }
                        return(r);
                    }
                    if (member.Name == nameof(IDictionary.Values))
                    {
                        var type           = (member as PropertyInfo) !.PropertyType.GetGenericArguments()[0];
                        var realReturnType = typeof(List <>).MakeGenericType(type);
                        var sub            = _sqlExpressionFactory.ApplyDefaultTypeMapping(_sqlExpressionFactory.Function(
                                                                                               mapping.IsJsonb ? "jsonb_each_text" : "json_each_text",
                                                                                               new[] { instance },
                                                                                               nullable: true,
                                                                                               argumentsPropagateNullability: TrueArrays[1],
                                                                                               typeof(string))
                                                                                           );

                        var binary = new PostgresUnknownBinaryExpression(_sqlExpressionFactory.Fragment("select value"), sub, "from", typeof(string), _stringTypeMapping);
                        var r      = _sqlExpressionFactory.ApplyDefaultTypeMapping(_sqlExpressionFactory.Function(
                                                                                       "ARRAY",
                                                                                       new SqlExpression[] { binary },
                                                                                       nullable: true,
                                                                                       argumentsPropagateNullability: TrueArrays[1],
                                                                                       realReturnType));
                        if (type != typeof(string))
                        {
                            r = _sqlExpressionFactory.ApplyDefaultTypeMapping(_sqlExpressionFactory.Convert(r, realReturnType));
                        }
                        return(r);
                    }
                }
            }
            else if (instance.TypeMapping is NpgsqlArrayTypeMapping arrayMapping &&
                     member.DeclaringType !.IsGenericCollection() &&
                     member.Name == nameof(ICollection.Count))
            {
                return(_sqlExpressionFactory.Function(
                           "cardinality",
                           new[] { instance },
                           nullable: true,
                           argumentsPropagateNullability: TrueArrays[1],
                           typeof(int?)));
            }

            if (!typeof(JToken).IsAssignableFrom(member.DeclaringType))
            {
                return(null);
            }

            if (member.Name == nameof(JToken.Root) &&
                instance is ColumnExpression column &&
                column.TypeMapping is NpgsqlJsonTypeMapping)
            {
                // Simply get rid of the RootElement member access
                return(column);
            }
            return(null);
        }
Beispiel #5
0
            SqlExpression?TranslateCommon(SqlExpression arrayOrList, IReadOnlyList <SqlExpression> arguments)
            {
                // Predicate-less Any - translate to a simple length check.
                if (method.IsClosedFormOf(EnumerableAnyWithoutPredicate))
                {
                    return(_sqlExpressionFactory.GreaterThan(
                               _jsonPocoTranslator.TranslateArrayLength(arrayOrList) ??
                               _sqlExpressionFactory.Function(
                                   "cardinality",
                                   new[] { arrayOrList },
                                   nullable: true,
                                   argumentsPropagateNullability: TrueArrays[1],
                                   typeof(int)),
                               _sqlExpressionFactory.Constant(0)));
                }

                // Note that .Where(e => new[] { "a", "b", "c" }.Any(p => e.SomeText == p)))
                // is pattern-matched in AllAnyToContainsRewritingExpressionVisitor, which transforms it to
                // new[] { "a", "b", "c" }.Contains(e.Some Text).

                if ((method.IsClosedFormOf(EnumerableContains) || // Enumerable.Contains extension method
                     method.Name == nameof(List <int> .Contains) && method.DeclaringType.IsGenericList() &&
                     method.GetParameters().Length == 1)
                    &&
                    (
                        // Handle either array columns (with an array mapping) or parameters/constants (no mapping). We specifically
                        // don't want to translate if the type mapping is bytea (CLR type is array, but not an array in
                        // the database).
                        // arrayOrList.TypeMapping == null && _typeMappingSource.FindMapping(arrayOrList.Type) != null ||
                        arrayOrList.TypeMapping is NpgsqlArrayTypeMapping or null
                    ))
                {
                    var item = arguments[0];

                    switch (arrayOrList)
                    {
                    // When the array is a column, we translate to array @> ARRAY[item]. GIN indexes
                    // on array are used, but null semantics is impossible without preventing index use.
                    case ColumnExpression:
                        if (item is SqlConstantExpression constant && constant.Value is null)
                        {
                            // We special-case null constant item and use array_position instead, since it does
                            // nulls correctly (but doesn't use indexes)
                            // TODO: once lambda-based caching is implemented, move this to NpgsqlSqlNullabilityProcessor
                            // (https://github.com/dotnet/efcore/issues/17598) and do for parameters as well.
                            return(_sqlExpressionFactory.IsNotNull(
                                       _sqlExpressionFactory.Function(
                                           "array_position",
                                           new[] { arrayOrList, item },
                                           nullable: true,
                                           argumentsPropagateNullability: FalseArrays[2],
                                           typeof(int))));
                        }

                        return(_sqlExpressionFactory.Contains(arrayOrList,
                                                              _sqlExpressionFactory.NewArrayOrConstant(new[] { item }, arrayOrList.Type)));

                    // Don't do anything PG-specific for constant arrays since the general EF Core mechanism is fine
                    // for that case: item IN (1, 2, 3).
                    // After https://github.com/aspnet/EntityFrameworkCore/issues/16375 is done we may not need the
                    // check any more.
                    case SqlConstantExpression:
                        return(null);

                    // For ParameterExpression, and for all other cases - e.g. array returned from some function -
                    // translate to e.SomeText = ANY (@p). This is superior to the general solution which will expand
                    // parameters to constants, since non-PG SQL does not support arrays.
                    // Note that this will allow indexes on the item to be used.
                    default:
                        return(_sqlExpressionFactory.Any(item, arrayOrList, PostgresAnyOperatorType.Equal));
                    }
                }

                // Note: we also translate .Where(e => new[] { "a", "b", "c" }.Any(p => EF.Functions.Like(e.SomeText, p)))
                // to LIKE ANY (...). See NpgsqlSqlTranslatingExpressionVisitor.VisitArrayMethodCall.

                return(null);
            }
        public SqlExpression?Translate(SqlExpression instance, MethodInfo method, IReadOnlyList <SqlExpression> arguments, IDiagnosticsLogger <DbLoggerCategory.Query> logger)
        {
            if (typeof(JToken).IsAssignableFrom(method.DeclaringType) &&
                method.Name == "get_Item" &&
                arguments.Count == 1)
            {
                return((instance is ColumnExpression columnExpression
                        ? _sqlExpressionFactory.JsonTraversal(
                            columnExpression, returnsText: false, typeof(string), instance.TypeMapping)
                        : instance) is PostgresJsonTraversalExpression prevPathTraversal
                        ? prevPathTraversal.Append(_sqlExpressionFactory.ApplyDefaultTypeMapping(arguments[0]))
                        : null);
            }
            if (arguments.FirstOrDefault() is PostgresJsonTraversalExpression traversal)
            {
                // Support for .Value<T>() and .Value<U, T>():
                if (instance == null &&
                    method.Name == nameof(Extensions.Value) &&
                    method.DeclaringType == typeof(Extensions) &&
                    method.IsGenericMethod &&
                    method.GetParameters().Length == 1 &&
                    arguments.Count == 1)
                {
                    var traversalToText = new PostgresJsonTraversalExpression(
                        traversal.Expression,
                        traversal.Path,
                        returnsText: true,
                        typeof(string),
                        _stringTypeMapping);

                    if (method.ReturnType == typeof(string))
                    {
                        return(traversalToText);
                    }
                    else
                    {
                        return(_sqlExpressionFactory.Convert(traversalToText, method.ReturnType, _typeMappingSource.FindMapping(method.ReturnType)));
                    }
                }

                // Support for Count()
                if (instance == null &&
                    method.Name == nameof(Enumerable.Count) &&
                    method.DeclaringType == typeof(Enumerable) &&
                    method.IsGenericMethod &&
                    method.GetParameters().Length == 1 &&
                    arguments.Count == 1)
                {
                    return(_jsonPocoTranslator.TranslateArrayLength(traversal));
                }

                // Predicate-less Any - translate to a simple length check.
                if (method.IsClosedFormOf(_enumerableAnyWithoutPredicate) &&
                    arguments.Count == 1 &&
                    arguments[0].Type.TryGetElementType(out _) &&
                    arguments[0].TypeMapping is NpgsqlJsonTypeMapping)
                {
                    return(_sqlExpressionFactory.GreaterThan(
                               _jsonPocoTranslator.TranslateArrayLength(arguments[0]),
                               _sqlExpressionFactory.Constant(0)));
                }
            }
            return(null);
        }