private SqlExpression MakeEndsWithExpressionImpl( [NotNull] SqlExpression target, [NotNull] SqlExpression suffix, [NotNull] SqlExpression originalSuffix) { var endsWithExpression = _sqlExpressionFactory.Equal( _sqlExpressionFactory.Function( "RIGHT", new[] { target, CharLength(suffix) }, target.Type, null), suffix); if (originalSuffix is SqlConstantExpression constantSuffix) { return((string)constantSuffix.Value == string.Empty ? _sqlExpressionFactory.Constant(true) : (SqlExpression)endsWithExpression); } else { return(_sqlExpressionFactory.OrElse( endsWithExpression, _sqlExpressionFactory.Equal(originalSuffix, _sqlExpressionFactory.Constant(string.Empty)))); } }
/// <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 MakeContainsExpressionImpl( SqlExpression target, [NotNull] Func <SqlExpression, SqlExpression> targetTransform, SqlExpression pattern, [NotNull] Func <SqlExpression, SqlExpression> patternTransform) { var stringTypeMapping = ExpressionExtensions.InferTypeMapping(target, pattern); target = _sqlExpressionFactory.ApplyTypeMapping(target, stringTypeMapping); pattern = _sqlExpressionFactory.ApplyTypeMapping(pattern, stringTypeMapping); if (pattern is SqlConstantExpression constantPatternExpression) { // The prefix is constant. Aside from null or empty, we escape all special characters (%, _, \) // in C# and send a simple LIKE. if (constantPatternExpression.Value is string constantPatternString) { // TRUE (pattern == "") // something LIKE '%foo%' (pattern != "") return(constantPatternString == string.Empty ? (SqlExpression)_sqlExpressionFactory.Constant(true) : _sqlExpressionFactory.Like( targetTransform(target), patternTransform(_sqlExpressionFactory.Constant('%' + EscapeLikePattern(constantPatternString) + '%')))); } // TODO: EF Core 5 // 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))); } // 'foo' LIKE '' OR LOCATE('foo', 'barfoobar') > 0 // This cannot be "' ' = '' OR ..", because ' ' would be trimmed to '' when using equals, but not when using LIKE. // Using an empty pattern `LOCATE('', 'barfoobar')` returns 1. return(_sqlExpressionFactory.OrElse( _sqlExpressionFactory.Like( pattern, _sqlExpressionFactory.Constant(string.Empty, stringTypeMapping)), _sqlExpressionFactory.GreaterThan( _sqlExpressionFactory.NullableFunction( "LOCATE", new[] { patternTransform(pattern), targetTransform(target) }, typeof(int)), _sqlExpressionFactory.Constant(0)))); }
private static SqlExpression GetAndoyerDistance( SqlExpression left, SqlExpression right, Type resultType, MySqlSqlExpressionFactory sqlExpressionFactory) { SqlExpression toDegrees(SqlExpression coord) => sqlExpressionFactory.Divide( sqlExpressionFactory.Multiply( coord, sqlExpressionFactory.NonNullableFunction( "PI", Array.Empty <SqlExpression>(), resultType)), sqlExpressionFactory.Constant(180.0)); SqlExpression xCoord(SqlExpression point) => sqlExpressionFactory.NullableFunction( "ST_X", new[] { point }, resultType); SqlExpression yCoord(SqlExpression point) => sqlExpressionFactory.NullableFunction( "ST_Y", new[] { point }, resultType); var c0 = sqlExpressionFactory.Constant(0.0); var c1 = sqlExpressionFactory.Constant(1.0); var c2 = sqlExpressionFactory.Constant(2.0); var c3 = sqlExpressionFactory.Constant(3.0); var c2Int = sqlExpressionFactory.Constant(2); var lon1 = toDegrees(xCoord(left)); var lat1 = toDegrees(yCoord(left)); var lon2 = toDegrees(xCoord(right)); var lat2 = toDegrees(yCoord(right)); var g = sqlExpressionFactory.Divide( sqlExpressionFactory.Subtract( lat1, lat2), c2); var lambda = sqlExpressionFactory.Divide( sqlExpressionFactory.Subtract( lon1, lon2), c2); var f = sqlExpressionFactory.Divide( sqlExpressionFactory.Add( lat1, lat2), c2); var sinG2 = sqlExpressionFactory.NullableFunction( "POWER", new SqlExpression[] { sqlExpressionFactory.NullableFunction( "SIN", new[] { g }, resultType), c2Int }, resultType); var cosG2 = sqlExpressionFactory.NullableFunction( "POWER", new SqlExpression[] { sqlExpressionFactory.NullableFunction( "COS", new[] { g }, resultType), c2Int }, resultType); var sinF2 = sqlExpressionFactory.NullableFunction( "POWER", new SqlExpression[] { sqlExpressionFactory.NullableFunction( "SIN", new[] { f }, resultType), c2Int }, resultType); var cosF2 = sqlExpressionFactory.NullableFunction( "POWER", new SqlExpression[] { sqlExpressionFactory.NullableFunction( "COS", new[] { f }, resultType), c2Int }, resultType); var sinL2 = sqlExpressionFactory.NullableFunction( "POWER", new SqlExpression[] { sqlExpressionFactory.NullableFunction( "SIN", new[] { lambda }, resultType), c2Int }, resultType); var cosL2 = sqlExpressionFactory.NullableFunction( "POWER", new SqlExpression[] { sqlExpressionFactory.NullableFunction( "COS", new[] { lambda }, resultType), c2Int }, resultType); var s = sqlExpressionFactory.Add( sqlExpressionFactory.Multiply(sinG2, cosL2), sqlExpressionFactory.Multiply(cosF2, sinL2)); var c = sqlExpressionFactory.Add( sqlExpressionFactory.Multiply(cosG2, cosL2), sqlExpressionFactory.Multiply(sinF2, sinL2)); var radiusA = sqlExpressionFactory.Constant(6378137.0); var radiusB = sqlExpressionFactory.Constant(6356752.3142451793); var flattening = sqlExpressionFactory.Divide( sqlExpressionFactory.Subtract(radiusA, radiusB), radiusA); var omega = sqlExpressionFactory.NullableFunction( "ATAN", new[] { sqlExpressionFactory.NullableFunction( "SQRT", new[] { sqlExpressionFactory.Divide(s, c) }, resultType, false) }, resultType); var r3 = sqlExpressionFactory.Divide( sqlExpressionFactory.Multiply( c3, sqlExpressionFactory.NullableFunction( "SQRT", new[] { sqlExpressionFactory.Multiply(s, c) }, resultType, false)), omega); var d = sqlExpressionFactory.Multiply( sqlExpressionFactory.Multiply(c2, omega), radiusA); var h1 = sqlExpressionFactory.Divide( sqlExpressionFactory.Subtract(r3, c1), sqlExpressionFactory.Multiply(c2, c)); var h2 = sqlExpressionFactory.Divide( sqlExpressionFactory.Add(r3, c1), sqlExpressionFactory.Multiply(c2, s)); var andoyer = sqlExpressionFactory.Multiply( d, sqlExpressionFactory.Add( c1, sqlExpressionFactory.Multiply( flattening, sqlExpressionFactory.Subtract( sqlExpressionFactory.Multiply( sqlExpressionFactory.Multiply( h1, sinF2), cosG2), sqlExpressionFactory.Multiply( sqlExpressionFactory.Multiply( h2, cosF2), sinG2))))); return(sqlExpressionFactory.Case( new[] { new CaseWhenClause( sqlExpressionFactory.OrElse( sqlExpressionFactory.OrElse( sqlExpressionFactory.AndAlso( sqlExpressionFactory.Equal(lambda, c0), sqlExpressionFactory.Equal(g, c0)), sqlExpressionFactory.Equal(s, c0)), sqlExpressionFactory.Equal(c, c0)), c0), }, andoyer)); }