private SqlExpression GetFirstArgumentAsInt64(IReadOnlyList <SqlExpression> arguments, long multiplier) { SqlExpression value = _sqlExpressionFactory.ApplyDefaultTypeMapping(arguments[0]); if (value.TypeMapping != null && value.TypeMapping.StoreTypeNameBase == "FLOAT64") { value = _sqlExpressionFactory.ApplyDefaultTypeMapping(_sqlExpressionFactory.Function("CAST", new[] { value, _sqlExpressionFactory.Fragment("INT64") }, typeof(long))); } if (multiplier != 1L) { value = _sqlExpressionFactory.Multiply(_sqlExpressionFactory.Constant(multiplier), value, value.TypeMapping); } return(value); }
public virtual SqlExpression Translate(SqlExpression instance, MemberInfo member, Type returnType) { Check.NotNull(member, nameof(member)); Check.NotNull(returnType, nameof(returnType)); if (member.DeclaringType == typeof(DateTime)) { var memberName = member.Name; if (_datePartMapping.TryGetValue(memberName, out var datePart)) { return(_sqlExpressionFactory.Convert( SqliteExpression.Strftime( _sqlExpressionFactory, typeof(string), datePart, instance), returnType)); } if (string.Equals(memberName, nameof(DateTime.Ticks))) { return(_sqlExpressionFactory.Convert( _sqlExpressionFactory.Multiply( _sqlExpressionFactory.Subtract( _sqlExpressionFactory.Function( "julianday", new[] { instance }, nullable: true, argumentsPropagateNullability: new[] { true }, typeof(double)), _sqlExpressionFactory.Constant(1721425.5)), // NB: Result of julianday('0001-01-01 00:00:00') _sqlExpressionFactory.Constant(TimeSpan.TicksPerDay)), typeof(long))); } if (string.Equals(memberName, nameof(DateTime.Millisecond))) { return(_sqlExpressionFactory.Modulo( _sqlExpressionFactory.Multiply( _sqlExpressionFactory.Convert( SqliteExpression.Strftime( _sqlExpressionFactory, typeof(string), "%f", instance), typeof(double)), _sqlExpressionFactory.Constant(1000)), _sqlExpressionFactory.Constant(1000))); } var format = "%Y-%m-%d %H:%M:%f"; SqlExpression timestring; var modifiers = new List <SqlExpression>(); switch (memberName) { case nameof(DateTime.Now): timestring = _sqlExpressionFactory.Constant("now"); modifiers.Add(_sqlExpressionFactory.Constant("localtime")); break; case nameof(DateTime.UtcNow): timestring = _sqlExpressionFactory.Constant("now"); break; case nameof(DateTime.Date): timestring = instance; modifiers.Add(_sqlExpressionFactory.Constant("start of day")); break; case nameof(DateTime.Today): timestring = _sqlExpressionFactory.Constant("now"); modifiers.Add(_sqlExpressionFactory.Constant("localtime")); modifiers.Add(_sqlExpressionFactory.Constant("start of day")); break; case nameof(DateTime.TimeOfDay): format = "%H:%M:%f"; timestring = instance; break; default: return(null); } Check.DebugAssert(timestring != null, "timestring is null"); return(_sqlExpressionFactory.Function( "rtrim", new SqlExpression[] { _sqlExpressionFactory.Function( "rtrim", new SqlExpression[] { SqliteExpression.Strftime( _sqlExpressionFactory, returnType, format, timestring, modifiers), _sqlExpressionFactory.Constant("0") }, nullable: true, argumentsPropagateNullability: new[] { true, false }, returnType), _sqlExpressionFactory.Constant(".") }, nullable: true, argumentsPropagateNullability: new[] { true, false }, returnType)); } return(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)))); }