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);
}
