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