public virtual SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList <SqlExpression> arguments)
{
Check.NotNull(method, nameof(method));
Check.NotNull(arguments, nameof(arguments));
SqlExpression modifier = null;
if (_addMilliseconds.Equals(method))
{
modifier = _sqlExpressionFactory.Add(
_sqlExpressionFactory.Convert(
_sqlExpressionFactory.Divide(
arguments[0],
_sqlExpressionFactory.Constant(1000.0)),
typeof(string)),
_sqlExpressionFactory.Constant(" seconds"));
}
else if (_addTicks.Equals(method))
{
modifier = _sqlExpressionFactory.Add(
_sqlExpressionFactory.Convert(
_sqlExpressionFactory.Divide(
arguments[0],
_sqlExpressionFactory.Constant((double)TimeSpan.TicksPerDay)),
typeof(string)),
_sqlExpressionFactory.Constant(" seconds"));
}
else if (_methodInfoToUnitSuffix.TryGetValue(method, out var unitSuffix))
{
modifier = _sqlExpressionFactory.Add(
_sqlExpressionFactory.Convert(arguments[0], typeof(string)),
_sqlExpressionFactory.Constant(unitSuffix));
}
if (modifier != null)
{
return(_sqlExpressionFactory.Function(
"rtrim",
new SqlExpression[]
{
_sqlExpressionFactory.Function(
"rtrim",
new SqlExpression[]
{
SqliteExpression.Strftime(
_sqlExpressionFactory,
method.ReturnType,
"%Y-%m-%d %H:%M:%f",
instance,
new[] { modifier }),
_sqlExpressionFactory.Constant("0")
},
method.ReturnType),
_sqlExpressionFactory.Constant(".")
},
method.ReturnType));
}
return(null);
}
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));
// Issue #15586: Query: TypeCompatibility chart for inference.
return(_methodInfo.Equals(method)
? _sqlExpressionFactory.Function(
"abs",
new SqlExpression[]
{
_sqlExpressionFactory.Divide(
_sqlExpressionFactory.Function(
"random",
Array.Empty <SqlExpression>(),
nullable: false,
argumentsPropagateNullability: Array.Empty <bool>(),
method.ReturnType),
_sqlExpressionFactory.Constant(9223372036854780000.0))
},
nullable: false,
argumentsPropagateNullability: Array.Empty <bool>(),
method.ReturnType)
: null);
}
private static SqlExpression GetAndoyerDistance(
SqlExpression left,
SqlExpression right,
Type resultType,
ISqlExpressionFactory sqlExpressionFactory)
{
SqlExpression toDegrees(SqlExpression coord)
=> sqlExpressionFactory.Divide(
sqlExpressionFactory.Multiply(
coord,
sqlExpressionFactory.Function(
"PI",
Array.Empty <SqlExpression>(),
resultType)),
sqlExpressionFactory.Constant(180.0));
SqlExpression xCoord(SqlExpression point)
=> sqlExpressionFactory.Function(
"ST_X",
new[] { point },
resultType);
SqlExpression yCoord(SqlExpression point)
=> sqlExpressionFactory.Function(
"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.Function(
"POWER",
new SqlExpression[]
{
sqlExpressionFactory.Function(
"SIN",
new[] { g },
resultType),
c2Int
},
resultType);
var cosG2 = sqlExpressionFactory.Function(
"POWER",
new SqlExpression[]
{
sqlExpressionFactory.Function(
"COS",
new[] { g },
resultType),
c2Int
},
resultType);
var sinF2 = sqlExpressionFactory.Function(
"POWER",
new SqlExpression[]
{
sqlExpressionFactory.Function(
"SIN",
new[] { f },
resultType),
c2Int
},
resultType);
var cosF2 = sqlExpressionFactory.Function(
"POWER",
new SqlExpression[]
{
sqlExpressionFactory.Function(
"COS",
new[] { f },
resultType),
c2Int
},
resultType);
var sinL2 = sqlExpressionFactory.Function(
"POWER",
new SqlExpression[]
{
sqlExpressionFactory.Function(
"SIN",
new[] { lambda },
resultType),
c2Int
},
resultType);
var cosL2 = sqlExpressionFactory.Function(
"POWER",
new SqlExpression[]
{
sqlExpressionFactory.Function(
"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.Function(
"ATAN",
new[]
{
sqlExpressionFactory.Function(
"SQRT",
new[] { sqlExpressionFactory.Divide(s, c) },
resultType)
},
resultType);
var r3 = sqlExpressionFactory.Divide(
sqlExpressionFactory.Multiply(
c3,
sqlExpressionFactory.Function(
"SQRT",
new[] { sqlExpressionFactory.Multiply(s, c) },
resultType)),
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));
}
private static SqlExpression GetHaversineDistance(
SqlExpression left,
SqlExpression right,
Type resultType,
ISqlExpressionFactory 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.Function(
"ASIN",
new[]
{
sqlExpressionFactory.Function(
"SQRT",
new[]
{
sqlExpressionFactory.Add(
sqlExpressionFactory.Function(
"POWER",
new SqlExpression[]
{
sqlExpressionFactory.Function(
"SIN",
new[]
{
sqlExpressionFactory.Divide(
sqlExpressionFactory.Divide(
sqlExpressionFactory.Multiply(
sqlExpressionFactory.Subtract(
sqlExpressionFactory.Function(
"ST_Y",
new[] { right },
resultType),
sqlExpressionFactory.Function(
"ST_Y",
new[] { left },
resultType)),
sqlExpressionFactory.Function(
"PI",
Array.Empty <SqlExpression>(),
resultType)),
sqlExpressionFactory.Constant(180.0)),
sqlExpressionFactory.Constant(2.0))
},
resultType),
sqlExpressionFactory.Constant(2),
},
resultType),
sqlExpressionFactory.Multiply(
sqlExpressionFactory.Function(
"COS",
new[]
{
sqlExpressionFactory.Divide(
sqlExpressionFactory.Multiply(
sqlExpressionFactory.Function(
"ST_Y",
new[] { left },
resultType),
sqlExpressionFactory.Function(
"PI",
Array.Empty <SqlExpression>(),
resultType)),
sqlExpressionFactory.Constant(180.0)),
},
resultType),
sqlExpressionFactory.Multiply(
sqlExpressionFactory.Function(
"COS",
new[]
{
sqlExpressionFactory.Divide(
sqlExpressionFactory.Multiply(
sqlExpressionFactory.Function(
"ST_Y",
new[] { right },
resultType),
sqlExpressionFactory.Function(
"PI",
Array.Empty <SqlExpression>(),
resultType)),
sqlExpressionFactory.Constant(180.0)),
},
resultType),
sqlExpressionFactory.Function(
"POWER",
new SqlExpression[]
{
sqlExpressionFactory.Function(
"SIN",
new[]
{
sqlExpressionFactory.Divide(
sqlExpressionFactory.Divide(
sqlExpressionFactory.Multiply(
sqlExpressionFactory.Subtract(
sqlExpressionFactory.Function(
"ST_X",
new[] { right },
resultType),
sqlExpressionFactory.Function(
"ST_X",
new[] { left },
resultType)),
sqlExpressionFactory.Function(
"PI",
Array.Empty <SqlExpression>(),
resultType)),
sqlExpressionFactory.Constant(180.0)),
sqlExpressionFactory.Constant(2.0))
},
resultType),
sqlExpressionFactory.Constant(2),
},
resultType))))
},
resultType)
},
resultType))));
}
