private SqlBinaryExpression MakeStartsWithExpressionImpl(
SqlExpression target,
SqlExpression prefix,
SqlExpression originalPrefix = null)
{
// BUG: EF Core #17389 will lead to a System.NullReferenceException, if SqlExpressionFactory.Like()
// is being called with match and pattern as two expressions, that have not been applied a
// TypeMapping yet and no escapeChar (null).
// As a workaround, apply/infer the type mapping for the match expression manually for now.
return(_sqlExpressionFactory.AndAlso(
_sqlExpressionFactory.Like(
target,
_sqlExpressionFactory.ApplyDefaultTypeMapping(_sqlExpressionFactory.Function(
"CONCAT",
// when performing the like it is preferable to use the untransformed prefix
// value to ensure the index can be used
new[] { originalPrefix ?? prefix, _sqlExpressionFactory.Constant("%") },
typeof(string)))),
_sqlExpressionFactory.Equal(
_sqlExpressionFactory.Function(
"LEFT",
new[]
{
target,
CharLength(prefix)
},
typeof(string)),
prefix
)));
}
/// <summary>
/// This API supports the Entity Framework Core infrastructure and is not intended to be used
/// directly from your code. This API may change or be removed in future releases.
/// </summary>
public SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList <SqlExpression> arguments)
{
if (_likeMethodInfos.Any(m => Equals(method, m)))
{
var match = _sqlExpressionFactory.ApplyDefaultTypeMapping(arguments[1]);
var pattern = InferStringTypeMappingOrApplyDefault(
arguments[2],
match.TypeMapping);
var excapeChar = arguments.Count == 4
? InferStringTypeMappingOrApplyDefault(
arguments[3],
match.TypeMapping)
: null;
return(_sqlExpressionFactory.Like(
match,
pattern,
excapeChar));
}
if (Equals(method, _matchMethodInfo))
{
if (arguments[3] is SqlConstantExpression constant)
{
return(_sqlExpressionFactory.MakeMatch(
arguments[1],
arguments[2],
(MySqlMatchSearchMode)constant.Value));
}
if (arguments[3] is SqlParameterExpression parameter)
{
// Use nested OR clauses here, because MariaDB does not support MATCH...AGAINST from inside of
// CASE statements and the nested OR clauses use the fulltext index, while using CASE does not:
// <search_mode_1> = @p AND MATCH ... AGAINST ... OR
// <search_mode_2> = @p AND MATCH ... AGAINST ... OR [...]
var andClauses = Enum.GetValues(typeof(MySqlMatchSearchMode))
.Cast <MySqlMatchSearchMode>()
.OrderByDescending(m => m)
.Select(m => _sqlExpressionFactory.AndAlso(
_sqlExpressionFactory.Equal(parameter, _sqlExpressionFactory.Constant(m)),
_sqlExpressionFactory.MakeMatch(arguments[1], arguments[2], m)))
.ToArray();
return(andClauses
.Skip(1)
.Aggregate(
andClauses.First(),
(currentAnd, previousExpression) => _sqlExpressionFactory.OrElse(previousExpression, currentAnd)));
}
}
return(null);
}
private SqlExpression MakeStartsWithEndsWithExpressionImpl(
SqlExpression target,
[NotNull] Func <SqlExpression, SqlExpression> targetTransform,
SqlExpression prefixSuffix,
[NotNull] Func <SqlExpression, SqlExpression> prefixSuffixTransform,
bool startsWith)
{
var stringTypeMapping = ExpressionExtensions.InferTypeMapping(target, prefixSuffix);
target = _sqlExpressionFactory.ApplyTypeMapping(target, stringTypeMapping);
prefixSuffix = _sqlExpressionFactory.ApplyTypeMapping(prefixSuffix, stringTypeMapping);
if (prefixSuffix is SqlConstantExpression constantPrefixSuffixExpression)
{
// The prefix is constant. Aside from null or empty, we escape all special characters (%, _, \)
// in C# and send a simple LIKE.
if (constantPrefixSuffixExpression.Value is string constantPrefixSuffixString)
{
// TRUE (pattern == "")
// something LIKE 'foo%' (pattern != "", StartsWith())
// something LIKE '%foo' (pattern != "", EndsWith())
return(constantPrefixSuffixString == string.Empty
? (SqlExpression)_sqlExpressionFactory.Constant(true)
: _sqlExpressionFactory.Like(
targetTransform(target),
prefixSuffixTransform(
_sqlExpressionFactory.Constant(
(startsWith
? string.Empty
: "%") +
EscapeLikePattern(constantPrefixSuffixString) +
(startsWith
? "%"
: string.Empty)))));
}
// https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/996#issuecomment-607876040
// Can return NULL in .NET 5 after https://github.com/dotnet/efcore/issues/20498 has been fixed.
// `something LIKE NULL` always returns `NULL`. We will return `false`, to indicate, that no match
// could be found, because returning a constant of `NULL` will throw later in EF Core when used as
// a predicate.
// return _sqlExpressionFactory.Constant(null, RelationalTypeMapping.NullMapping);
// This results in NULL anyway, but works around EF Core's inability to handle predicates that are
// constant null values.
return(_sqlExpressionFactory.Like(target, _sqlExpressionFactory.Constant(null, stringTypeMapping)));
}
// TODO: Generally, LEFT & compare is faster than escaping potential pattern characters with REPLACE().
// However, this might not be the case, if the pattern is constant after all (e.g. `LCASE('fo%o')`), in
// which case, `something LIKE CONCAT(REPLACE(REPLACE(LCASE('fo%o'), '%', '\\%'), '_', '\\_'), '%')` should
// be faster than `LEFT(something, CHAR_LENGTH('fo%o')) = LCASE('fo%o')`.
// See https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/996#issuecomment-607733553
// The prefix is non-constant, we use LEFT to extract the substring and compare.
return(_sqlExpressionFactory.Equal(
_sqlExpressionFactory.NullableFunction(
startsWith
? "LEFT"
: "RIGHT",
new[] { targetTransform(target), CharLength(prefixSuffix) },
typeof(string),
stringTypeMapping),
prefixSuffixTransform(prefixSuffix)));
}
