public virtual SqlExpression Translate( SqlExpression instance, MethodInfo method, IReadOnlyList <SqlExpression> arguments, IDiagnosticsLogger <DbLoggerCategory.Query> logger) { Check.NotNull(method, nameof(method)); Check.NotNull(arguments, nameof(arguments)); Check.NotNull(logger, nameof(logger)); if (method.IsGenericMethod && method.GetGenericMethodDefinition().Equals(_containsMethod) && arguments[0].Type == typeof(byte[])) { var source = arguments[0]; var sourceTypeMapping = source.TypeMapping; var value = arguments[1] is SqlConstantExpression constantValue ? (SqlExpression)_sqlExpressionFactory.Constant(new[] { (byte)constantValue.Value }, sourceTypeMapping) : _sqlExpressionFactory.Convert(arguments[1], typeof(byte[]), sourceTypeMapping); return(_sqlExpressionFactory.GreaterThan( _sqlExpressionFactory.NullableFunction( "LOCATE", new[] { value, source }, typeof(int)), _sqlExpressionFactory.Constant(0))); } return(null); }
private static SqlFunctionExpression SetSrid( SqlExpression geometry, int srid, MySqlSqlExpressionFactory sqlExpressionFactory, IMySqlOptions options) => options.ServerVersion.Supports.SpatialSetSridFunction ? sqlExpressionFactory.NullableFunction( "ST_SRID", new[] { geometry, sqlExpressionFactory.Constant(srid) }, typeof(int)) : sqlExpressionFactory.NullableFunction( "ST_GeomFromWKB", new SqlExpression[] { sqlExpressionFactory.NullableFunction( "ST_AsBinary", new[] { geometry }, typeof(byte[])), sqlExpressionFactory.Constant(srid) }, geometry.Type);
public MySqlStringComparisonMethodTranslator(ISqlExpressionFactory sqlExpressionFactory, IMySqlOptions options) { _sqlExpressionFactory = (MySqlSqlExpressionFactory)sqlExpressionFactory; _options = options; _caseSensitiveComparisons = _sqlExpressionFactory.Constant( new[] { StringComparison.Ordinal, StringComparison.CurrentCulture, StringComparison.InvariantCulture }); }
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 ))); }
public virtual SqlExpression Translate(SqlExpression instance, MemberInfo member, Type returnType) { if (instance?.TypeMapping is MySqlJsonTypeMapping || instance is MySqlJsonTraversalExpression) { // Path locations need to be rendered without surrounding quotes, because the path itself already // has quotes. var sqlConstantExpression = _sqlExpressionFactory.ApplyDefaultTypeMapping( _sqlExpressionFactory.Constant( GetJsonPropertyName(member) ?? member.Name, _unquotedStringTypeMapping)); return(TranslateMemberAccess( instance, sqlConstantExpression, returnType)); } return(null); }
public virtual SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList <SqlExpression> arguments) { if (instance != null && instance.Type.IsGenericList() && method.Name == "get_Item" && arguments.Count == 1) { // Try translating indexing inside json column return(_jsonPocoTranslator.TranslateMemberAccess(instance, arguments[0], method.ReturnType)); } // Predicate-less Any - translate to a simple length check. if (method.IsClosedFormOf(_enumerableAnyWithoutPredicate) && arguments.Count == 1 && arguments[0].Type.TryGetElementType(out _) && arguments[0].TypeMapping is MySqlJsonTypeMapping) { return(_sqlExpressionFactory.GreaterThan( _jsonPocoTranslator.TranslateArrayLength(arguments[0]), _sqlExpressionFactory.Constant(0))); } return(null); }
private static SqlExpression GetHaversineDistance( SqlExpression left, SqlExpression right, Type resultType, MySqlSqlExpressionFactory sqlExpressionFactory) { // HAVERSINE = 6371000 * 2 * ASIN( // SQRT( // POWER(SIN((ST_Y(pt2) - ST_Y(pt1)) * pi()/180 / 2), 2) + // COS(ST_Y(pt1) * pi()/180) * // COS(ST_Y(pt2) * pi()/180) * // POWER(SIN((ST_X(pt2) - ST_X(pt1)) * pi()/180 / 2), 2) // ) // ) return(sqlExpressionFactory.Multiply( sqlExpressionFactory.Constant(6370986.0), // see https://postgis.net/docs/manual-1.4/ST_Distance_Sphere.html sqlExpressionFactory.Multiply( sqlExpressionFactory.Constant(2.0), sqlExpressionFactory.NullableFunction( "ASIN", new[] { sqlExpressionFactory.NullableFunction( "SQRT", new[] { sqlExpressionFactory.Add( sqlExpressionFactory.NullableFunction( "POWER", new SqlExpression[] { sqlExpressionFactory.NullableFunction( "SIN", new[] { sqlExpressionFactory.Divide( sqlExpressionFactory.Divide( sqlExpressionFactory.Multiply( sqlExpressionFactory.Subtract( sqlExpressionFactory.NullableFunction( "ST_Y", new[] { right }, resultType), sqlExpressionFactory.NullableFunction( "ST_Y", new[] { left }, resultType)), sqlExpressionFactory.NonNullableFunction( "PI", Array.Empty <SqlExpression>(), resultType)), sqlExpressionFactory.Constant(180.0)), sqlExpressionFactory.Constant(2.0)) }, resultType), sqlExpressionFactory.Constant(2), }, resultType), sqlExpressionFactory.Multiply( sqlExpressionFactory.NullableFunction( "COS", new[] { sqlExpressionFactory.Divide( sqlExpressionFactory.Multiply( sqlExpressionFactory.NullableFunction( "ST_Y", new[] { left }, resultType), sqlExpressionFactory.NonNullableFunction( "PI", Array.Empty <SqlExpression>(), resultType)), sqlExpressionFactory.Constant(180.0)), }, resultType), sqlExpressionFactory.Multiply( sqlExpressionFactory.NullableFunction( "COS", new[] { sqlExpressionFactory.Divide( sqlExpressionFactory.Multiply( sqlExpressionFactory.NullableFunction( "ST_Y", new[] { right }, resultType), sqlExpressionFactory.NonNullableFunction( "PI", Array.Empty <SqlExpression>(), resultType)), sqlExpressionFactory.Constant(180.0)), }, resultType), sqlExpressionFactory.NullableFunction( "POWER", new SqlExpression[] { sqlExpressionFactory.NullableFunction( "SIN", new[] { sqlExpressionFactory.Divide( sqlExpressionFactory.Divide( sqlExpressionFactory.Multiply( sqlExpressionFactory.Subtract( sqlExpressionFactory.NullableFunction( "ST_X", new[] { right }, resultType), sqlExpressionFactory.NullableFunction( "ST_X", new[] { left }, resultType)), sqlExpressionFactory.NonNullableFunction( "PI", Array.Empty <SqlExpression>(), resultType)), sqlExpressionFactory.Constant(180.0)), sqlExpressionFactory.Constant(2.0)) }, resultType), sqlExpressionFactory.Constant(2), }, resultType)))) }, resultType, false) }, resultType, false)))); }
public static SqlExpression GetStDistanceSphereFunctionCall( SqlExpression left, SqlExpression right, SpatialDistanceAlgorithm algorithm, Type resultType, RelationalTypeMapping resultTypeMapping, MySqlSqlExpressionFactory sqlExpressionFactory, IMySqlOptions options) { if (options.ServerVersion.Supports.SpatialDistanceSphereFunction) { if (algorithm == SpatialDistanceAlgorithm.Native) { // Returns null for empty geometry arguments. return(sqlExpressionFactory.NullableFunction( "ST_Distance_Sphere", new[] { left, right }, resultType, resultTypeMapping, false)); } if (algorithm == SpatialDistanceAlgorithm.Andoyer && options.ServerVersion.Supports.SpatialDistanceFunctionImplementsAndoyer) { // The `ST_Distance()` in MySQL already uses the Andoyer algorithm, when SRID 4326 is associated // with the geometry. // CHECK: It might be faster to just run the custom implementation, if `ST_SRID()` does not support // a second parameter yet (see SetSrid()). return(sqlExpressionFactory.Case( new[] { new CaseWhenClause( sqlExpressionFactory.Equal( sqlExpressionFactory.NullableFunction( "ST_SRID", new[] { left }, typeof(int)), sqlExpressionFactory.Constant(4326)), GetStDistanceFunctionCall( left, right, resultType, resultTypeMapping, sqlExpressionFactory)) }, GetStDistanceFunctionCall( SetSrid(left, 4326, sqlExpressionFactory, options), SetSrid(right, 4326, sqlExpressionFactory, options), resultType, resultTypeMapping, sqlExpressionFactory))); } if (algorithm == SpatialDistanceAlgorithm.Haversine) { // The Haversine algorithm assumes planar coordinates. return(sqlExpressionFactory.Case( new[] { new CaseWhenClause( sqlExpressionFactory.Equal( sqlExpressionFactory.NullableFunction( "ST_SRID", new[] { left }, typeof(int)), sqlExpressionFactory.Constant(0)), GetHaversineDistance( left, right, resultType, sqlExpressionFactory)) }, GetHaversineDistance( SetSrid(left, 0, sqlExpressionFactory, options), SetSrid(right, 0, sqlExpressionFactory, options), resultType, sqlExpressionFactory))); } } if (algorithm == SpatialDistanceAlgorithm.Haversine) { return(GetHaversineDistance(left, right, resultType, sqlExpressionFactory)); } return(GetAndoyerDistance(left, right, resultType, sqlExpressionFactory)); }
/// <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, IDiagnosticsLogger <DbLoggerCategory.Query> logger) { 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))); } } if (_hexMethodInfos.Any(m => Equals(method, m))) { return(_sqlExpressionFactory.NullableFunction( "HEX", new[] { arguments[1] }, typeof(string))); } if (Equals(method, _unhexMethodInfo)) { return(_sqlExpressionFactory.NullableFunction( "UNHEX", new[] { arguments[1] }, typeof(string), false)); } return(null); }
private SqlExpression ConvertFromJsonExtract(SqlExpression expression, Type returnType) => returnType == typeof(bool) ? _sqlExpressionFactory.Equal( expression, _sqlExpressionFactory.Constant(true, _typeMappingSource.FindMapping(typeof(bool)))) : expression;
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))); }
public virtual SqlExpression Translate( SqlExpression instance, MethodInfo method, IReadOnlyList <SqlExpression> arguments, IDiagnosticsLogger <DbLoggerCategory.Query> logger) { if (method.DeclaringType != typeof(MySqlJsonDbFunctionsExtensions)) { return(null); } var args = arguments // Skip useless DbFunctions instance .Skip(1) // JSON extensions accept object parameters for JSON, since they must be able to handle POCOs, strings or DOM types. // This means they come wrapped in a convert node, which we need to remove. // Convert nodes may also come from wrapping JsonTraversalExpressions generated through POCO traversal. .Select(RemoveConvert) // CHECK: Either just not doing this at all is fine, or not applying it to JsonQuote and JsonUnquote // (as already implemented below) is needed. An alternative would be to move the check into the local // json() function. // // If a function is invoked over a JSON traversal expression, that expression may come with // returnText: true (i.e. operator ->> and not ->). Since the functions below require a json object and // not text, we transform it. // .Select( // a => a is MySqlJsonTraversalExpression traversal && // method.Name != nameof(MySqlJsonDbFunctionsExtensions.JsonQuote) && // method.Name != nameof(MySqlJsonDbFunctionsExtensions.JsonUnquote) // ? withReturnsText(traversal, false) // : a) .ToArray(); var result = method.Name switch { nameof(MySqlJsonDbFunctionsExtensions.AsJson) => _sqlExpressionFactory.ApplyTypeMapping( args[0], _sqlExpressionFactory.FindMapping(method.ReturnType, "json")), nameof(MySqlJsonDbFunctionsExtensions.JsonType) => _sqlExpressionFactory.NullableFunction( "JSON_TYPE", new[] { Json(args[0]) }, typeof(string)), nameof(MySqlJsonDbFunctionsExtensions.JsonQuote) => _sqlExpressionFactory.NullableFunction( "JSON_QUOTE", new[] { args[0] }, method.ReturnType), nameof(MySqlJsonDbFunctionsExtensions.JsonUnquote) => _sqlExpressionFactory.NullableFunction( "JSON_UNQUOTE", new[] { args[0] }, method.ReturnType), nameof(MySqlJsonDbFunctionsExtensions.JsonExtract) => _sqlExpressionFactory.NullableFunction( "JSON_EXTRACT", Array.Empty <SqlExpression>() .Append(Json(args[0])) .Concat(DeconstructParamsArray(args[1])), method.ReturnType, _sqlExpressionFactory.FindMapping(method.ReturnType, "json"), false), nameof(MySqlJsonDbFunctionsExtensions.JsonContains) => _sqlExpressionFactory.NullableFunction( "JSON_CONTAINS", args.Length >= 3 ? new[] { Json(args[0]), args[1], args[2] } : new[] { Json(args[0]), args[1] }, typeof(bool)), nameof(MySqlJsonDbFunctionsExtensions.JsonContainsPath) => _sqlExpressionFactory.NullableFunction( "JSON_CONTAINS_PATH", new[] { Json(args[0]), _sqlExpressionFactory.Constant("one"), args[1] }, typeof(bool)), nameof(MySqlJsonDbFunctionsExtensions.JsonContainsPathAny) => _sqlExpressionFactory.NullableFunction( "JSON_CONTAINS_PATH", Array.Empty <SqlExpression>() .Append(Json(args[0])) .Append(_sqlExpressionFactory.Constant("one")) .Concat(DeconstructParamsArray(args[1])), typeof(bool)), nameof(MySqlJsonDbFunctionsExtensions.JsonContainsPathAll) => _sqlExpressionFactory.NullableFunction( "JSON_CONTAINS_PATH", Array.Empty <SqlExpression>() .Append(Json(args[0])) .Append(_sqlExpressionFactory.Constant("all")) .Concat(DeconstructParamsArray(args[1])), typeof(bool)), nameof(MySqlJsonDbFunctionsExtensions.JsonSearchAny) => _sqlExpressionFactory.IsNotNull( _sqlExpressionFactory.NullableFunction( "JSON_SEARCH", Array.Empty <SqlExpression>() .Append(Json(args[0])) .Append(_sqlExpressionFactory.Constant("one")) .Append(args[1]) .AppendIfTrue( args.Length >= 3, () => args.Length >= 4 ? args[3] : _sqlExpressionFactory.Constant(null, RelationalTypeMapping.NullMapping)) .AppendIfTrue(args.Length >= 3, () => args[2]), typeof(bool), null, false)), // JSON_SEARCH can return null even if all arguments are not null _ => null }; return(result); SqlExpression Json(SqlExpression e) => _sqlExpressionFactory.ApplyTypeMapping(EnsureJson(e), _sqlExpressionFactory.FindMapping(e.Type, "json"));
public SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList <SqlExpression> arguments, IDiagnosticsLogger <DbLoggerCategory.Query> logger) { if (instance != null && instance.Type.IsGenericList() && method.Name == "get_Item" && arguments.Count == 1) { // Try translating indexing inside json column return(_jsonPocoTranslator.TranslateMemberAccess(instance, arguments[0], method.ReturnType)); } // Predicate-less Any - translate to a simple length check. if (method.IsClosedFormOf(_enumerableAnyWithoutPredicate) && arguments.Count == 1 && arguments[0].Type.TryGetElementType(out _) && arguments[0].TypeMapping is MySqlJsonTypeMapping) { return(_sqlExpressionFactory.GreaterThan( _jsonPocoTranslator.TranslateArrayLength(arguments[0]), _sqlExpressionFactory.Constant(0))); } if (method.DeclaringType != typeof(JsonElement) || !(instance.TypeMapping is MySqlJsonTypeMapping mapping)) { return(null); } // The root of the JSON expression is a ColumnExpression. We wrap that with an empty traversal // expression (col->'$'); subsequent traversals will gradually append the path into that. // Note that it's possible to call methods such as GetString() directly on the root, and the // empty traversal is necessary to properly convert it to a text. instance = instance is ColumnExpression columnExpression ? _sqlExpressionFactory.JsonTraversal( columnExpression, returnsText : false, typeof(string), mapping) : instance; if (method == _getProperty) { return(instance is MySqlJsonTraversalExpression prevPathTraversal ? prevPathTraversal.Append( ApplyPathLocationTypeMapping(arguments[0]), typeof(JsonElement), _typeMappingSource.FindMapping(typeof(JsonElement))) : null); } if (method == _arrayIndexer) { return(instance is MySqlJsonTraversalExpression prevPathTraversal ? prevPathTraversal.Append( _sqlExpressionFactory.JsonArrayIndex(ApplyPathLocationTypeMapping(arguments[0])), typeof(JsonElement), _typeMappingSource.FindMapping(typeof(JsonElement))) : null); } if (_getMethods.Contains(method.Name) && arguments.Count == 0 && instance is MySqlJsonTraversalExpression traversal) { return(ConvertFromJsonExtract( traversal.Clone( method.Name == nameof(JsonElement.GetString), method.ReturnType, _typeMappingSource.FindMapping(method.ReturnType) ), method.ReturnType)); } if (method == _getArrayLength) { // Could return NULL if the path is not found, but we would be alright to throw then. return(_sqlExpressionFactory.NullableFunction( "JSON_LENGTH", new[] { instance }, typeof(int), false)); } if (method.Name.StartsWith("TryGet") && arguments.Count == 0) { throw new InvalidOperationException($"The TryGet* methods on {nameof(JsonElement)} aren't translated yet, use Get* instead.'"); } return(null); }
public virtual SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList <SqlExpression> arguments) { if (method.DeclaringType != typeof(MySqlJsonDbFunctionsExtensions)) { return(null); } var args = arguments // Skip useless DbFunctions instance .Skip(1) // JSON extensions accept object parameters for JSON, since they must be able to handle POCOs, strings or DOM types. // This means they come wrapped in a convert node, which we need to remove. // Convert nodes may also come from wrapping JsonTraversalExpressions generated through POCO traversal. .Select(removeConvert) // If a function is invoked over a JSON traversal expression, that expression may come with // returnText: true (i.e. operator ->> and not ->). Since the functions below require a json object and // not text, we transform it. .Select(a => a is MySqlJsonTraversalExpression traversal ? withReturnsText(traversal, false) : a) .ToArray(); if (!args.Any(a => a.TypeMapping is MySqlJsonTypeMapping || a is MySqlJsonTraversalExpression)) { throw new InvalidOperationException("The EF JSON methods require a JSON parameter but none was found."); } var result = method.Name switch { nameof(MySqlJsonDbFunctionsExtensions.JsonType) => _sqlExpressionFactory.Function( "JSON_TYPE", new[] { args[0] }, typeof(string)), nameof(MySqlJsonDbFunctionsExtensions.JsonContains) => _sqlExpressionFactory.Function( "JSON_CONTAINS", args.Length >= 3 ? new[] { json(args[0]), args[1], args[2] } : new[] { json(args[0]), args[1] }, typeof(bool)), nameof(MySqlJsonDbFunctionsExtensions.JsonContainsPath) => _sqlExpressionFactory.Function( "JSON_CONTAINS_PATH", new[] { json(args[0]), _sqlExpressionFactory.Constant("one"), args[1] }, typeof(bool)), nameof(MySqlJsonDbFunctionsExtensions.JsonContainsPathAny) => _sqlExpressionFactory.Function( "JSON_CONTAINS_PATH", Array.Empty <SqlExpression>() .Append(json(args[0])) .Append(_sqlExpressionFactory.Constant("one")) .Concat(deconstructParamsArray(args[1])), typeof(bool)), nameof(MySqlJsonDbFunctionsExtensions.JsonContainsPathAll) => _sqlExpressionFactory.Function( "JSON_CONTAINS_PATH", Array.Empty <SqlExpression>() .Append(json(args[0])) .Append(_sqlExpressionFactory.Constant("all")) .Concat(deconstructParamsArray(args[1])), typeof(bool)), nameof(MySqlJsonDbFunctionsExtensions.JsonSearchAny) => _sqlExpressionFactory.IsNotNull( _sqlExpressionFactory.Function( "JSON_SEARCH", Array.Empty <SqlExpression>() .Append(json(args[0])) .Append(_sqlExpressionFactory.Constant("one")) .Append(args[1]) .AppendIfTrue(args.Length >= 3, () => args.Length >= 4 ? args[3] : _sqlExpressionFactory.Constant(null, RelationalTypeMapping.NullMapping)) .AppendIfTrue(args.Length >= 3, () => args[2]), typeof(bool))), nameof(MySqlJsonDbFunctionsExtensions.JsonSearchAll) => _sqlExpressionFactory.IsNotNull( _sqlExpressionFactory.Function( "JSON_SEARCH", Array.Empty <SqlExpression>() .Append(json(args[0])) .Append(_sqlExpressionFactory.Constant("all")) .Append(args[1]) .AppendIfTrue(args.Length >= 3, () => args.Length >= 4 ? args[3] : _sqlExpressionFactory.Constant(null, RelationalTypeMapping.NullMapping)) .AppendIfTrue(args.Length >= 3, () => args[2]), typeof(bool))) as SqlExpression, _ => null }; return(result); SqlExpression json(SqlExpression e) => _sqlExpressionFactory.ApplyTypeMapping(e, _sqlExpressionFactory.FindMapping(e.Type, "json"));
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)); }
/// <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 virtual SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList <SqlExpression> arguments, IDiagnosticsLogger <DbLoggerCategory.Query> logger) { if (Equals(method, _spatialDistancePlanarMethodInfo)) { // MySQL 8 uses the Andoyer algorithm by default for `ST_Distance()`, if an SRID of 4326 has been // associated with the geometry. // Since this call explicitly asked for a planar distance calculation, we need to ensure that // MySQL actually does that. // MariaDB ignores SRIDs and always calculates the planar distance. // CHECK: It could be faster to just manually apply the Pythagoras Theorem instead of changing the // SRID in the case where ST_SRID() does not support a second parameter yet (see // SetSrid()). if (_options.ServerVersion.Supports.SpatialSupportFunctionAdditions && _options.ServerVersion.Supports.SpatialDistanceFunctionImplementsAndoyer) { return(_sqlExpressionFactory.Case( new[] { new CaseWhenClause( _sqlExpressionFactory.Equal( _sqlExpressionFactory.NullableFunction( "ST_SRID", new[] { arguments[1] }, typeof(int)), _sqlExpressionFactory.Constant(0)), GetStDistanceFunctionCall( arguments[1], arguments[2], method.ReturnType, _typeMappingSource.FindMapping(method.ReturnType), _sqlExpressionFactory)) }, GetStDistanceFunctionCall( SetSrid(arguments[1], 0, _sqlExpressionFactory, _options), SetSrid(arguments[2], 0, _sqlExpressionFactory, _options), method.ReturnType, _typeMappingSource.FindMapping(method.ReturnType), _sqlExpressionFactory))); } return(GetStDistanceFunctionCall( arguments[1], arguments[2], method.ReturnType, _typeMappingSource.FindMapping(method.ReturnType), _sqlExpressionFactory)); } if (Equals(method, _spatialDistanceSphere)) { if (!(arguments[3] is SqlConstantExpression algorithm)) { throw new InvalidOperationException("The 'algorithm' parameter must be supplied as a constant."); } return(GetStDistanceSphereFunctionCall( arguments[1], arguments[2], (SpatialDistanceAlgorithm)algorithm.Value, method.ReturnType, _typeMappingSource.FindMapping(method.ReturnType), _sqlExpressionFactory, _options)); } return(null); }
public virtual SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList <SqlExpression> arguments) { var traversal = GetTraversalExpression(instance, arguments); if (traversal == null) { return(null); } if (typeof(JToken).IsAssignableFrom(method.DeclaringType) && method.Name == "get_Item" && arguments.Count == 1) { var indexExpression = _sqlExpressionFactory.ApplyDefaultTypeMapping(arguments[0]); if (method.DeclaringType == typeof(JArray) || indexExpression.Type == typeof(int)) { // Try translating indexing inside json column. return(_jsonPocoTranslator.TranslateMemberAccess( traversal, _sqlExpressionFactory.JsonArrayIndex(indexExpression), method.ReturnType)); } return(traversal.Append( ApplyPathLocationTypeMapping(arguments[0]), method.DeclaringType, _typeMappingSource.FindMapping(method.DeclaringType))); } // Support for .Value<T>() and .Value<U, T>(): if (instance == null && method.Name == nameof(global::Newtonsoft.Json.Linq.Extensions.Value) && method.DeclaringType == typeof(global::Newtonsoft.Json.Linq.Extensions) && method.IsGenericMethod && method.GetParameters().Length == 1 && arguments.Count == 1) { return(ConvertFromJsonExtract( traversal.Clone( method.ReturnType == typeof(string), method.ReturnType, _typeMappingSource.FindMapping(method.ReturnType) ), method.ReturnType)); } // Support for Count() if (instance == null && method.Name == nameof(Enumerable.Count) && method.DeclaringType == typeof(Enumerable) && method.IsGenericMethod && method.GetParameters().Length == 1 && arguments.Count == 1) { return(_jsonPocoTranslator.TranslateArrayLength(traversal)); } // Predicate-less Any - translate to a simple length check. if (method.IsClosedFormOf(_enumerableAnyWithoutPredicate) && arguments.Count == 1 && arguments[0].Type.TryGetElementType(out _) && arguments[0].TypeMapping is MySqlJsonTypeMapping) { return(_sqlExpressionFactory.GreaterThan( _jsonPocoTranslator.TranslateArrayLength(arguments[0]), _sqlExpressionFactory.Constant(0))); } return(null); }