private SqlExpression TranslateIndexOf(
        SqlExpression instance,
        MethodInfo method,
        SqlExpression searchExpression,
        SqlExpression?startIndex)
    {
        var stringTypeMapping = ExpressionExtensions.InferTypeMapping(instance, searchExpression) !;

        searchExpression = _sqlExpressionFactory.ApplyTypeMapping(searchExpression, stringTypeMapping);
        instance         = _sqlExpressionFactory.ApplyTypeMapping(instance, stringTypeMapping);

        var charIndexArguments = new List <SqlExpression> {
            searchExpression, instance
        };

        if (startIndex is not null)
        {
            charIndexArguments.Add(_sqlExpressionFactory.Add(startIndex, _sqlExpressionFactory.Constant(1)));
        }

        var argumentsPropagateNullability = Enumerable.Repeat(true, charIndexArguments.Count);

        SqlExpression charIndexExpression;
        var           storeType = stringTypeMapping.StoreType;

        if (string.Equals(storeType, "nvarchar(max)", StringComparison.OrdinalIgnoreCase) ||
            string.Equals(storeType, "varchar(max)", StringComparison.OrdinalIgnoreCase))
        {
            charIndexExpression = _sqlExpressionFactory.Function(
                "CHARINDEX",
                charIndexArguments,
                nullable: true,
                argumentsPropagateNullability,
                typeof(long));

            charIndexExpression = _sqlExpressionFactory.Convert(charIndexExpression, typeof(int));
        }
        else
        {
            charIndexExpression = _sqlExpressionFactory.Function(
                "CHARINDEX",
                charIndexArguments,
                nullable: true,
                argumentsPropagateNullability,
                method.ReturnType);
        }

        charIndexExpression = _sqlExpressionFactory.Subtract(charIndexExpression, _sqlExpressionFactory.Constant(1));

        return(_sqlExpressionFactory.Case(
                   new[]
        {
            new CaseWhenClause(
                _sqlExpressionFactory.Equal(
                    searchExpression,
                    _sqlExpressionFactory.Constant(string.Empty, stringTypeMapping)),
                _sqlExpressionFactory.Constant(0))
        },
                   charIndexExpression));
    }
    /// <summary>
    ///     This is an internal API that supports the Entity Framework Core infrastructure and not subject to
    ///     the same compatibility standards as public APIs. It may be changed or removed without notice in
    ///     any release. You should only use it directly in your code with extreme caution and knowing that
    ///     doing so can result in application failures when updating to a new Entity Framework Core release.
    /// </summary>
    public virtual SqlExpression?Translate(
        SqlExpression?instance,
        MethodInfo method,
        IReadOnlyList <SqlExpression> arguments,
        IDiagnosticsLogger <DbLoggerCategory.Query> logger)
    {
        if (method.Equals(RegexIsMatchMethodInfo))
        {
            var input             = arguments[0];
            var pattern           = arguments[1];
            var stringTypeMapping = ExpressionExtensions.InferTypeMapping(input, pattern);

            return(_sqlExpressionFactory.Function(
                       "regexp",
                       new[]
            {
                _sqlExpressionFactory.ApplyTypeMapping(pattern, stringTypeMapping),
                _sqlExpressionFactory.ApplyTypeMapping(input, stringTypeMapping)
            },
                       nullable: true,
                       argumentsPropagateNullability: new[] { true, true },
                       typeof(bool)));
        }

        return(null);
    }
        public virtual SqlFunctionExpression Coalesce(SqlExpression left, SqlExpression right, RelationalTypeMapping typeMapping = null)
        {
            Check.NotNull(left, nameof(left));
            Check.NotNull(right, nameof(right));

            var resultType = right.Type;
            var inferredTypeMapping = typeMapping
                ?? ExpressionExtensions.InferTypeMapping(left, right)
                ?? _typeMappingSource.FindMapping(resultType);

            var typeMappedArguments = new List<SqlExpression>()
            {
                ApplyTypeMapping(left, inferredTypeMapping),
                ApplyTypeMapping(right, inferredTypeMapping)
            };

            return SqlFunctionExpression.Create(
                "COALESCE",
                typeMappedArguments,
                nullResultAllowed: true,
                // COALESCE is handled separately since it's only nullable if *both* arguments are null
                argumentsPropagateNullability: new[] { false, false },
                resultType,
                inferredTypeMapping);
        }
        private SqlExpression ApplyTypeMappingOnSqlBinary(
            SqlBinaryExpression sqlBinaryExpression, RelationalTypeMapping typeMapping)
        {
            var left = sqlBinaryExpression.Left;
            var right = sqlBinaryExpression.Right;

            Type resultType;
            RelationalTypeMapping resultTypeMapping;
            RelationalTypeMapping inferredTypeMapping;
            switch (sqlBinaryExpression.OperatorType)
            {
                case ExpressionType.Equal:
                case ExpressionType.GreaterThan:
                case ExpressionType.GreaterThanOrEqual:
                case ExpressionType.LessThan:
                case ExpressionType.LessThanOrEqual:
                case ExpressionType.NotEqual:
                {
                    inferredTypeMapping = ExpressionExtensions.InferTypeMapping(left, right)
                        ?? _typeMappingSource.FindMapping(left.Type);
                    resultType = typeof(bool);
                    resultTypeMapping = _boolTypeMapping;
                    break;
                }

                case ExpressionType.AndAlso:
                case ExpressionType.OrElse:
                {
                    inferredTypeMapping = _boolTypeMapping;
                    resultType = typeof(bool);
                    resultTypeMapping = _boolTypeMapping;
                    break;
                }

                case ExpressionType.Add:
                case ExpressionType.Subtract:
                case ExpressionType.Multiply:
                case ExpressionType.Divide:
                case ExpressionType.Modulo:
                case ExpressionType.And:
                case ExpressionType.Or:
                {
                    inferredTypeMapping = typeMapping ?? ExpressionExtensions.InferTypeMapping(left, right);
                    resultType = left.Type;
                    resultTypeMapping = inferredTypeMapping;
                    break;
                }

                default:
                    throw new InvalidOperationException("Incorrect operatorType for SqlBinaryExpression");
            }

            return new SqlBinaryExpression(
                sqlBinaryExpression.OperatorType,
                ApplyTypeMapping(left, inferredTypeMapping),
                ApplyTypeMapping(right, inferredTypeMapping),
                resultType,
                resultTypeMapping);
        }
Пример #5
0
        /// <inheritdoc />
        protected override Expression VisitBinary(BinaryExpression binaryExpression)
        {
            // Support DateTime subtraction, which returns a TimeSpan.
            if (binaryExpression.NodeType == ExpressionType.Subtract &&
                binaryExpression.Left.Type.UnwrapNullableType() == typeof(DateTime) &&
                binaryExpression.Left.Type.UnwrapNullableType() == typeof(DateTime))
            {
                if (TranslationFailed(binaryExpression.Left, Visit(TryRemoveImplicitConvert(binaryExpression.Left)), out var sqlLeft) ||
                    TranslationFailed(binaryExpression.Right, Visit(TryRemoveImplicitConvert(binaryExpression.Right)), out var sqlRight))
                {
                    return(null);
                }

                var inferredDateTimeTypeMapping = ExpressionExtensions.InferTypeMapping(sqlLeft, sqlRight);
                return(new SqlBinaryExpression(
                           ExpressionType.Subtract,
                           _sqlExpressionFactory.ApplyTypeMapping(sqlLeft, inferredDateTimeTypeMapping),
                           _sqlExpressionFactory.ApplyTypeMapping(sqlRight, inferredDateTimeTypeMapping),
                           typeof(TimeSpan),
                           null));
            }

            if (binaryExpression.NodeType == ExpressionType.ArrayIndex)
            {
                if (TranslationFailed(binaryExpression.Left, Visit(TryRemoveImplicitConvert(binaryExpression.Left)), out var sqlLeft) ||
                    TranslationFailed(binaryExpression.Right, Visit(TryRemoveImplicitConvert(binaryExpression.Right)), out var sqlRight))
                {
                    return(null);
                }

                // ArrayIndex over bytea is special, we have to use function rather than subscript
                if (binaryExpression.Left.Type == typeof(byte[]))
                {
                    return(_sqlExpressionFactory.Function(
                               "get_byte",
                               new[]
                    {
                        _sqlExpressionFactory.ApplyDefaultTypeMapping(sqlLeft),
                        _sqlExpressionFactory.ApplyDefaultTypeMapping(sqlRight)
                    },
                               nullable: true,
                               argumentsPropagateNullability: TrueArrays[2],
                               typeof(byte),
                               _typeMappingSource.FindMapping(typeof(byte))
                               ));
                }

                return
                    // Try translating ArrayIndex inside json column
                    (_jsonPocoTranslator.TranslateMemberAccess(sqlLeft, sqlRight, binaryExpression.Type) ??
                     // Other types should be subscriptable - but PostgreSQL arrays are 1-based, so adjust the index.
                     _sqlExpressionFactory.ArrayIndex(sqlLeft, GenerateOneBasedIndexExpression(sqlRight)));
            }

            return(base.VisitBinary(binaryExpression));
        }
        private SqlExpression ApplyTypeMappingOnLike(LikeExpression likeExpression)
        {
            var inferredTypeMapping = (likeExpression.EscapeChar == null
                    ? ExpressionExtensions.InferTypeMapping(
                        likeExpression.Match, likeExpression.Pattern)
                    : ExpressionExtensions.InferTypeMapping(
                        likeExpression.Match, likeExpression.Pattern, likeExpression.EscapeChar))
                ?? _typeMappingSource.FindMapping(likeExpression.Match.Type);

            return new LikeExpression(
                ApplyTypeMapping(likeExpression.Match, inferredTypeMapping),
                ApplyTypeMapping(likeExpression.Pattern, inferredTypeMapping),
                ApplyTypeMapping(likeExpression.EscapeChar, inferredTypeMapping),
                _boolTypeMapping);
        }
    private SqlExpression TranslateStartsEndsWith(SqlExpression instance, SqlExpression pattern, bool startsWith)
    {
        var stringTypeMapping = ExpressionExtensions.InferTypeMapping(instance, pattern);

        instance = _sqlExpressionFactory.ApplyTypeMapping(instance, stringTypeMapping);
        pattern  = _sqlExpressionFactory.ApplyTypeMapping(pattern, stringTypeMapping);

        if (pattern is SqlConstantExpression constantExpression)
        {
            // The pattern is constant. Aside from null or empty, we escape all special characters (%, _, \)
            // in C# and send a simple LIKE
            if (!(constantExpression.Value is string patternValue))
            {
                return(_sqlExpressionFactory.Like(
                           instance,
                           _sqlExpressionFactory.Constant(null, stringTypeMapping)));
            }

            return(patternValue.Any(IsLikeWildChar)
                ? _sqlExpressionFactory.Like(
                       instance,
                       _sqlExpressionFactory.Constant(
                           startsWith
                            ? EscapeLikePattern(patternValue) + '%'
                            : '%' + EscapeLikePattern(patternValue)),
                       _sqlExpressionFactory.Constant(LikeEscapeString))
                : _sqlExpressionFactory.Like(
                       instance,
                       _sqlExpressionFactory.Constant(startsWith ? patternValue + '%' : '%' + patternValue)));
        }

        // The pattern is non-constant, we use LEFT or RIGHT to extract substring and compare.
        if (startsWith)
        {
            return(_sqlExpressionFactory.Equal(
                       _sqlExpressionFactory.Function(
                           "LEFT",
                           new[]
            {
                instance,
                _sqlExpressionFactory.Function(
                    "LEN",
                    new[] { pattern },
                    nullable: true,
                    argumentsPropagateNullability: new[] { true },
                    typeof(int))
            },
                           nullable: true,
                           argumentsPropagateNullability: new[] { true, true },
                           typeof(string),
                           stringTypeMapping),
                       pattern));
        }

        return(_sqlExpressionFactory.Equal(
                   _sqlExpressionFactory.Function(
                       "RIGHT",
                       new[]
        {
            instance,
            _sqlExpressionFactory.Function(
                "LEN",
                new[] { pattern },
                nullable: true,
                argumentsPropagateNullability: new[] { true },
                typeof(int))
        },
                       nullable: true,
                       argumentsPropagateNullability: new[] { true, true },
                       typeof(string),
                       stringTypeMapping),
                   pattern));
    }
    /// <summary>
    ///     This is an internal API that supports the Entity Framework Core infrastructure and not subject to
    ///     the same compatibility standards as public APIs. It may be changed or removed without notice in
    ///     any release. You should only use it directly in your code with extreme caution and knowing that
    ///     doing so can result in application failures when updating to a new Entity Framework Core release.
    /// </summary>
    public virtual SqlExpression?Translate(
        SqlExpression?instance,
        MethodInfo method,
        IReadOnlyList <SqlExpression> arguments,
        IDiagnosticsLogger <DbLoggerCategory.Query> logger)
    {
        if (instance != null)
        {
            if (_indexOfMethodInfo.Equals(method))
            {
                return(TranslateIndexOf(instance, method, arguments[0], null));
            }

            if (_indexOfMethodInfoWithStartingPosition.Equals(method))
            {
                return(TranslateIndexOf(instance, method, arguments[0], arguments[1]));
            }

            if (_replaceMethodInfo.Equals(method))
            {
                var firstArgument     = arguments[0];
                var secondArgument    = arguments[1];
                var stringTypeMapping = ExpressionExtensions.InferTypeMapping(instance, firstArgument, secondArgument);

                instance       = _sqlExpressionFactory.ApplyTypeMapping(instance, stringTypeMapping);
                firstArgument  = _sqlExpressionFactory.ApplyTypeMapping(firstArgument, stringTypeMapping);
                secondArgument = _sqlExpressionFactory.ApplyTypeMapping(secondArgument, stringTypeMapping);

                return(_sqlExpressionFactory.Function(
                           "REPLACE",
                           new[] { instance, firstArgument, secondArgument },
                           nullable: true,
                           argumentsPropagateNullability: new[] { true, true, true },
                           method.ReturnType,
                           stringTypeMapping));
            }

            if (_toLowerMethodInfo.Equals(method) ||
                _toUpperMethodInfo.Equals(method))
            {
                return(_sqlExpressionFactory.Function(
                           _toLowerMethodInfo.Equals(method) ? "LOWER" : "UPPER",
                           new[] { instance },
                           nullable: true,
                           argumentsPropagateNullability: new[] { true },
                           method.ReturnType,
                           instance.TypeMapping));
            }

            if (_substringMethodInfoWithOneArg.Equals(method))
            {
                return(_sqlExpressionFactory.Function(
                           "SUBSTRING",
                           new[]
                {
                    instance,
                    _sqlExpressionFactory.Add(
                        arguments[0],
                        _sqlExpressionFactory.Constant(1)),
                    _sqlExpressionFactory.Function(
                        "LEN",
                        new[] { instance },
                        nullable: true,
                        argumentsPropagateNullability: new[] { true },
                        typeof(int))
                },
                           nullable: true,
                           argumentsPropagateNullability: new[] { true, true, true },
                           method.ReturnType,
                           instance.TypeMapping));
            }

            if (_substringMethodInfoWithTwoArgs.Equals(method))
            {
                return(_sqlExpressionFactory.Function(
                           "SUBSTRING",
                           new[]
                {
                    instance,
                    _sqlExpressionFactory.Add(
                        arguments[0],
                        _sqlExpressionFactory.Constant(1)),
                    arguments[1]
                },
                           nullable: true,
                           argumentsPropagateNullability: new[] { true, true, true },
                           method.ReturnType,
                           instance.TypeMapping));
            }

            if (_trimStartMethodInfoWithoutArgs?.Equals(method) == true ||
                (_trimStartMethodInfoWithCharArrayArg.Equals(method)
                 // SqlServer LTRIM does not take arguments
                 && ((arguments[0] as SqlConstantExpression)?.Value as Array)?.Length == 0))
            {
                return(_sqlExpressionFactory.Function(
                           "LTRIM",
                           new[] { instance },
                           nullable: true,
                           argumentsPropagateNullability: new[] { true },
                           instance.Type,
                           instance.TypeMapping));
            }

            if (_trimEndMethodInfoWithoutArgs?.Equals(method) == true ||
                (_trimEndMethodInfoWithCharArrayArg.Equals(method)
                 // SqlServer RTRIM does not take arguments
                 && ((arguments[0] as SqlConstantExpression)?.Value as Array)?.Length == 0))
            {
                return(_sqlExpressionFactory.Function(
                           "RTRIM",
                           new[] { instance },
                           nullable: true,
                           argumentsPropagateNullability: new[] { true },
                           instance.Type,
                           instance.TypeMapping));
            }

            if (_trimMethodInfoWithoutArgs?.Equals(method) == true ||
                (_trimMethodInfoWithCharArrayArg.Equals(method)
                 // SqlServer LTRIM/RTRIM does not take arguments
                 && ((arguments[0] as SqlConstantExpression)?.Value as Array)?.Length == 0))
            {
                return(_sqlExpressionFactory.Function(
                           "LTRIM",
                           new[]
                {
                    _sqlExpressionFactory.Function(
                        "RTRIM",
                        new[] { instance },
                        nullable: true,
                        argumentsPropagateNullability: new[] { true },
                        instance.Type,
                        instance.TypeMapping)
                },
                           nullable: true,
                           argumentsPropagateNullability: new[] { true },
                           instance.Type,
                           instance.TypeMapping));
            }

            if (_containsMethodInfo.Equals(method))
            {
                var pattern           = arguments[0];
                var stringTypeMapping = ExpressionExtensions.InferTypeMapping(instance, pattern);
                instance = _sqlExpressionFactory.ApplyTypeMapping(instance, stringTypeMapping);
                pattern  = _sqlExpressionFactory.ApplyTypeMapping(pattern, stringTypeMapping);

                if (pattern is SqlConstantExpression constantPattern)
                {
                    if (!(constantPattern.Value is string patternValue))
                    {
                        return(_sqlExpressionFactory.Like(
                                   instance,
                                   _sqlExpressionFactory.Constant(null, stringTypeMapping)));
                    }

                    if (patternValue.Length == 0)
                    {
                        return(_sqlExpressionFactory.Constant(true));
                    }

                    return(patternValue.Any(IsLikeWildChar)
                        ? _sqlExpressionFactory.Like(
                               instance,
                               _sqlExpressionFactory.Constant($"%{EscapeLikePattern(patternValue)}%"),
                               _sqlExpressionFactory.Constant(LikeEscapeString))
                        : _sqlExpressionFactory.Like(instance, _sqlExpressionFactory.Constant($"%{patternValue}%")));
                }

                return(_sqlExpressionFactory.OrElse(
                           _sqlExpressionFactory.Like(
                               pattern,
                               _sqlExpressionFactory.Constant(string.Empty, stringTypeMapping)),
                           _sqlExpressionFactory.GreaterThan(
                               _sqlExpressionFactory.Function(
                                   "CHARINDEX",
                                   new[] { pattern, instance },
                                   nullable: true,
                                   argumentsPropagateNullability: new[] { true, true },
                                   typeof(int)),
                               _sqlExpressionFactory.Constant(0))));
            }

            if (_startsWithMethodInfo.Equals(method))
            {
                return(TranslateStartsEndsWith(instance, arguments[0], true));
            }

            if (_endsWithMethodInfo.Equals(method))
            {
                return(TranslateStartsEndsWith(instance, arguments[0], false));
            }
        }

        if (_isNullOrEmptyMethodInfo.Equals(method))
        {
            var argument = arguments[0];

            return(_sqlExpressionFactory.OrElse(
                       _sqlExpressionFactory.IsNull(argument),
                       _sqlExpressionFactory.Like(
                           argument,
                           _sqlExpressionFactory.Constant(string.Empty))));
        }

        if (_isNullOrWhiteSpaceMethodInfo.Equals(method))
        {
            var argument = arguments[0];

            return(_sqlExpressionFactory.OrElse(
                       _sqlExpressionFactory.IsNull(argument),
                       _sqlExpressionFactory.Equal(
                           argument,
                           _sqlExpressionFactory.Constant(string.Empty, argument.TypeMapping))));
        }

        if (_firstOrDefaultMethodInfoWithoutArgs.Equals(method))
        {
            var argument = arguments[0];
            return(_sqlExpressionFactory.Function(
                       "SUBSTRING",
                       new[] { argument, _sqlExpressionFactory.Constant(1), _sqlExpressionFactory.Constant(1) },
                       nullable: true,
                       argumentsPropagateNullability: new[] { true, true, true },
                       method.ReturnType));
        }

        if (_lastOrDefaultMethodInfoWithoutArgs.Equals(method))
        {
            var argument = arguments[0];
            return(_sqlExpressionFactory.Function(
                       "SUBSTRING",
                       new[]
            {
                argument,
                _sqlExpressionFactory.Function(
                    "LEN",
                    new[] { argument },
                    nullable: true,
                    argumentsPropagateNullability: new[] { true },
                    typeof(int)),
                _sqlExpressionFactory.Constant(1)
            },
                       nullable: true,
                       argumentsPropagateNullability: new[] { true, true, true },
                       method.ReturnType));
        }

        return(null);
    }
Пример #9
0
    /// <inheritdoc />
    public virtual SqlExpression?Translate(
        SqlExpression?instance,
        MethodInfo method,
        IReadOnlyList <SqlExpression> arguments,
        IDiagnosticsLogger <DbLoggerCategory.Query> logger)
    {
        // Any() over multirange -> NOT isempty(). NpgsqlRange<T> has IsEmpty which is translated below.
        if (method.IsGenericMethod &&
            method.GetGenericMethodDefinition() == EnumerableAnyWithoutPredicate &&
            arguments[0].Type.TryGetMultirangeSubtype(out _))
        {
            return(_sqlExpressionFactory.Not(
                       _sqlExpressionFactory.Function(
                           "isempty",
                           new[] { arguments[0] },
                           nullable: true,
                           argumentsPropagateNullability: TrueArrays[1],
                           typeof(bool))));
        }

        if (method.DeclaringType != typeof(NpgsqlRangeDbFunctionsExtensions) &&
            method.DeclaringType != typeof(NpgsqlMultirangeDbFunctionsExtensions))
        {
            return(null);
        }

        if (method.Name == nameof(NpgsqlRangeDbFunctionsExtensions.Merge))
        {
            if (method.DeclaringType == typeof(NpgsqlRangeDbFunctionsExtensions))
            {
                var inferredMapping = ExpressionExtensions.InferTypeMapping(arguments[0], arguments[1]);

                return(_sqlExpressionFactory.Function(
                           "range_merge",
                           new[] {
                    _sqlExpressionFactory.ApplyTypeMapping(arguments[0], inferredMapping),
                    _sqlExpressionFactory.ApplyTypeMapping(arguments[1], inferredMapping)
                },
                           nullable: true,
                           argumentsPropagateNullability: TrueArrays[2],
                           method.ReturnType,
                           inferredMapping));
            }

            if (method.DeclaringType == typeof(NpgsqlMultirangeDbFunctionsExtensions))
            {
                var returnTypeMapping = arguments[0].TypeMapping is NpgsqlMultirangeTypeMapping multirangeTypeMapping
                    ? multirangeTypeMapping.RangeMapping
                    : null;

                return(_sqlExpressionFactory.Function(
                           "range_merge",
                           new[] { arguments[0] },
                           nullable: true,
                           argumentsPropagateNullability: TrueArrays[1],
                           method.ReturnType,
                           returnTypeMapping));
            }
        }

        return(method.Name switch
        {
            nameof(NpgsqlRangeDbFunctionsExtensions.Contains)
            => _sqlExpressionFactory.Contains(arguments[0], arguments[1]),
            nameof(NpgsqlRangeDbFunctionsExtensions.ContainedBy)
            => _sqlExpressionFactory.ContainedBy(arguments[0], arguments[1]),
            nameof(NpgsqlRangeDbFunctionsExtensions.Overlaps)
            => _sqlExpressionFactory.Overlaps(arguments[0], arguments[1]),
            nameof(NpgsqlRangeDbFunctionsExtensions.IsStrictlyLeftOf)
            => _sqlExpressionFactory.MakePostgresBinary(PostgresExpressionType.RangeIsStrictlyLeftOf, arguments[0], arguments[1]),
            nameof(NpgsqlRangeDbFunctionsExtensions.IsStrictlyRightOf)
            => _sqlExpressionFactory.MakePostgresBinary(PostgresExpressionType.RangeIsStrictlyRightOf, arguments[0], arguments[1]),
            nameof(NpgsqlRangeDbFunctionsExtensions.DoesNotExtendRightOf)
            => _sqlExpressionFactory.MakePostgresBinary(PostgresExpressionType.RangeDoesNotExtendRightOf, arguments[0], arguments[1]),
            nameof(NpgsqlRangeDbFunctionsExtensions.DoesNotExtendLeftOf)
            => _sqlExpressionFactory.MakePostgresBinary(PostgresExpressionType.RangeDoesNotExtendLeftOf, arguments[0], arguments[1]),
            nameof(NpgsqlRangeDbFunctionsExtensions.IsAdjacentTo)
            => _sqlExpressionFactory.MakePostgresBinary(PostgresExpressionType.RangeIsAdjacentTo, arguments[0], arguments[1]),
            nameof(NpgsqlRangeDbFunctionsExtensions.Union)
            => _sqlExpressionFactory.MakePostgresBinary(PostgresExpressionType.RangeUnion, arguments[0], arguments[1]),
            nameof(NpgsqlRangeDbFunctionsExtensions.Intersect)
            => _sqlExpressionFactory.MakePostgresBinary(PostgresExpressionType.RangeIntersect, arguments[0], arguments[1]),
            nameof(NpgsqlRangeDbFunctionsExtensions.Except)
            => _sqlExpressionFactory.MakePostgresBinary(PostgresExpressionType.RangeExcept, arguments[0], arguments[1]),

            _ => null
        });
Пример #10
0
    private SqlExpression TranslateStartsEndsWith(SqlExpression instance, SqlExpression pattern, bool startsWith)
    {
        var stringTypeMapping = ExpressionExtensions.InferTypeMapping(instance, pattern);

        instance = _sqlExpressionFactory.ApplyTypeMapping(instance, stringTypeMapping);
        pattern  = _sqlExpressionFactory.ApplyTypeMapping(pattern, stringTypeMapping);

        if (pattern is SqlConstantExpression constantExpression)
        {
            // The pattern is constant. Aside from null or empty, we escape all special characters (%, _, \)
            // in C# and send a simple LIKE
            if (!(constantExpression.Value is string constantString))
            {
                return(_sqlExpressionFactory.Like(instance, _sqlExpressionFactory.Constant(null, stringTypeMapping)));
            }

            if (constantString.Length == 0)
            {
                return(_sqlExpressionFactory.Constant(true));
            }

            return(constantString.Any(c => IsLikeWildChar(c))
                ? _sqlExpressionFactory.Like(
                       instance,
                       _sqlExpressionFactory.Constant(
                           startsWith
                            ? EscapeLikePattern(constantString) + '%'
                            : '%' + EscapeLikePattern(constantString)),
                       _sqlExpressionFactory.Constant(
                           LikeEscapeChar.ToString())) // SQL Server has no char mapping, avoid value conversion warning)
                : _sqlExpressionFactory.Like(
                       instance,
                       _sqlExpressionFactory.Constant(startsWith ? constantString + '%' : '%' + constantString)));
        }

        // The pattern is non-constant, we use LEFT or RIGHT to extract substring and compare.
        // For StartsWith we also first run a LIKE to quickly filter out most non-matching results (sargable, but imprecise
        // because of wildcards).
        if (startsWith)
        {
            return(_sqlExpressionFactory.OrElse(
                       _sqlExpressionFactory.AndAlso(
                           _sqlExpressionFactory.Like(
                               instance,
                               _sqlExpressionFactory.Add(
                                   pattern,
                                   _sqlExpressionFactory.Constant("%"))),
                           _sqlExpressionFactory.Equal(
                               _sqlExpressionFactory.Function(
                                   "substr",
                                   new[]
            {
                instance,
                _sqlExpressionFactory.Constant(1),
                _sqlExpressionFactory.Function(
                    "length",
                    new[] { pattern },
                    nullable: true,
                    argumentsPropagateNullability: new[] { true },
                    typeof(int))
            },
                                   nullable: true,
                                   argumentsPropagateNullability: new[] { true, false, true },
                                   typeof(string),
                                   stringTypeMapping),
                               pattern)),
                       _sqlExpressionFactory.Equal(
                           pattern,
                           _sqlExpressionFactory.Constant(string.Empty))));
        }

        return(_sqlExpressionFactory.OrElse(
                   _sqlExpressionFactory.Equal(
                       _sqlExpressionFactory.Function(
                           "substr",
                           new[]
        {
            instance,
            _sqlExpressionFactory.Negate(
                _sqlExpressionFactory.Function(
                    "length",
                    new[] { pattern },
                    nullable: true,
                    argumentsPropagateNullability: new[] { true },
                    typeof(int)))
        },
                           nullable: true,
                           argumentsPropagateNullability: new[] { true, true },
                           typeof(string),
                           stringTypeMapping),
                       pattern),
                   _sqlExpressionFactory.Equal(
                       pattern,
                       _sqlExpressionFactory.Constant(string.Empty))));
    }
Пример #11
0
    /// <summary>
    ///     This is an internal API that supports the Entity Framework Core infrastructure and not subject to
    ///     the same compatibility standards as public APIs. It may be changed or removed without notice in
    ///     any release. You should only use it directly in your code with extreme caution and knowing that
    ///     doing so can result in application failures when updating to a new Entity Framework Core release.
    /// </summary>
    public virtual SqlExpression?Translate(
        SqlExpression?instance,
        MethodInfo method,
        IReadOnlyList <SqlExpression> arguments,
        IDiagnosticsLogger <DbLoggerCategory.Query> logger)
    {
        if (instance != null)
        {
            if (IndexOfMethodInfo.Equals(method))
            {
                var argument          = arguments[0];
                var stringTypeMapping = ExpressionExtensions.InferTypeMapping(instance, argument);

                return(_sqlExpressionFactory.Subtract(
                           _sqlExpressionFactory.Function(
                               "instr",
                               new[]
                {
                    _sqlExpressionFactory.ApplyTypeMapping(instance, stringTypeMapping),
                    _sqlExpressionFactory.ApplyTypeMapping(argument, stringTypeMapping)
                },
                               nullable: true,
                               argumentsPropagateNullability: new[] { true, true },
                               method.ReturnType),
                           _sqlExpressionFactory.Constant(1)));
            }

            if (ReplaceMethodInfo.Equals(method))
            {
                var firstArgument     = arguments[0];
                var secondArgument    = arguments[1];
                var stringTypeMapping = ExpressionExtensions.InferTypeMapping(instance, firstArgument, secondArgument);

                return(_sqlExpressionFactory.Function(
                           "replace",
                           new[]
                {
                    _sqlExpressionFactory.ApplyTypeMapping(instance, stringTypeMapping),
                    _sqlExpressionFactory.ApplyTypeMapping(firstArgument, stringTypeMapping),
                    _sqlExpressionFactory.ApplyTypeMapping(secondArgument, stringTypeMapping)
                },
                           nullable: true,
                           argumentsPropagateNullability: new[] { true, true, true },
                           method.ReturnType,
                           stringTypeMapping));
            }

            if (ToLowerMethodInfo.Equals(method) ||
                ToUpperMethodInfo.Equals(method))
            {
                return(_sqlExpressionFactory.Function(
                           ToLowerMethodInfo.Equals(method) ? "lower" : "upper",
                           new[] { instance },
                           nullable: true,
                           argumentsPropagateNullability: new[] { true },
                           method.ReturnType,
                           instance.TypeMapping));
            }

            if (SubstringMethodInfoWithOneArg.Equals(method))
            {
                return(_sqlExpressionFactory.Function(
                           "substr",
                           new[] { instance, _sqlExpressionFactory.Add(arguments[0], _sqlExpressionFactory.Constant(1)) },
                           nullable: true,
                           argumentsPropagateNullability: new[] { true, true },
                           method.ReturnType,
                           instance.TypeMapping));
            }

            if (SubstringMethodInfoWithTwoArgs.Equals(method))
            {
                return(_sqlExpressionFactory.Function(
                           "substr",
                           new[] { instance, _sqlExpressionFactory.Add(arguments[0], _sqlExpressionFactory.Constant(1)), arguments[1] },
                           nullable: true,
                           argumentsPropagateNullability: new[] { true, true, true },
                           method.ReturnType,
                           instance.TypeMapping));
            }

            if (TrimStartMethodInfoWithoutArgs.Equals(method) == true ||
                TrimStartMethodInfoWithCharArg.Equals(method) == true ||
                TrimStartMethodInfoWithCharArrayArg.Equals(method))
            {
                return(ProcessTrimMethod(instance, arguments, "ltrim"));
            }

            if (TrimEndMethodInfoWithoutArgs.Equals(method) == true ||
                TrimEndMethodInfoWithCharArg.Equals(method) == true ||
                TrimEndMethodInfoWithCharArrayArg.Equals(method))
            {
                return(ProcessTrimMethod(instance, arguments, "rtrim"));
            }

            if (TrimMethodInfoWithoutArgs.Equals(method) == true ||
                TrimMethodInfoWithCharArg.Equals(method) == true ||
                TrimMethodInfoWithCharArrayArg.Equals(method))
            {
                return(ProcessTrimMethod(instance, arguments, "trim"));
            }

            if (ContainsMethodInfo.Equals(method))
            {
                var pattern           = arguments[0];
                var stringTypeMapping = ExpressionExtensions.InferTypeMapping(instance, pattern);

                instance = _sqlExpressionFactory.ApplyTypeMapping(instance, stringTypeMapping);
                pattern  = _sqlExpressionFactory.ApplyTypeMapping(pattern, stringTypeMapping);

                return(_sqlExpressionFactory.OrElse(
                           _sqlExpressionFactory.Equal(
                               pattern,
                               _sqlExpressionFactory.Constant(string.Empty, stringTypeMapping)),
                           _sqlExpressionFactory.GreaterThan(
                               _sqlExpressionFactory.Function(
                                   "instr",
                                   new[] { instance, pattern },
                                   nullable: true,
                                   argumentsPropagateNullability: new[] { true, true },
                                   typeof(int)),
                               _sqlExpressionFactory.Constant(0))));
            }

            if (StartsWithMethodInfo.Equals(method))
            {
                return(TranslateStartsEndsWith(instance, arguments[0], true));
            }

            if (EndsWithMethodInfo.Equals(method))
            {
                return(TranslateStartsEndsWith(instance, arguments[0], false));
            }
        }

        if (IsNullOrWhiteSpaceMethodInfo.Equals(method))
        {
            var argument = arguments[0];

            return(_sqlExpressionFactory.OrElse(
                       _sqlExpressionFactory.IsNull(argument),
                       _sqlExpressionFactory.Equal(
                           _sqlExpressionFactory.Function(
                               "trim",
                               new[] { argument },
                               nullable: true,
                               argumentsPropagateNullability: new[] { true },
                               argument.Type,
                               argument.TypeMapping),
                           _sqlExpressionFactory.Constant(string.Empty))));
        }

        if (FirstOrDefaultMethodInfoWithoutArgs.Equals(method))
        {
            var argument = arguments[0];
            return(_sqlExpressionFactory.Function(
                       "substr",
                       new[] { argument, _sqlExpressionFactory.Constant(1), _sqlExpressionFactory.Constant(1) },
                       nullable: true,
                       argumentsPropagateNullability: new[] { true, true, true },
                       method.ReturnType));
        }

        if (LastOrDefaultMethodInfoWithoutArgs.Equals(method))
        {
            var argument = arguments[0];
            return(_sqlExpressionFactory.Function(
                       "substr",
                       new[]
            {
                argument,
                _sqlExpressionFactory.Function(
                    "length",
                    new[] { argument },
                    nullable: true,
                    argumentsPropagateNullability: new[] { true },
                    typeof(int)),
                _sqlExpressionFactory.Constant(1)
            },
                       nullable: true,
                       argumentsPropagateNullability: new[] { true, true, true },
                       method.ReturnType));
        }

        return(null);
    }
Пример #12
0
        /// <summary>
        /// Identifies complex array-related constructs which cannot be translated in regular method translators, since
        /// they require accessing lambdas.
        /// </summary>
        protected virtual Expression VisitArrayMethodCall(MethodInfo method, ReadOnlyCollection <Expression> arguments)
        {
            {
                // Pattern match for .Where(e => new[] { "a", "b", "c" }.Any(p => EF.Functions.Like(e.SomeText, p))),
                // which we translate to WHERE s.""SomeText"" LIKE ANY (ARRAY['a','b','c']) (see test Any_like)
                // Note: NavigationExpander normalized Any(x) to Where(x).Any()
                if (method.IsClosedFormOf(EnumerableAnyWithPredicate) &&
                    arguments[1] is LambdaExpression wherePredicate &&
                    wherePredicate.Body is MethodCallExpression wherePredicateMethodCall && (
                        wherePredicateMethodCall.Method == Like2MethodInfo ||
                        wherePredicateMethodCall.Method == ILike2MethodInfo))
                {
                    var array = (SqlExpression)Visit(arguments[0]);
                    var match = (SqlExpression)Visit(wherePredicateMethodCall.Arguments[1]);

                    return(_sqlExpressionFactory.ArrayAnyAll(match, array, ArrayComparisonType.Any,
                                                             wherePredicateMethodCall.Method == Like2MethodInfo ? "LIKE" : "ILIKE"));
                }

                // Note: we also handle the above with equality instead of Like, see NpgsqlArrayMethodTranslator
            }

            {
                // Same for All (but without the normalization)
                if (method.IsClosedFormOf(EnumerableAll) &&
                    arguments[1] is LambdaExpression wherePredicate &&
                    wherePredicate.Body is MethodCallExpression wherePredicateMethodCall && (
                        wherePredicateMethodCall.Method == Like2MethodInfo ||
                        wherePredicateMethodCall.Method == ILike2MethodInfo))
                {
                    var array = (SqlExpression)Visit(arguments[0]);
                    var match = (SqlExpression)Visit(wherePredicateMethodCall.Arguments[1]);

                    return(_sqlExpressionFactory.ArrayAnyAll(match, array, ArrayComparisonType.All,
                                                             wherePredicateMethodCall.Method == Like2MethodInfo ? "LIKE" : "ILIKE"));
                }
            }

            {
                // Translate e => new[] { 4, 5 }.Any(p => e.SomeArray.Contains(p)),
                // using array overlap (&&)
                if (method.IsClosedFormOf(EnumerableAnyWithPredicate) &&
                    arguments[1] is LambdaExpression wherePredicate &&
                    wherePredicate.Body is MethodCallExpression wherePredicateMethodCall &&
                    wherePredicateMethodCall.Method.IsClosedFormOf(Contains) &&
                    wherePredicateMethodCall.Arguments[0].Type.IsArray &&
                    wherePredicateMethodCall.Arguments[1] is ParameterExpression parameterExpression &&
                    parameterExpression == wherePredicate.Parameters[0])
                {
                    var array1          = (SqlExpression)Visit(arguments[0]);
                    var array2          = (SqlExpression)Visit(wherePredicateMethodCall.Arguments[0]);
                    var inferredMapping = ExpressionExtensions.InferTypeMapping(array1, array2);

                    return(new SqlCustomBinaryExpression(
                               _sqlExpressionFactory.ApplyTypeMapping(array1, inferredMapping),
                               _sqlExpressionFactory.ApplyTypeMapping(array2, inferredMapping),
                               "&&",
                               typeof(bool),
                               _boolMapping));
                }
            }

            {
                // Translate e => new[] { 4, 5 }.All(p => e.SomeArray.Contains(p)),
                // using array containment (<@)
                if (method.IsClosedFormOf(EnumerableAll) &&
                    arguments[1] is LambdaExpression wherePredicate &&
                    wherePredicate.Body is MethodCallExpression wherePredicateMethodCall &&
                    wherePredicateMethodCall.Method.IsClosedFormOf(Contains) &&
                    wherePredicateMethodCall.Arguments[0].Type.IsArray &&
                    wherePredicateMethodCall.Arguments[1] is ParameterExpression parameterExpression &&
                    parameterExpression == wherePredicate.Parameters[0])
                {
                    var array1          = (SqlExpression)Visit(arguments[0]);
                    var array2          = (SqlExpression)Visit(wherePredicateMethodCall.Arguments[0]);
                    var inferredMapping = ExpressionExtensions.InferTypeMapping(array1, array2);

                    return(new SqlCustomBinaryExpression(
                               _sqlExpressionFactory.ApplyTypeMapping(array1, inferredMapping),
                               _sqlExpressionFactory.ApplyTypeMapping(array2, inferredMapping),
                               "<@",
                               typeof(bool),
                               _boolMapping));
                }
            }

            return(null);
        }
Пример #13
0
    /// <summary>
    ///     This is an internal API that supports the Entity Framework Core infrastructure and not subject to
    ///     the same compatibility standards as public APIs. It may be changed or removed without notice in
    ///     any release. You should only use it directly in your code with extreme caution and knowing that
    ///     doing so can result in application failures when updating to a new Entity Framework Core release.
    /// </summary>
    public virtual SqlExpression?Translate(
        SqlExpression?instance,
        MethodInfo method,
        IReadOnlyList <SqlExpression> arguments,
        IDiagnosticsLogger <DbLoggerCategory.Query> logger)
    {
        if (typeof(Geometry).IsAssignableFrom(method.DeclaringType) &&
            instance != null)
        {
            var geometryExpressions = new[] { instance }.Concat(
                arguments.Where(e => typeof(Geometry).IsAssignableFrom(e.Type)));
            var typeMapping = ExpressionExtensions.InferTypeMapping(geometryExpressions.ToArray());

            Check.DebugAssert(typeMapping != null, "At least one argument must have typeMapping.");
            var storeType   = typeMapping.StoreType;
            var isGeography = string.Equals(storeType, "geography", StringComparison.OrdinalIgnoreCase);

            if (MethodToFunctionName.TryGetValue(method, out var functionName) ||
                (!isGeography && GeometryMethodToFunctionName.TryGetValue(method, out functionName)))
            {
                instance = _sqlExpressionFactory.ApplyTypeMapping(
                    instance, _typeMappingSource.FindMapping(instance.Type, storeType));

                var typeMappedArguments = new List <SqlExpression>();
                foreach (var argument in arguments)
                {
                    typeMappedArguments.Add(
                        _sqlExpressionFactory.ApplyTypeMapping(
                            argument,
                            typeof(Geometry).IsAssignableFrom(argument.Type)
                                ? _typeMappingSource.FindMapping(argument.Type, storeType)
                                : _typeMappingSource.FindMapping(argument.Type)));
                }

                var resultTypeMapping = typeof(Geometry).IsAssignableFrom(method.ReturnType)
                    ? _typeMappingSource.FindMapping(method.ReturnType, storeType)
                    : _typeMappingSource.FindMapping(method.ReturnType);

                var finalArguments = Simplify(typeMappedArguments, isGeography);

                var argumentsPropagateNullability = functionName == "STBuffer"
                    ? new[] { false }
                    : functionName == "STRelate"
                        ? new[] { true, false }
                        : finalArguments.Select(_ => true).ToArray();

                return(_sqlExpressionFactory.Function(
                           instance,
                           functionName,
                           finalArguments,
                           nullable: true,
                           instancePropagatesNullability: true,
                           argumentsPropagateNullability,
                           method.ReturnType,
                           resultTypeMapping));
            }

            if (Equals(method, GetGeometryN))
            {
                return(_sqlExpressionFactory.Function(
                           instance,
                           "STGeometryN",
                           new[]
                {
                    _sqlExpressionFactory.Add(
                        arguments[0],
                        _sqlExpressionFactory.Constant(1))
                },
                           nullable: true,
                           instancePropagatesNullability: true,
                           argumentsPropagateNullability: new[] { false },
                           method.ReturnType,
                           _typeMappingSource.FindMapping(method.ReturnType, storeType)));
            }

            if (Equals(method, IsWithinDistance))
            {
                instance = _sqlExpressionFactory.ApplyTypeMapping(
                    instance, _typeMappingSource.FindMapping(instance.Type, storeType));

                var typeMappedArguments = new List <SqlExpression>();
                foreach (var argument in arguments)
                {
                    typeMappedArguments.Add(
                        _sqlExpressionFactory.ApplyTypeMapping(
                            argument,
                            typeof(Geometry).IsAssignableFrom(argument.Type)
                                ? _typeMappingSource.FindMapping(argument.Type, storeType)
                                : _typeMappingSource.FindMapping(argument.Type)));
                }

                var finalArguments = Simplify(new[] { typeMappedArguments[0] }, isGeography);

                return(_sqlExpressionFactory.LessThanOrEqual(
                           _sqlExpressionFactory.Function(
                               instance,
                               "STDistance",
                               finalArguments,
                               nullable: true,
                               instancePropagatesNullability: true,
                               argumentsPropagateNullability: finalArguments.Select(_ => true),
                               typeof(double)),
                           typeMappedArguments[1]));
            }
        }

        return(null);
    }