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);
}
/// <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);
}
/// <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
});
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))));
}
/// <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);
}
/// <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);
}
/// <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);
}
