private SqlExpression OptimizeCompareTo(
SqlBinaryExpression sqlBinaryExpression,
int intValue,
CaseExpression caseExpression)
{
var testLeft = ((SqlBinaryExpression)caseExpression.WhenClauses[0].Test).Left;
var testRight = ((SqlBinaryExpression)caseExpression.WhenClauses[0].Test).Right;
var operatorType = sqlBinaryExpression.Right is SqlConstantExpression
? sqlBinaryExpression.OperatorType
: sqlBinaryExpression.OperatorType switch
{
ExpressionType.GreaterThan => ExpressionType.LessThan,
ExpressionType.GreaterThanOrEqual => ExpressionType.LessThanOrEqual,
ExpressionType.LessThan => ExpressionType.GreaterThan,
ExpressionType.LessThanOrEqual => ExpressionType.GreaterThanOrEqual,
_ => sqlBinaryExpression.OperatorType
};
switch (operatorType)
{
// CompareTo(a, b) != 0 -> a != b
// CompareTo(a, b) != 1 -> a <= b
// CompareTo(a, b) != -1 -> a >= b
case ExpressionType.NotEqual:
return((SqlExpression)Visit(intValue switch
{
0 => _sqlExpressionFactory.NotEqual(testLeft, testRight),
1 => _sqlExpressionFactory.LessThanOrEqual(testLeft, testRight),
_ => _sqlExpressionFactory.GreaterThanOrEqual(testLeft, testRight),
}));
/// <summary>
/// 更新 Select 语句
/// </summary>
/// <param name="selectExpression"></param>
/// <returns></returns>
private Expression VisitSelect(SelectExpression selectExpression)
{
var oldOffset = selectExpression.Offset;
if (oldOffset == null)
{
return(selectExpression);
}
var oldLimit = selectExpression.Limit;
var oldOrderings = selectExpression.Orderings;
// 在子查询中 OrderBy 必须写 Top 数量
var newOrderings = oldOrderings.Count > 0 && (oldLimit != null || selectExpression == root)
? oldOrderings.ToList()
: new List <OrderingExpression>();
// 更新表达式
selectExpression = selectExpression.Update(selectExpression.Projection.ToList(),
selectExpression.Tables.ToList(),
selectExpression.Predicate,
selectExpression.GroupBy.ToList(),
selectExpression.Having,
orderings: newOrderings,
limit: null,
offset: null);
var rowOrderings = oldOrderings.Count != 0 ? oldOrderings
: new[] { new OrderingExpression(new SqlFragmentExpression("(SELECT 1)"), true) };
_ = selectExpression.PushdownIntoSubquery(); // .NET 6 该方法已无返回值
var subQuery = (SelectExpression)selectExpression.Tables[0];
var projection = new RowNumberExpression(Array.Empty <SqlExpression>(), rowOrderings, oldOffset.TypeMapping);
var left = GenerateOuterColumnAccessor(subQuery, projection, "row");
selectExpression.ApplyPredicate(sqlExpressionFactory.GreaterThan(left, oldOffset));
if (oldLimit != null)
{
if (oldOrderings.Count == 0)
{
selectExpression.ApplyPredicate(sqlExpressionFactory.LessThanOrEqual(left, sqlExpressionFactory.Add(oldOffset, oldLimit)));
}
else
{
// 这里不支持子查询的 OrderBy 操作
selectExpression.ApplyLimit(oldLimit);
}
}
return(selectExpression);
}
public SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList <SqlExpression> arguments)
{
if (method == IPAddressParse)
{
return(_sqlExpressionFactory.Convert(arguments[0], typeof(IPAddress), _sqlExpressionFactory.FindMapping(typeof(IPAddress))));
}
if (method == PhysicalAddressParse)
{
return(_sqlExpressionFactory.Convert(arguments[0], typeof(PhysicalAddress), _sqlExpressionFactory.FindMapping(typeof(PhysicalAddress))));
}
if (method.DeclaringType != typeof(NpgsqlNetworkExtensions))
{
return(null);
}
return(method.Name switch
{
nameof(NpgsqlNetworkExtensions.LessThan) => _sqlExpressionFactory.LessThan(arguments[1], arguments[2]),
nameof(NpgsqlNetworkExtensions.LessThanOrEqual) => _sqlExpressionFactory.LessThanOrEqual(arguments[1], arguments[2]),
nameof(NpgsqlNetworkExtensions.GreaterThanOrEqual) => _sqlExpressionFactory.GreaterThanOrEqual(arguments[1], arguments[2]),
nameof(NpgsqlNetworkExtensions.GreaterThan) => _sqlExpressionFactory.GreaterThan(arguments[1], arguments[2]),
nameof(NpgsqlNetworkExtensions.ContainedBy) => BoolReturningOnTwoNetworkTypes("<<"),
nameof(NpgsqlNetworkExtensions.ContainedByOrEqual) => BoolReturningOnTwoNetworkTypes("<<="),
nameof(NpgsqlNetworkExtensions.Contains) => BoolReturningOnTwoNetworkTypes(">>"),
nameof(NpgsqlNetworkExtensions.ContainsOrEqual) => BoolReturningOnTwoNetworkTypes(">>="),
nameof(NpgsqlNetworkExtensions.ContainsOrContainedBy) => BoolReturningOnTwoNetworkTypes("&&"),
// TODO: Hack, see #1118
nameof(NpgsqlNetworkExtensions.BitwiseNot) => new SqlUnaryExpression(ExpressionType.Negate,
arguments[1],
arguments[1].Type,
arguments[1].TypeMapping),
nameof(NpgsqlNetworkExtensions.BitwiseAnd) => _sqlExpressionFactory.And(arguments[1], arguments[2]),
nameof(NpgsqlNetworkExtensions.BitwiseOr) => _sqlExpressionFactory.Or(arguments[1], arguments[2]),
// Add/Subtract accept inet + int, so we can't use the default type mapping inference logic which assumes
// same-typed operands
nameof(NpgsqlNetworkExtensions.Add)
=> new SqlBinaryExpression(
ExpressionType.Add,
_sqlExpressionFactory.ApplyDefaultTypeMapping(arguments[1]),
_sqlExpressionFactory.ApplyDefaultTypeMapping(arguments[2]),
arguments[1].Type,
arguments[1].TypeMapping),
nameof(NpgsqlNetworkExtensions.Subtract) when arguments[2].Type == typeof(int)
private Expression VisitSelect(SelectExpression selectExpression)
{
var oldOffset = selectExpression.Offset;
if (oldOffset == null)
{
return(selectExpression);
}
var oldLimit = selectExpression.Limit;
var oldOrderings = selectExpression.Orderings;
//order by in subQuery without TOP N is invalid.
var newOrderings = oldOrderings.Count > 0 && (oldLimit != null || selectExpression == root)
? oldOrderings.ToList()
: new List <OrderingExpression>();
selectExpression = selectExpression.Update(selectExpression.Projection.ToList(),
selectExpression.Tables.ToList(),
selectExpression.Predicate,
selectExpression.GroupBy.ToList(),
selectExpression.Having,
orderings: newOrderings,
limit: null,
offset: null,
selectExpression.IsDistinct,
selectExpression.Alias);
var rowOrderings = oldOrderings.Count != 0 ? oldOrderings
: new[] { new OrderingExpression(new SqlFragmentExpression("(SELECT 1)"), true) };
_ = selectExpression.PushdownIntoSubquery();
var subQuery = (SelectExpression)selectExpression.Tables[0];
var projection = new RowNumberExpression(Array.Empty <SqlExpression>(), rowOrderings, oldOffset.TypeMapping);
var left = GenerateOuterColumnAccessor(subQuery, projection, "row");
selectExpression.ApplyPredicate(sqlExpressionFactory.GreaterThan(left, oldOffset));
if (oldLimit != null)
{
if (oldOrderings.Count == 0)
{
selectExpression.ApplyPredicate(sqlExpressionFactory.LessThanOrEqual(left, sqlExpressionFactory.Add(oldOffset, oldLimit)));
}
else
{
//the above one not working when used as subQuery with orderBy
selectExpression.ApplyLimit(oldLimit);
}
}
return(selectExpression);
}
public SqlExpression Translate(SqlExpression instance, MethodInfo method, IList <SqlExpression> arguments)
{
method = method.OnInterface(typeof(IGeometry));
if (_methodToFunctionName.TryGetValue(method, out var functionName))
{
SqlExpression translation = _sqlExpressionFactory.Function(
functionName,
new[] { instance }.Concat(arguments),
method.ReturnType);
if (method.ReturnType == typeof(bool))
{
translation = _sqlExpressionFactory.Case(
new[]
{
new CaseWhenClause(_sqlExpressionFactory.IsNotNull(instance), translation)
},
null);
}
return(translation);
}
if (Equals(method, _getGeometryN))
{
return(_sqlExpressionFactory.Function(
"GeometryN",
new[] {
instance,
_sqlExpressionFactory.Add(
arguments[0],
_sqlExpressionFactory.Constant(1))
},
method.ReturnType));
}
if (Equals(method, _isWithinDistance))
{
return(_sqlExpressionFactory.LessThanOrEqual(
_sqlExpressionFactory.Function(
"Distance",
new[] { instance, arguments[0] },
typeof(double)),
arguments[1]));
}
return(null);
}
/// <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 virtual SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList <SqlExpression> arguments)
{
if (typeof(Geometry).IsAssignableFrom(method.DeclaringType))
{
var geometryExpressions = new[] { instance }.Concat(
arguments.Where(e => typeof(Geometry).IsAssignableFrom(e.Type)));
var typeMapping = ExpressionExtensions.InferTypeMapping(geometryExpressions.ToArray());
Debug.Assert(typeMapping != null, "At least one argument must have typeMapping.");
var storeType = typeMapping.StoreType;
var isGeography = string.Equals(storeType, "geography", StringComparison.OrdinalIgnoreCase);
if (_methodToFunctionName.TryGetValue(method, out var functionName) ||
(!isGeography && _geometryMethodToFunctionName.TryGetValue(method, out functionName)))
{
instance = _sqlExpressionFactory.ApplyTypeMapping(
instance, _typeMappingSource.FindMapping(instance.Type, storeType));
var typeMappedArguments = new List <SqlExpression>();
foreach (var argument in arguments)
{
typeMappedArguments.Add(
_sqlExpressionFactory.ApplyTypeMapping(
argument,
typeof(Geometry).IsAssignableFrom(argument.Type)
? _typeMappingSource.FindMapping(argument.Type, storeType)
: _typeMappingSource.FindMapping(argument.Type)));
}
var resultTypeMapping = typeof(Geometry).IsAssignableFrom(method.ReturnType)
? _typeMappingSource.FindMapping(method.ReturnType, storeType)
: _typeMappingSource.FindMapping(method.ReturnType);
return(_sqlExpressionFactory.Function(
instance,
functionName,
Simplify(typeMappedArguments, isGeography),
method.ReturnType,
resultTypeMapping));
}
if (Equals(method, _getGeometryN))
{
return(_sqlExpressionFactory.Function(
instance,
"STGeometryN",
new[] {
_sqlExpressionFactory.Add(
arguments[0],
_sqlExpressionFactory.Constant(1))
},
method.ReturnType,
_typeMappingSource.FindMapping(method.ReturnType, storeType)));
}
if (Equals(method, _isWithinDistance))
{
instance = _sqlExpressionFactory.ApplyTypeMapping(
instance, _typeMappingSource.FindMapping(instance.Type, storeType));
var typeMappedArguments = new List <SqlExpression>();
foreach (var argument in arguments)
{
typeMappedArguments.Add(
_sqlExpressionFactory.ApplyTypeMapping(
argument,
typeof(Geometry).IsAssignableFrom(argument.Type)
? _typeMappingSource.FindMapping(argument.Type, storeType)
: _typeMappingSource.FindMapping(argument.Type)));
}
return(_sqlExpressionFactory.LessThanOrEqual(
_sqlExpressionFactory.Function(
instance,
"STDistance",
Simplify(new[] { typeMappedArguments[0] }, isGeography),
typeof(double)),
typeMappedArguments[1]));
}
}
return(null);
}
/// <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)
{
Check.NotNull(method, nameof(method));
Check.NotNull(arguments, nameof(arguments));
Check.NotNull(logger, nameof(logger));
if (typeof(Geometry).IsAssignableFrom(method.DeclaringType) &&
instance != null)
{
var geometryExpressions = new[] { instance }.Concat(
arguments.Where(e => typeof(Geometry).IsAssignableFrom(e.Type)));
var typeMapping = ExpressionExtensions.InferTypeMapping(geometryExpressions.ToArray());
Check.DebugAssert(typeMapping != null, "At least one argument must have typeMapping.");
var storeType = typeMapping.StoreType;
var isGeography = string.Equals(storeType, "geography", StringComparison.OrdinalIgnoreCase);
if (_methodToFunctionName.TryGetValue(method, out var functionName) ||
(!isGeography && _geometryMethodToFunctionName.TryGetValue(method, out functionName)))
{
instance = _sqlExpressionFactory.ApplyTypeMapping(
instance, _typeMappingSource.FindMapping(instance.Type, storeType));
var typeMappedArguments = new List <SqlExpression>();
foreach (var argument in arguments)
{
typeMappedArguments.Add(
_sqlExpressionFactory.ApplyTypeMapping(
argument,
typeof(Geometry).IsAssignableFrom(argument.Type)
? _typeMappingSource.FindMapping(argument.Type, storeType)
: _typeMappingSource.FindMapping(argument.Type)));
}
var resultTypeMapping = typeof(Geometry).IsAssignableFrom(method.ReturnType)
? _typeMappingSource.FindMapping(method.ReturnType, storeType)
: _typeMappingSource.FindMapping(method.ReturnType);
var finalArguments = Simplify(typeMappedArguments, isGeography);
var argumentsPropagateNullability = functionName == "STBuffer"
? new[] { false }
: functionName == "STRelate"
? new[] { true, false }
: finalArguments.Select(a => true).ToArray();
return(_sqlExpressionFactory.Function(
instance,
functionName,
finalArguments,
nullable: true,
instancePropagatesNullability: true,
argumentsPropagateNullability,
method.ReturnType,
resultTypeMapping));
}
if (Equals(method, _getGeometryN))
{
return(_sqlExpressionFactory.Function(
instance,
"STGeometryN",
new[]
{
_sqlExpressionFactory.Add(
arguments[0],
_sqlExpressionFactory.Constant(1))
},
nullable: true,
instancePropagatesNullability: true,
argumentsPropagateNullability: new[] { false },
method.ReturnType,
_typeMappingSource.FindMapping(method.ReturnType, storeType)));
}
if (Equals(method, _isWithinDistance))
{
instance = _sqlExpressionFactory.ApplyTypeMapping(
instance, _typeMappingSource.FindMapping(instance.Type, storeType));
var typeMappedArguments = new List <SqlExpression>();
foreach (var argument in arguments)
{
typeMappedArguments.Add(
_sqlExpressionFactory.ApplyTypeMapping(
argument,
typeof(Geometry).IsAssignableFrom(argument.Type)
? _typeMappingSource.FindMapping(argument.Type, storeType)
: _typeMappingSource.FindMapping(argument.Type)));
}
var finalArguments = Simplify(new[] { typeMappedArguments[0] }, isGeography);
return(_sqlExpressionFactory.LessThanOrEqual(
_sqlExpressionFactory.Function(
instance,
"STDistance",
finalArguments,
nullable: true,
instancePropagatesNullability: true,
argumentsPropagateNullability: finalArguments.Select(a => true),
typeof(double)),
typeMappedArguments[1]));
}
}
return(null);
}
/// <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);
}
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);
}
public virtual SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList <SqlExpression> arguments)
{
if (typeof(Geometry).IsAssignableFrom(method.DeclaringType))
{
var geometryExpressions = new[] { instance }.Concat(
arguments.Where(e => typeof(Geometry).IsAssignableFrom(e.Type)));
var typeMapping = ExpressionExtensions.InferTypeMapping(geometryExpressions.ToArray());
Debug.Assert(typeMapping != null, "At least one argument must have typeMapping.");
var storeType = typeMapping.StoreType;
if (_methodToFunctionName.TryGetValue(method, out var functionName) ||
_geometryMethodToFunctionName.TryGetValue(method, out functionName))
{
instance = _sqlExpressionFactory.ApplyTypeMapping(
instance,
_typeMappingSource.FindMapping(instance.Type, storeType));
var typeMappedArguments = new List <SqlExpression>
{
instance
};
foreach (var argument in arguments)
{
typeMappedArguments.Add(
_sqlExpressionFactory.ApplyTypeMapping(
argument,
typeof(Geometry).IsAssignableFrom(argument.Type)
? _typeMappingSource.FindMapping(argument.Type, storeType)
: _typeMappingSource.FindMapping(argument.Type)));
}
var resultTypeMapping = typeof(Geometry).IsAssignableFrom(method.ReturnType)
? _typeMappingSource.FindMapping(method.ReturnType, storeType)
: _typeMappingSource.FindMapping(method.ReturnType);
return(_sqlExpressionFactory.Function(
functionName,
typeMappedArguments,
method.ReturnType,
resultTypeMapping));
}
if (Equals(method, _getGeometryN))
{
return(_sqlExpressionFactory.Function(
"ST_GeometryN",
new[]
{
instance,
_sqlExpressionFactory.Add(
arguments[0],
_sqlExpressionFactory.Constant(1))
},
method.ReturnType,
_typeMappingSource.FindMapping(method.ReturnType, storeType)));
}
if (Equals(method, _distance))
{
return(GetDistanceCallBySrid(
instance,
arguments[0],
method.ReturnType,
_typeMappingSource.FindMapping(method.ReturnType, storeType)));
}
if (Equals(method, _isWithinDistance))
{
return(_sqlExpressionFactory.LessThanOrEqual(
GetDistanceCallBySrid(
instance,
arguments[0],
_distance.ReturnType,
_typeMappingSource.FindMapping(_distance.ReturnType)),
arguments[1]));
}
}
return(null);
}
