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