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