// ?a == ?b -> [(a == b) && (a != null && b != null)] || (a == null && b == null)) // // a | b | F1 = a == b | F2 = (a != null && b != null) | F3 = F1 && F2 | // | | | | | // 0 | 0 | 1 | 1 | 1 | // 0 | 1 | 0 | 1 | 0 | // 0 | N | N | 0 | 0 | // 1 | 0 | 0 | 1 | 0 | // 1 | 1 | 1 | 1 | 1 | // 1 | N | N | 0 | 0 | // N | 0 | N | 0 | 0 | // N | 1 | N | 0 | 0 | // N | N | N | 0 | 0 | // // a | b | F4 = (a == null && b == null) | Final = F3 OR F4 | // | | | | // 0 | 0 | 0 | 1 OR 0 = 1 | // 0 | 1 | 0 | 0 OR 0 = 0 | // 0 | N | 0 | 0 OR 0 = 0 | // 1 | 0 | 0 | 0 OR 0 = 0 | // 1 | 1 | 0 | 1 OR 0 = 1 | // 1 | N | 0 | 0 OR 0 = 0 | // N | 0 | 0 | 0 OR 0 = 0 | // N | 1 | 0 | 0 OR 0 = 0 | // N | N | 1 | 0 OR 1 = 1 | private SqlBinaryExpression ExpandNullableEqualNullable( SqlExpression left, SqlExpression right, SqlExpression leftIsNull, SqlExpression rightIsNull) => _sqlExpressionFactory.OrElse( _sqlExpressionFactory.AndAlso( _sqlExpressionFactory.Equal(left, right), _sqlExpressionFactory.AndAlso( _sqlExpressionFactory.Not(leftIsNull), _sqlExpressionFactory.Not(rightIsNull))), _sqlExpressionFactory.AndAlso( leftIsNull, rightIsNull));
private SqlExpression TranslateStartsEndsWith(SqlExpression instance, SqlExpression pattern, bool startsWith) { var stringTypeMapping = ExpressionExtensions.InferTypeMapping(instance, pattern); instance = _sqlExpressionFactory.ApplyTypeMapping(instance, stringTypeMapping); pattern = _sqlExpressionFactory.ApplyTypeMapping(pattern, stringTypeMapping); if (pattern is SqlConstantExpression constantExpression) { // The pattern is constant. Aside from null or empty, we escape all special characters (%, _, \) // in C# and send a simple LIKE if (!(constantExpression.Value is string constantString)) { return(_sqlExpressionFactory.Like( instance, _sqlExpressionFactory.Constant(null, stringTypeMapping))); } return(constantString.Any(c => IsLikeWildChar(c)) ? _sqlExpressionFactory.Like( instance, _sqlExpressionFactory.Constant( startsWith ? EscapeLikePattern(constantString) + '%' : '%' + EscapeLikePattern(constantString)), _sqlExpressionFactory.Constant( LikeEscapeChar.ToString())) // SQL Server has no char mapping, avoid value conversion warning) : _sqlExpressionFactory.Like( instance, _sqlExpressionFactory.Constant(startsWith ? constantString + '%' : '%' + constantString))); } // The pattern is non-constant, we use LEFT or RIGHT to extract substring and compare. // For StartsWith we also first run a LIKE to quickly filter out most non-matching results (sargable, but imprecise // because of wildchars). if (startsWith) { return(_sqlExpressionFactory.AndAlso( _sqlExpressionFactory.Like( instance, _sqlExpressionFactory.Add( instance, _sqlExpressionFactory.Constant("%"))), _sqlExpressionFactory.Equal( _sqlExpressionFactory.Function( "LEFT", new[] { instance, _sqlExpressionFactory.Function("LEN", new[] { pattern }, typeof(int)) }, typeof(string), stringTypeMapping), pattern))); } return(_sqlExpressionFactory.Equal( _sqlExpressionFactory.Function( "RIGHT", new[] { instance, _sqlExpressionFactory.Function("LEN", new[] { pattern }, typeof(int)) }, typeof(string), stringTypeMapping), pattern)); }
private SqlBinaryExpression CreateJoinPredicate( ShapedQueryExpression outer, LambdaExpression outerKeySelector, ShapedQueryExpression inner, LambdaExpression innerKeySelector) { var outerKey = RemapLambdaBody(outer.ShaperExpression, outerKeySelector); var innerKey = RemapLambdaBody(inner.ShaperExpression, innerKeySelector); if (outerKey is NewExpression outerNew) { var innerNew = (NewExpression)innerKey; SqlBinaryExpression result = null; for (var i = 0; i < outerNew.Arguments.Count; i++) { result = result == null ? CreateJoinPredicate(outerNew.Arguments[i], innerNew.Arguments[i]) : _sqlExpressionFactory.AndAlso( result, CreateJoinPredicate(outerNew.Arguments[i], innerNew.Arguments[i])); } return(result); } return(CreateJoinPredicate(outerKey, innerKey)); }
SqlExpression TranslateStartsEndsWith(SqlExpression instance, SqlExpression pattern, bool startsWith) { var stringTypeMapping = ExpressionExtensions.InferTypeMapping(instance, pattern); instance = _sqlExpressionFactory.ApplyTypeMapping(instance, stringTypeMapping); pattern = _sqlExpressionFactory.ApplyTypeMapping(pattern, stringTypeMapping); if (pattern is SqlConstantExpression constantExpression) { // The pattern is constant. Aside from null, we escape all special characters (%, _, \) // in C# and send a simple LIKE return(constantExpression.Value is string constantPattern ? _sqlExpressionFactory.Like( instance, _sqlExpressionFactory.Constant( startsWith ? EscapeLikePattern(constantPattern) + '%' : '%' + EscapeLikePattern(constantPattern))) : _sqlExpressionFactory.Like(instance, _sqlExpressionFactory.Constant(null, stringTypeMapping))); } // The pattern is non-constant, we use LEFT or RIGHT to extract substring and compare. // For StartsWith we also first run a LIKE to quickly filter out most non-matching results (sargable, but imprecise // because of wildchars). SqlExpression leftRight = _sqlExpressionFactory.Function( startsWith ? "LEFT" : "RIGHT", new[] { instance, _sqlExpressionFactory.Function("LENGTH", new[] { pattern }, typeof(int)) }, typeof(string), stringTypeMapping); // LEFT/RIGHT of a citext return a text, so for non-default text mappings we apply an explicit cast. if (instance.TypeMapping != _textTypeMapping) { leftRight = _sqlExpressionFactory.Convert(leftRight, typeof(string), instance.TypeMapping); } // Also add an explicit cast on the pattern; this is only required because of // The following is only needed because of https://github.com/aspnet/EntityFrameworkCore/issues/19120 var castPattern = pattern.TypeMapping == _textTypeMapping ? pattern : _sqlExpressionFactory.Convert(pattern, typeof(string), pattern.TypeMapping); return(startsWith ? _sqlExpressionFactory.AndAlso( _sqlExpressionFactory.Like( instance, _sqlExpressionFactory.Add( pattern, _sqlExpressionFactory.Constant("%"))), _sqlExpressionFactory.Equal(leftRight, castPattern)) : _sqlExpressionFactory.Equal(leftRight, castPattern)); }
public virtual SqlExpression Translate(SqlExpression instance, MemberInfo member, Type returnType) { if (_memberToFunctionName.TryGetValue(member, out var functionName)) { Debug.Assert(instance.TypeMapping != null, "Instance must have typeMapping assigned."); var storeType = instance.TypeMapping.StoreType; var resultTypeMapping = typeof(Geometry).IsAssignableFrom(returnType) ? _typeMappingSource.FindMapping(returnType, storeType) : _typeMappingSource.FindMapping(returnType); // Emulate ST_IsRing if not supported. var sqlExpression = functionName != "ST_IsRing" || _options.ServerVersion.SupportsSpatialIsRingFunction ? (SqlExpression)_sqlExpressionFactory.Function( functionName, new[] { instance }, returnType, resultTypeMapping) : _sqlExpressionFactory.AndAlso( _sqlExpressionFactory.Function( "ST_IsClosed", new[] { instance }, returnType, resultTypeMapping), _sqlExpressionFactory.Function( "ST_IsSimple", new[] { instance }, returnType, resultTypeMapping) ); // ST_IsRing and others returns TRUE for a NULL value in MariaDB, which is inconsistent with NTS' implementation. // We return the following instead: // CASE // WHEN instance IS NULL THEN NULL // ELSE expression // END if (returnType == typeof(bool)) { sqlExpression = _sqlExpressionFactory.Case( new[] { new CaseWhenClause( _sqlExpressionFactory.IsNull(instance), _sqlExpressionFactory.Constant(null, RelationalTypeMapping.NullMapping)) }, sqlExpression); } return(sqlExpression); } return(null); }
private SqlBinaryExpression CreateJoinPredicate( IList <Expression> outerExpressions, IList <Expression> innerExpressions) { SqlBinaryExpression result = null; for (var i = 0; i < outerExpressions.Count; i++) { result = result == null ? CreateJoinPredicate(outerExpressions[i], innerExpressions[i]) : _sqlExpressionFactory.AndAlso( result, CreateJoinPredicate(outerExpressions[i], innerExpressions[i])); } return(result); }
/// <inheritdoc/> 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 (_methodInfoDatePartMapping.TryGetValue(method, out var value)) { var start = _sqlExpressionFactory.GreaterThanOrEqual(arguments[0], arguments[1]); var end = _sqlExpressionFactory.LessThanOrEqual(arguments[0], arguments[2]); return(_sqlExpressionFactory.AndAlso(start, end)); } return(null); }
public override Expression Visit(Expression expression) { if (expression is InExpression inExpression && inExpression.Values != null) { var inValues = new List <object>(); var hasNullValue = false; RelationalTypeMapping typeMapping = null; switch (inExpression.Values) { case SqlConstantExpression sqlConstant: { typeMapping = sqlConstant.TypeMapping; var values = (IEnumerable)sqlConstant.Value; foreach (var value in values) { if (value == null) { hasNullValue = true; continue; } inValues.Add(value); } } break; case SqlParameterExpression sqlParameter: { typeMapping = sqlParameter.TypeMapping; var values = (IEnumerable)_parametersValues[sqlParameter.Name]; foreach (var value in values) { if (value == null) { hasNullValue = true; continue; } inValues.Add(value); } } break; } var updatedInExpression = inValues.Count > 0 ? _sqlExpressionFactory.In( (SqlExpression)Visit(inExpression.Item), _sqlExpressionFactory.Constant(inValues, typeMapping), inExpression.Negated) : null; var nullCheckExpression = hasNullValue ? inExpression.Negated ? _sqlExpressionFactory.IsNotNull(inExpression.Item) : _sqlExpressionFactory.IsNull(inExpression.Item) : null; if (updatedInExpression != null && nullCheckExpression != null) { return(inExpression.Negated ? _sqlExpressionFactory.AndAlso(updatedInExpression, nullCheckExpression) : _sqlExpressionFactory.OrElse(updatedInExpression, nullCheckExpression)); } if (updatedInExpression == null && nullCheckExpression == null) { return(_sqlExpressionFactory.Equal(_sqlExpressionFactory.Constant(true), _sqlExpressionFactory.Constant(inExpression.Negated))); } return((SqlExpression)updatedInExpression ?? nullCheckExpression); } return(base.Visit(expression)); }
/// <summary> /// This is an internal API that supports the Entity Framework Core infrastructure and not subject to /// the same compatibility standards as public APIs. It may be changed or removed without notice in /// any release. You should only use it directly in your code with extreme caution and knowing that /// doing so can result in application failures when updating to a new Entity Framework Core release. /// </summary> public virtual SqlExpression?Translate( SqlExpression?instance, MethodInfo method, IReadOnlyList <SqlExpression> arguments, IDiagnosticsLogger <DbLoggerCategory.Query> logger) { if (instance != null) { if (MethodToFunctionName.TryGetValue(method, out var functionName)) { var finalArguments = new[] { instance }.Concat(arguments); if (method.ReturnType == typeof(bool)) { var nullCheck = (SqlExpression)_sqlExpressionFactory.IsNotNull(instance); foreach (var argument in arguments) { nullCheck = _sqlExpressionFactory.AndAlso( nullCheck, _sqlExpressionFactory.IsNotNull(argument)); } return(_sqlExpressionFactory.Case( new[] { new CaseWhenClause( nullCheck, _sqlExpressionFactory.Function( functionName, finalArguments, nullable: false, finalArguments.Select(a => false), method.ReturnType)) }, null)); } return(_sqlExpressionFactory.Function( functionName, finalArguments, nullable: true, finalArguments.Select(a => true), method.ReturnType)); } if (Equals(method, GetGeometryN)) { return(_sqlExpressionFactory.Function( "GeometryN", new[] { instance, _sqlExpressionFactory.Add( arguments[0], _sqlExpressionFactory.Constant(1)) }, nullable: true, argumentsPropagateNullability: new[] { true, true }, method.ReturnType)); } if (Equals(method, IsWithinDistance)) { return(_sqlExpressionFactory.LessThanOrEqual( _sqlExpressionFactory.Function( "Distance", new[] { instance, arguments[0] }, nullable: true, argumentsPropagateNullability: new[] { true, true }, typeof(double)), arguments[1])); } } return(null); }
private Expression VisitSqlUnaryExpression(SqlUnaryExpression sqlUnaryExpression) { // !(true) -> false // !(false) -> true if (sqlUnaryExpression.OperatorType == ExpressionType.Not && sqlUnaryExpression.Operand is SqlConstantExpression innerConstantBool && innerConstantBool.Value is bool value) { return(value ? _sqlExpressionFactory.Constant(false, sqlUnaryExpression.TypeMapping) : _sqlExpressionFactory.Constant(true, sqlUnaryExpression.TypeMapping)); } // NULL IS NULL -> true // non_nullablee_constant IS NULL -> false if (sqlUnaryExpression.OperatorType == ExpressionType.Equal && sqlUnaryExpression.Operand is SqlConstantExpression innerConstantNull1) { return(_sqlExpressionFactory.Constant(innerConstantNull1.Value == null, sqlUnaryExpression.TypeMapping)); } // NULL IS NOT NULL -> false // non_nullablee_constant IS NOT NULL -> true if (sqlUnaryExpression.OperatorType == ExpressionType.NotEqual && sqlUnaryExpression.Operand is SqlConstantExpression innerConstantNull2) { return(_sqlExpressionFactory.Constant(innerConstantNull2.Value != null, sqlUnaryExpression.TypeMapping)); } if (sqlUnaryExpression.Operand is SqlUnaryExpression innerUnary) { if (sqlUnaryExpression.OperatorType == ExpressionType.Not) { // !(!a) -> a if (innerUnary.OperatorType == ExpressionType.Not) { return(Visit(innerUnary.Operand)); } if (innerUnary.OperatorType == ExpressionType.Equal) { //!(a IS NULL) -> a IS NOT NULL return(Visit(_sqlExpressionFactory.IsNotNull(innerUnary.Operand))); } //!(a IS NOT NULL) -> a IS NULL if (innerUnary.OperatorType == ExpressionType.NotEqual) { return(Visit(_sqlExpressionFactory.IsNull(innerUnary.Operand))); } } // (!a) IS NULL <==> a IS NULL if (sqlUnaryExpression.OperatorType == ExpressionType.Equal && innerUnary.OperatorType == ExpressionType.Not) { return(Visit(_sqlExpressionFactory.IsNull(innerUnary.Operand))); } // (!a) IS NOT NULL <==> a IS NOT NULL if (sqlUnaryExpression.OperatorType == ExpressionType.NotEqual && innerUnary.OperatorType == ExpressionType.Not) { return(Visit(_sqlExpressionFactory.IsNotNull(innerUnary.Operand))); } } if (sqlUnaryExpression.Operand is SqlBinaryExpression innerBinary) { // De Morgan's if (innerBinary.OperatorType == ExpressionType.AndAlso || innerBinary.OperatorType == ExpressionType.OrElse) { var newLeft = (SqlExpression)Visit(_sqlExpressionFactory.Not(innerBinary.Left)); var newRight = (SqlExpression)Visit(_sqlExpressionFactory.Not(innerBinary.Right)); return(innerBinary.OperatorType == ExpressionType.AndAlso ? _sqlExpressionFactory.OrElse(newLeft, newRight) : _sqlExpressionFactory.AndAlso(newLeft, newRight)); } // note that those optimizations are only valid in 2-value logic // they are safe to do here because null semantics removes possibility of nulls in the tree // however if we decide to do "partial" null semantics (that doesn't distinguish between NULL and FALSE, e.g. for predicates) // we need to be extra careful here if (TryNegate(innerBinary.OperatorType, out var negated)) { return(Visit( _sqlExpressionFactory.MakeBinary( negated, innerBinary.Left, innerBinary.Right, innerBinary.TypeMapping))); } } var newOperand = (SqlExpression)Visit(sqlUnaryExpression.Operand); return(sqlUnaryExpression.Update(newOperand)); }
protected override Expression VisitSqlBinary(SqlBinaryExpression sqlBinaryExpression) { Check.NotNull(sqlBinaryExpression, nameof(sqlBinaryExpression)); _isNullable = false; var canOptimize = _canOptimize; // for SqlServer we could also allow optimize on children of ExpressionType.Equal // because they get converted to CASE blocks anyway, but for other providers it's incorrect // once/if null semantics optimizations are provider-specific we can enable it _canOptimize = _canOptimize && (sqlBinaryExpression.OperatorType == ExpressionType.AndAlso || sqlBinaryExpression.OperatorType == ExpressionType.OrElse); var nonNullableColumns = new List <ColumnExpression>(); if (sqlBinaryExpression.OperatorType == ExpressionType.AndAlso) { nonNullableColumns = FindNonNullableColumns(sqlBinaryExpression.Left); } var newLeft = (SqlExpression)Visit(sqlBinaryExpression.Left); var leftNullable = _isNullable; _isNullable = false; if (nonNullableColumns.Count > 0) { _nonNullableColumns.AddRange(nonNullableColumns); } var newRight = (SqlExpression)Visit(sqlBinaryExpression.Right); var rightNullable = _isNullable; foreach (var nonNullableColumn in nonNullableColumns) { _nonNullableColumns.Remove(nonNullableColumn); } if (sqlBinaryExpression.OperatorType == ExpressionType.Coalesce) { _isNullable = leftNullable && rightNullable; _canOptimize = canOptimize; return(sqlBinaryExpression.Update(newLeft, newRight)); } if (sqlBinaryExpression.OperatorType == ExpressionType.Add && sqlBinaryExpression.Type == typeof(string)) { if (leftNullable) { newLeft = newLeft is SqlConstantExpression ? _sqlExpressionFactory.Constant(string.Empty) : newLeft is ColumnExpression || newLeft is SqlParameterExpression ? _sqlExpressionFactory.Coalesce(newLeft, _sqlExpressionFactory.Constant(string.Empty)) : newLeft; } if (rightNullable) { newRight = newRight is SqlConstantExpression ? _sqlExpressionFactory.Constant(string.Empty) : newRight is ColumnExpression || newRight is SqlParameterExpression ? _sqlExpressionFactory.Coalesce(newRight, _sqlExpressionFactory.Constant(string.Empty)) : newRight; } return(sqlBinaryExpression.Update(newLeft, newRight)); } if (sqlBinaryExpression.OperatorType == ExpressionType.Equal || sqlBinaryExpression.OperatorType == ExpressionType.NotEqual) { var leftConstantNull = newLeft is SqlConstantExpression leftConstant && leftConstant.Value == null; var rightConstantNull = newRight is SqlConstantExpression rightConstant && rightConstant.Value == null; // a == null -> a IS NULL // a != null -> a IS NOT NULL if (rightConstantNull) { _isNullable = false; _canOptimize = canOptimize; return(sqlBinaryExpression.OperatorType == ExpressionType.Equal ? _sqlExpressionFactory.IsNull(newLeft) : _sqlExpressionFactory.IsNotNull(newLeft)); } // null == a -> a IS NULL // null != a -> a IS NOT NULL if (leftConstantNull) { _isNullable = false; _canOptimize = canOptimize; return(sqlBinaryExpression.OperatorType == ExpressionType.Equal ? _sqlExpressionFactory.IsNull(newRight) : _sqlExpressionFactory.IsNotNull(newRight)); } var leftUnary = newLeft as SqlUnaryExpression; var rightUnary = newRight as SqlUnaryExpression; var leftNegated = leftUnary?.IsLogicalNot() == true; var rightNegated = rightUnary?.IsLogicalNot() == true; if (leftNegated) { newLeft = leftUnary.Operand; } if (rightNegated) { newRight = rightUnary.Operand; } var leftIsNull = _sqlExpressionFactory.IsNull(newLeft); var rightIsNull = _sqlExpressionFactory.IsNull(newRight); // optimized expansion which doesn't distinguish between null and false if (canOptimize && sqlBinaryExpression.OperatorType == ExpressionType.Equal && !leftNegated && !rightNegated) { // when we use optimized form, the result can still be nullable if (leftNullable && rightNullable) { _isNullable = true; _canOptimize = canOptimize; return(_sqlExpressionFactory.OrElse( _sqlExpressionFactory.Equal(newLeft, newRight), _sqlExpressionFactory.AndAlso(leftIsNull, rightIsNull))); } if ((leftNullable && !rightNullable) || (!leftNullable && rightNullable)) { _isNullable = true; _canOptimize = canOptimize; return(_sqlExpressionFactory.Equal(newLeft, newRight)); } } // doing a full null semantics rewrite - removing all nulls from truth table // this will NOT be correct once we introduce simplified null semantics _isNullable = false; _canOptimize = canOptimize; if (sqlBinaryExpression.OperatorType == ExpressionType.Equal) { if (!leftNullable && !rightNullable) { // a == b <=> !a == !b -> a == b // !a == b <=> a == !b -> a != b return(leftNegated == rightNegated ? _sqlExpressionFactory.Equal(newLeft, newRight) : _sqlExpressionFactory.NotEqual(newLeft, newRight)); } if (leftNullable && rightNullable) { // ?a == ?b <=> !(?a) == !(?b) -> [(a == b) && (a != null && b != null)] || (a == null && b == null)) // !(?a) == ?b <=> ?a == !(?b) -> [(a != b) && (a != null && b != null)] || (a == null && b == null) return(leftNegated == rightNegated ? ExpandNullableEqualNullable(newLeft, newRight, leftIsNull, rightIsNull) : ExpandNegatedNullableEqualNullable(newLeft, newRight, leftIsNull, rightIsNull)); } if (leftNullable && !rightNullable) { // ?a == b <=> !(?a) == !b -> (a == b) && (a != null) // !(?a) == b <=> ?a == !b -> (a != b) && (a != null) return(leftNegated == rightNegated ? ExpandNullableEqualNonNullable(newLeft, newRight, leftIsNull) : ExpandNegatedNullableEqualNonNullable(newLeft, newRight, leftIsNull)); } if (rightNullable && !leftNullable) { // a == ?b <=> !a == !(?b) -> (a == b) && (b != null) // !a == ?b <=> a == !(?b) -> (a != b) && (b != null) return(leftNegated == rightNegated ? ExpandNullableEqualNonNullable(newLeft, newRight, rightIsNull) : ExpandNegatedNullableEqualNonNullable(newLeft, newRight, rightIsNull)); } } if (sqlBinaryExpression.OperatorType == ExpressionType.NotEqual) { if (!leftNullable && !rightNullable) { // a != b <=> !a != !b -> a != b // !a != b <=> a != !b -> a == b return(leftNegated == rightNegated ? _sqlExpressionFactory.NotEqual(newLeft, newRight) : _sqlExpressionFactory.Equal(newLeft, newRight)); } if (leftNullable && rightNullable) { // ?a != ?b <=> !(?a) != !(?b) -> [(a != b) || (a == null || b == null)] && (a != null || b != null) // !(?a) != ?b <=> ?a != !(?b) -> [(a == b) || (a == null || b == null)] && (a != null || b != null) return(leftNegated == rightNegated ? ExpandNullableNotEqualNullable(newLeft, newRight, leftIsNull, rightIsNull) : ExpandNegatedNullableNotEqualNullable(newLeft, newRight, leftIsNull, rightIsNull)); } if (leftNullable) { // ?a != b <=> !(?a) != !b -> (a != b) || (a == null) // !(?a) != b <=> ?a != !b -> (a == b) || (a == null) return(leftNegated == rightNegated ? ExpandNullableNotEqualNonNullable(newLeft, newRight, leftIsNull) : ExpandNegatedNullableNotEqualNonNullable(newLeft, newRight, leftIsNull)); } if (rightNullable) { // a != ?b <=> !a != !(?b) -> (a != b) || (b == null) // !a != ?b <=> a != !(?b) -> (a == b) || (b == null) return(leftNegated == rightNegated ? ExpandNullableNotEqualNonNullable(newLeft, newRight, rightIsNull) : ExpandNegatedNullableNotEqualNonNullable(newLeft, newRight, rightIsNull)); } } } _isNullable = leftNullable || rightNullable; _canOptimize = canOptimize; return(sqlBinaryExpression.Update(newLeft, newRight)); }
public virtual SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList <SqlExpression> arguments) { Check.NotNull(method, nameof(method)); Check.NotNull(arguments, nameof(arguments)); if (_methodToFunctionName.TryGetValue(method, out var functionName)) { var finalArguments = new[] { instance }.Concat(arguments); if (method.ReturnType == typeof(bool)) { var nullCheck = (SqlExpression)_sqlExpressionFactory.IsNotNull(instance); foreach (var argument in arguments) { nullCheck = _sqlExpressionFactory.AndAlso( nullCheck, _sqlExpressionFactory.IsNotNull(argument)); } return(_sqlExpressionFactory.Case( new[] { new CaseWhenClause( nullCheck, _sqlExpressionFactory.Function( functionName, finalArguments, nullResultAllowed: false, finalArguments.Select(a => false), method.ReturnType)) }, null)); } return(_sqlExpressionFactory.Function( functionName, finalArguments, nullResultAllowed: true, finalArguments.Select(a => true), method.ReturnType)); } if (Equals(method, _getGeometryN)) { return(_sqlExpressionFactory.Function( "GeometryN", new[] { instance, _sqlExpressionFactory.Add( arguments[0], _sqlExpressionFactory.Constant(1)) }, nullResultAllowed: true, argumentsPropagateNullability: new[] { true, true }, method.ReturnType)); } if (Equals(method, _isWithinDistance)) { return(_sqlExpressionFactory.LessThanOrEqual( _sqlExpressionFactory.Function( "Distance", new[] { instance, arguments[0] }, nullResultAllowed: true, argumentsPropagateNullability: new[] { true, true }, typeof(double)), arguments[1])); } 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)); }