public SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList <SqlExpression> arguments) { // TODO: Fully support List<> if (arguments.Count == 0 || !arguments[0].Type.IsArray && (!arguments[0].Type.IsGenericType || arguments[0].Type.GetGenericTypeDefinition() != typeof(List <>))) { return(null); } var arrayOperand = arguments[0]; if (method.IsClosedFormOf(SequenceEqual) && arguments[1].Type.IsArray) { return(_sqlExpressionFactory.Equal(arrayOperand, arguments[1])); } // Predicate-less Any - translate to a simple length check. if (method.IsClosedFormOf(EnumerableAnyWithoutPredicate)) { return (_sqlExpressionFactory.GreaterThan( _jsonPocoTranslator.TranslateArrayLength(arrayOperand) ?? _sqlExpressionFactory.Function("cardinality", arguments, typeof(int?)), _sqlExpressionFactory.Constant(0))); } // Note that .Where(e => new[] { "a", "b", "c" }.Any(p => e.SomeText == p))) // is pattern-matched in AllAnyToContainsRewritingExpressionVisitor, which transforms it to // new[] { "a", "b", "c" }.Contains(e.SomeText). // Here we go further, and translate that to the PostgreSQL-specific construct e.SomeText = ANY (@p) - // this is superior to the general solution which will expand parameters to constants, // since non-PG SQL does not support arrays. If the list is a constant we leave it for regular IN // (functionality the same but more familiar). // TODO: The following does not work correctly if there are any nulls in a parameterized array, because // of null semantics (test: Contains_on_nullable_array_produces_correct_sql). // https://github.com/aspnet/EntityFrameworkCore/issues/15892 tracks caching based on parameter values, // which should allow us to enable this and have correct behavior. // We still apply this translation when it's on a column expression, since that can't work anyway with // EF Core's parameter to constant expansion if (method.IsClosedFormOf(Contains) && arrayOperand is ColumnExpression && //!(arrayOperand is SqlConstantExpression) && // When the null semantics issue is resolved _sqlExpressionFactory.FindMapping(arrayOperand.Type) != null) { return(_sqlExpressionFactory.ArrayAnyAll(arguments[1], arrayOperand, ArrayComparisonType.Any, "=")); } // Note: we also translate .Where(e => new[] { "a", "b", "c" }.Any(p => EF.Functions.Like(e.SomeText, p))) // to LIKE ANY (...). See NpgsqlSqlTranslatingExpressionVisitor.VisitMethodCall. return(null); }
public virtual SqlExpression Translate( SqlExpression instance, MethodInfo method, IReadOnlyList <SqlExpression> arguments, IDiagnosticsLogger <DbLoggerCategory.Query> logger) { if (instance != null && instance.Type.IsGenericList() && method.Name == "get_Item" && arguments.Count == 1) { return // Try translating indexing inside json column (_jsonPocoTranslator.TranslateMemberAccess(instance, arguments[0], method.ReturnType) ?? // Other types should be subscriptable - but PostgreSQL arrays are 1-based, so adjust the index. _sqlExpressionFactory.ArrayIndex(instance, GenerateOneBasedIndexExpression(arguments[0]))); } if (arguments.Count == 0) { return(null); } var array = arguments[0]; if (!array.Type.TryGetElementType(out var elementType)) { return(null); // Not an array/list } // The array/list CLR type may be mapped to a non-array database type (e.g. byte[] to bytea, or just // value converters). Make sure we're dealing with an array // Regardless of CLR type, we may be dealing with a non-array database type (e.g. via value converters). if (array.TypeMapping is RelationalTypeMapping typeMapping && !(typeMapping is NpgsqlArrayTypeMapping) && !(typeMapping is NpgsqlJsonTypeMapping)) { return(null); } if (method.IsClosedFormOf(SequenceEqual) && arguments[1].Type.IsArray) { return(_sqlExpressionFactory.Equal(array, arguments[1])); } // Predicate-less Any - translate to a simple length check. if (method.IsClosedFormOf(EnumerableAnyWithoutPredicate)) { return(_sqlExpressionFactory.GreaterThan( _jsonPocoTranslator.TranslateArrayLength(array) ?? _sqlExpressionFactory.Function( "cardinality", arguments, nullable: true, argumentsPropagateNullability: TrueArrays[1], typeof(int)), _sqlExpressionFactory.Constant(0))); } // Note that .Where(e => new[] { "a", "b", "c" }.Any(p => e.SomeText == p))) // is pattern-matched in AllAnyToContainsRewritingExpressionVisitor, which transforms it to // new[] { "a", "b", "c" }.Contains(e.Some Text). if (method.IsClosedFormOf(Contains) && ( // Handle either parameters (no mapping but supported CLR type), or array columns. We specifically // don't want to translate if the type mapping is bytea (CLR type is array, but not an array in // the database). array.TypeMapping == null && _typeMappingSource.FindMapping(array.Type) != null || array.TypeMapping is NpgsqlArrayTypeMapping ) && // Exclude arrays/lists over Nullable<T> since the ADO layer doesn't handle them (but will in 5.0) Nullable.GetUnderlyingType(elementType) == null) { var item = arguments[1]; switch (array) { // When the array is a column, we translate to array @> ARRAY[item]. GIN indexes // on array are used, but null semantics is impossible without preventing index use. case ColumnExpression _: if (item is SqlConstantExpression constant && constant.Value is null) { // We special-case null constant item and use array_position instead, since it does // nulls correctly (but doesn't use indexes) // TODO: once lambda-based caching is implemented, move this to NpgsqlSqlNullabilityProcessor // (https://github.com/dotnet/efcore/issues/17598) and do for parameters as well. return(_sqlExpressionFactory.IsNotNull( _sqlExpressionFactory.Function( "array_position", new[] { array, item }, nullable: true, argumentsPropagateNullability: FalseArrays[2], typeof(int)))); } return(_sqlExpressionFactory.Contains(array, _sqlExpressionFactory.NewArrayOrConstant(new[] { item }, array.Type))); // Don't do anything PG-specific for constant arrays since the general EF Core mechanism is fine // for that case: item IN (1, 2, 3). // After https://github.com/aspnet/EntityFrameworkCore/issues/16375 is done we may not need the // check any more. case SqlConstantExpression _: return(null); // For ParameterExpression, and for all other cases - e.g. array returned from some function - // translate to e.SomeText = ANY (@p). This is superior to the general solution which will expand // parameters to constants, since non-PG SQL does not support arrays. // Note that this will allow indexes on the item to be used. default: return(_sqlExpressionFactory.Any(item, array, PostgresAnyOperatorType.Equal)); } } // Note: we also translate .Where(e => new[] { "a", "b", "c" }.Any(p => EF.Functions.Like(e.SomeText, p))) // to LIKE ANY (...). See NpgsqlSqlTranslatingExpressionVisitor.VisitArrayMethodCall. return(null); }
public SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList <SqlExpression> arguments) { // TODO: Fully support List<> if (arguments.Count == 0) { return(null); } var operand = arguments[0]; var operandElementType = operand.Type.IsArray ? operand.Type.GetElementType() : operand.Type.IsGenericType && operand.Type.GetGenericTypeDefinition() == typeof(List <>) ? operand.Type.GetGenericArguments()[0] : null; if (operandElementType == null) // Not an array/list { return(null); } // Even if the CLR type is an array/list, it may be mapped to a non-array database type (e.g. via value converters). if (operand.TypeMapping is RelationalTypeMapping typeMapping && !(typeMapping is NpgsqlArrayTypeMapping) && !(typeMapping is NpgsqlJsonTypeMapping)) { return(null); } if (method.IsClosedFormOf(SequenceEqual) && arguments[1].Type.IsArray) { return(_sqlExpressionFactory.Equal(operand, arguments[1])); } // Predicate-less Any - translate to a simple length check. if (method.IsClosedFormOf(EnumerableAnyWithoutPredicate)) { return(_sqlExpressionFactory.GreaterThan( _jsonPocoTranslator.TranslateArrayLength(operand) ?? _sqlExpressionFactory.Function("cardinality", arguments, typeof(int?)), _sqlExpressionFactory.Constant(0))); } // Note that .Where(e => new[] { "a", "b", "c" }.Any(p => e.SomeText == p))) // is pattern-matched in AllAnyToContainsRewritingExpressionVisitor, which transforms it to // new[] { "a", "b", "c" }.Contains(e.SomeText). // Here we go further, and translate that to the PostgreSQL-specific construct e.SomeText = ANY (@p) - // this is superior to the general solution which will expand parameters to constants, // since non-PG SQL does not support arrays. If the list is a constant we leave it for regular IN // (functionality the same but more familiar). // Note: we exclude constant array expressions from this PG-specific optimization since the general // EF Core mechanism is fine for that case. After https://github.com/aspnet/EntityFrameworkCore/issues/16375 // is done we may not need the check any more. // Note: we exclude arrays/lists over Nullable<T> since the ADO layer doesn't handle them (but will in 5.0) if (method.IsClosedFormOf(Contains) && _sqlExpressionFactory.FindMapping(operand.Type) != null && !(operand is SqlConstantExpression) && Nullable.GetUnderlyingType(operandElementType) == null) { var item = arguments[1]; // We require a null semantics check in case the item is null and the array contains a null. // Advanced parameter sniffing would help here: https://github.com/aspnet/EntityFrameworkCore/issues/17598 return(_sqlExpressionFactory.OrElse( // We need to coalesce to false since 'x' = ANY ({'y', NULL}) returns null, not false // (and so will be null when negated too) _sqlExpressionFactory.Coalesce( _sqlExpressionFactory.ArrayAnyAll(item, operand, ArrayComparisonType.Any, "="), _sqlExpressionFactory.Constant(false)), _sqlExpressionFactory.AndAlso( _sqlExpressionFactory.IsNull(item), _sqlExpressionFactory.IsNotNull( _sqlExpressionFactory.Function( "array_position", new[] { operand, _sqlExpressionFactory.Fragment("NULL") }, typeof(int)))))); } // Note: we also translate .Where(e => new[] { "a", "b", "c" }.Any(p => EF.Functions.Like(e.SomeText, p))) // to LIKE ANY (...). See NpgsqlSqlTranslatingExpressionVisitor.VisitMethodCall. return(null); }
public SqlExpression?Translate(SqlExpression instance, MemberInfo member, Type returnType, IDiagnosticsLogger <DbLoggerCategory.Query> logger) { if (instance is null) { return(null); } if (instance.TypeMapping is NpgsqlJsonTypeMapping mapping) { if (instance?.Type.IsGenericCollection() == true && member.Name == nameof(List <object> .Count) && instance.TypeMapping is null) { return(_jsonPocoTranslator.TranslateArrayLength(instance)); } if (member.DeclaringType !.IsGenericType && (typeof(IDictionary <,>).IsAssignableFrom(member.DeclaringType.GetGenericTypeDefinition()) || member.DeclaringType.GetInterfaces().Any(a => a.IsGenericType && a.GetGenericTypeDefinition() == typeof(IDictionary <,>))) ) { if (member.Name == nameof(IDictionary.Keys)) { var type = (member as PropertyInfo) !.PropertyType.GetGenericArguments()[0]; var realReturnType = typeof(List <>).MakeGenericType(type); var sub = _sqlExpressionFactory.ApplyDefaultTypeMapping(_sqlExpressionFactory.Function( mapping.IsJsonb ? "jsonb_each_text" : "json_each_text", new[] { instance }, nullable: true, argumentsPropagateNullability: TrueArrays[1], typeof(string)) ); var binary = new PostgresUnknownBinaryExpression(_sqlExpressionFactory.Fragment("select key"), sub, "from", typeof(string), _stringTypeMapping); var r = _sqlExpressionFactory.ApplyDefaultTypeMapping(_sqlExpressionFactory.Function( "ARRAY", new SqlExpression[] { binary }, nullable: true, argumentsPropagateNullability: TrueArrays[1], typeof(string[]))); if (type != typeof(string)) { r = _sqlExpressionFactory.ApplyDefaultTypeMapping(_sqlExpressionFactory.Convert(r, realReturnType)); } return(r); } if (member.Name == nameof(IDictionary.Values)) { var type = (member as PropertyInfo) !.PropertyType.GetGenericArguments()[0]; var realReturnType = typeof(List <>).MakeGenericType(type); var sub = _sqlExpressionFactory.ApplyDefaultTypeMapping(_sqlExpressionFactory.Function( mapping.IsJsonb ? "jsonb_each_text" : "json_each_text", new[] { instance }, nullable: true, argumentsPropagateNullability: TrueArrays[1], typeof(string)) ); var binary = new PostgresUnknownBinaryExpression(_sqlExpressionFactory.Fragment("select value"), sub, "from", typeof(string), _stringTypeMapping); var r = _sqlExpressionFactory.ApplyDefaultTypeMapping(_sqlExpressionFactory.Function( "ARRAY", new SqlExpression[] { binary }, nullable: true, argumentsPropagateNullability: TrueArrays[1], realReturnType)); if (type != typeof(string)) { r = _sqlExpressionFactory.ApplyDefaultTypeMapping(_sqlExpressionFactory.Convert(r, realReturnType)); } return(r); } } } else if (instance.TypeMapping is NpgsqlArrayTypeMapping arrayMapping && member.DeclaringType !.IsGenericCollection() && member.Name == nameof(ICollection.Count)) { return(_sqlExpressionFactory.Function( "cardinality", new[] { instance }, nullable: true, argumentsPropagateNullability: TrueArrays[1], typeof(int?))); } if (!typeof(JToken).IsAssignableFrom(member.DeclaringType)) { return(null); } if (member.Name == nameof(JToken.Root) && instance is ColumnExpression column && column.TypeMapping is NpgsqlJsonTypeMapping) { // Simply get rid of the RootElement member access return(column); } return(null); }
SqlExpression?TranslateCommon(SqlExpression arrayOrList, IReadOnlyList <SqlExpression> arguments) { // Predicate-less Any - translate to a simple length check. if (method.IsClosedFormOf(EnumerableAnyWithoutPredicate)) { return(_sqlExpressionFactory.GreaterThan( _jsonPocoTranslator.TranslateArrayLength(arrayOrList) ?? _sqlExpressionFactory.Function( "cardinality", new[] { arrayOrList }, nullable: true, argumentsPropagateNullability: TrueArrays[1], typeof(int)), _sqlExpressionFactory.Constant(0))); } // Note that .Where(e => new[] { "a", "b", "c" }.Any(p => e.SomeText == p))) // is pattern-matched in AllAnyToContainsRewritingExpressionVisitor, which transforms it to // new[] { "a", "b", "c" }.Contains(e.Some Text). if ((method.IsClosedFormOf(EnumerableContains) || // Enumerable.Contains extension method method.Name == nameof(List <int> .Contains) && method.DeclaringType.IsGenericList() && method.GetParameters().Length == 1) && ( // Handle either array columns (with an array mapping) or parameters/constants (no mapping). We specifically // don't want to translate if the type mapping is bytea (CLR type is array, but not an array in // the database). // arrayOrList.TypeMapping == null && _typeMappingSource.FindMapping(arrayOrList.Type) != null || arrayOrList.TypeMapping is NpgsqlArrayTypeMapping or null )) { var item = arguments[0]; switch (arrayOrList) { // When the array is a column, we translate to array @> ARRAY[item]. GIN indexes // on array are used, but null semantics is impossible without preventing index use. case ColumnExpression: if (item is SqlConstantExpression constant && constant.Value is null) { // We special-case null constant item and use array_position instead, since it does // nulls correctly (but doesn't use indexes) // TODO: once lambda-based caching is implemented, move this to NpgsqlSqlNullabilityProcessor // (https://github.com/dotnet/efcore/issues/17598) and do for parameters as well. return(_sqlExpressionFactory.IsNotNull( _sqlExpressionFactory.Function( "array_position", new[] { arrayOrList, item }, nullable: true, argumentsPropagateNullability: FalseArrays[2], typeof(int)))); } return(_sqlExpressionFactory.Contains(arrayOrList, _sqlExpressionFactory.NewArrayOrConstant(new[] { item }, arrayOrList.Type))); // Don't do anything PG-specific for constant arrays since the general EF Core mechanism is fine // for that case: item IN (1, 2, 3). // After https://github.com/aspnet/EntityFrameworkCore/issues/16375 is done we may not need the // check any more. case SqlConstantExpression: return(null); // For ParameterExpression, and for all other cases - e.g. array returned from some function - // translate to e.SomeText = ANY (@p). This is superior to the general solution which will expand // parameters to constants, since non-PG SQL does not support arrays. // Note that this will allow indexes on the item to be used. default: return(_sqlExpressionFactory.Any(item, arrayOrList, PostgresAnyOperatorType.Equal)); } } // Note: we also translate .Where(e => new[] { "a", "b", "c" }.Any(p => EF.Functions.Like(e.SomeText, p))) // to LIKE ANY (...). See NpgsqlSqlTranslatingExpressionVisitor.VisitArrayMethodCall. return(null); }
public SqlExpression?Translate(SqlExpression instance, MethodInfo method, IReadOnlyList <SqlExpression> arguments, IDiagnosticsLogger <DbLoggerCategory.Query> logger) { if (typeof(JToken).IsAssignableFrom(method.DeclaringType) && method.Name == "get_Item" && arguments.Count == 1) { return((instance is ColumnExpression columnExpression ? _sqlExpressionFactory.JsonTraversal( columnExpression, returnsText: false, typeof(string), instance.TypeMapping) : instance) is PostgresJsonTraversalExpression prevPathTraversal ? prevPathTraversal.Append(_sqlExpressionFactory.ApplyDefaultTypeMapping(arguments[0])) : null); } if (arguments.FirstOrDefault() is PostgresJsonTraversalExpression traversal) { // Support for .Value<T>() and .Value<U, T>(): if (instance == null && method.Name == nameof(Extensions.Value) && method.DeclaringType == typeof(Extensions) && method.IsGenericMethod && method.GetParameters().Length == 1 && arguments.Count == 1) { var traversalToText = new PostgresJsonTraversalExpression( traversal.Expression, traversal.Path, returnsText: true, typeof(string), _stringTypeMapping); if (method.ReturnType == typeof(string)) { return(traversalToText); } else { return(_sqlExpressionFactory.Convert(traversalToText, method.ReturnType, _typeMappingSource.FindMapping(method.ReturnType))); } } // Support for Count() if (instance == null && method.Name == nameof(Enumerable.Count) && method.DeclaringType == typeof(Enumerable) && method.IsGenericMethod && method.GetParameters().Length == 1 && arguments.Count == 1) { return(_jsonPocoTranslator.TranslateArrayLength(traversal)); } // Predicate-less Any - translate to a simple length check. if (method.IsClosedFormOf(_enumerableAnyWithoutPredicate) && arguments.Count == 1 && arguments[0].Type.TryGetElementType(out _) && arguments[0].TypeMapping is NpgsqlJsonTypeMapping) { return(_sqlExpressionFactory.GreaterThan( _jsonPocoTranslator.TranslateArrayLength(arguments[0]), _sqlExpressionFactory.Constant(0))); } } return(null); }