Closed suadev closed 3 years ago
Maybe duplication of https://github.com/npgsql/efcore.pg/issues/1559
As @roji said, should be fixed in 5.0.1, so there must be something else.
@suadev can you please post a minimal, runnable code sample? It's really difficult to investigate from partial fragments such as the above - I need to see the types of the properties being accessed in the query, etc.
I was afraid of that :) But, guess what happened? I downgraded to 5.0.0-rc2 and it worked.
So maybe you can figure it out by comparing the versions?
Sorry, since this is a large scale app, it's too hard to create a minimal sample.
But if you can't find the root, I am here ready to help.
@suadev I can't really compare anything to 5.0.0-rc2, because I don't have actual code to compare :)
Can you at least try extracting specifically the queries you mentioned above, creating the minimal model around them, and see if that reproduces the issue? I'd have to do the same on my side, except I don't even have your model...
I have the same problem with a query that worked in 5.0.1:
List<Guid> orgaIds = await _organisationService.GetOrganisationIdsByCustomer(customerId);
return await Context.Project.Where(p => orgaIds.Contains(p.OrganisationId)).ToListAsync(cancellationToken);
In my case the list to compare only included one item with Guid cdaac8b3-57ff-471d-a75e-c262e9d2d89f
(just test data from my development db). This is the stacktrace:
System.InvalidOperationException: Couldn't find array or element type mapping in ArrayAnyAllExpression at Npgsql.EntityFrameworkCore.PostgreSQL.Query.NpgsqlSqlExpressionFactory.ApplyTypeMappingsOnItemAndArray(SqlExpression itemExpression, SqlExpression arrayExpression) at Npgsql.EntityFrameworkCore.PostgreSQL.Query.NpgsqlSqlExpressionFactory.ApplyTypeMappingOnAny(PostgresAnyExpression postgresAnyExpression) at Npgsql.EntityFrameworkCore.PostgreSQL.Query.NpgsqlSqlExpressionFactory.ApplyTypeMapping(SqlExpression sqlExpression, RelationalTypeMapping typeMapping) at Microsoft.EntityFrameworkCore.Query.SqlExpressionFactory.ApplyDefaultTypeMapping(SqlExpression sqlExpression) at Npgsql.EntityFrameworkCore.PostgreSQL.Query.NpgsqlSqlExpressionFactory.Any(SqlExpression item, SqlExpression array, PostgresAnyOperatorType operatorType) at Npgsql.EntityFrameworkCore.PostgreSQL.Query.ExpressionTranslators.Internal.NpgsqlArrayTranslator.
gTranslateCommon|7_1(SqlExpression arrayOrList, IReadOnlyList 1 arguments, <>c__DisplayClass7_0& ) at Npgsql.EntityFrameworkCore.PostgreSQL.Query.ExpressionTranslators.Internal.NpgsqlArrayTranslator.Translate(SqlExpression instance, MethodInfo method, IReadOnlyList
1 arguments, IDiagnosticsLogger`1 logger) at Microsoft.EntityFrameworkCore.Query.RelationalMethodCallTranslatorProvider.<>cDisplayClass4_0.b3(IMethodCallTranslator t) at System.Linq.Enumerable.SelectEnumerableIterator 2.MoveNext() at System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable
1 source, Func2 predicate, Boolean& found) at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable
1 source, Func2 predicate) at Microsoft.EntityFrameworkCore.Query.RelationalMethodCallTranslatorProvider.Translate(IModel model, SqlExpression instance, MethodInfo method, IReadOnlyList
1 arguments, IDiagnosticsLogger`1 logger) at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCall) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression) at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression) at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateExpression(Expression expression) at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateLambdaExpression(ShapedQueryExpression shapedQueryExpression, LambdaExpression lambdaExpression) at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateWhere(ShapedQueryExpression source, LambdaExpression predicate) at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query) at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>cDisplayClass12_01.<ExecuteAsync>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func
1 compiler) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1.GetAsyncEnumerator(CancellationToken cancellationToken) at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable
1.GetAsyncEnumerator() at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken) at DroidSolutions.Internal.Admin.Services.ProjectService.<>c__DisplayClass3_0.<<GetProjectsOfCustomer>b__0>d.MoveNext() in /home/sissmer/dev/droidsolutions-admin-tool/DroidSolutions.Internal.Admin/Services/ProjectService.cs:line 38 --- End of stack trace from previous location --- at Microsoft.Extensions.Caching.Memory.CacheExtensions.GetOrCreateAsync[TItem](IMemoryCache cache, Object key, Func
2 factory) at
The Model code is:
public partial class Project
{
public Project()
{
}
public Guid Id { get; set; }
public Guid OrganisationId { get; set; }
}
@Kampfmoehre as above, I need a runnable code sample - the fragments aren't enough to investigate. I tried with the below code and everything seems to be working fine.
@roji I have created a quick repo which shows the error when run against my Database. Unfortunately I cannot expose my DB for you to test but I am working on adding seed data so you can test against a local DB. Until then can you try it against your own DB?
@Kampfmoehre thanks for the repro, I can see the error happening. The reason is that you're mapping the OrganisationId property (which is a Guid) to a database character varying
. If you map to a uuid
instead, the code works.
I'll investigate why this is happening.
@suadev can you please confirm whether you're also using a value converter?
Yes, There is an enum-to-string conversion for both cases.
builder.Property(p => p.Type).HasConversion<string>();
builder.Property(fa => fa.ActivityType).HasConversion<string>();
@roji Thanks for the quick response. The column is indeed character varying but the content is always a UUID, I tried to set an explicit conversion via
entity.Property(e => e.OrganisationId)
.IsRequired()
.HasColumnName("organisationId")
.HasColumnType("character varying")
.HasConversion(p => p.ToString(), p => (Guid)Guid.Parse(p));
but i still get the error. Is there anything else I can do to make that work? Unfortunately I cannot change the type of the column (the database is originally created and used by a NodeJS application).
This started failing in 5.0.1 because of #1589, which improved support for generic List, but left out full support for value conversions. I've just submitted #1610 to fix this.
You can switch to using an array instead of a List for the meantime, or go back to 5.0.0 (for EFCore.PG only - EF Core can stay at 5.0.1) until 5.0.2 is released with the fix.
Hi @roji,
we are using a custom ValueConverterSelector to apply conversion of a specific base type (typed ids, taken from here). This does also trigger the Couldn't find array or element type mapping in ArrayAnyAllExpression
exception for a queries with Contains
.
If we remove the ValueConverterSelector
and apply the conversion manually via HasConversion
it works though. Is this something that should work or would you consider using a custom ValueConverterSelector out of scope?
See git clone https://github.com/SpatialFocus/GlobalValueConverterArray
for a repo.
Without diving into the code, if you've managed to make this work manually by specifying HasConversion, then it's very likely that your custom ValueConverterSelector has a bug in it somewhere... It isn't really supposed to do anything beyond automating what you can manually do with HasConversion.
Note that EF Core doesn't currently support value converters on generated properties (e.g. identity IDs, https://github.com/dotnet/efcore/issues/11597), so I'm not sure this is a very good idea right now.
Thank you very much for your fast reply.
Without diving into the code, if you've managed to make this work manually by specifying HasConversion, then it's very likely that your custom ValueConverterSelector has a bug in it somewhere... It isn't really supposed to do anything beyond automating what you can manually do with HasConversion.
That was my first thought, but it works for all none range queries, eg context.MyEntitiesA.Where(x => x.EntityB == entity)
and for some but not all range queries:
// Works
context.MyEntitiesA.Where(x => context.MyEntitiesB.Contains(x.EntityB)).ToList();
MyEntityBId[] ids = context.MyEntitiesB.Select(x => x.Id).ToArray();
context.MyEntitiesA.Where(x => ids.Contains(x.EntityB.Id)).ToList();
// Does not work
List<MyEntityB> list = context.MyEntitiesB.ToList();
context.MyEntitiesA.Where(x => list.Contains(x.EntityB)).ToList();
MyEntityB[] list = context.MyEntitiesB.ToArray();
context.MyEntitiesA.Where(x => list.Contains(x.EntityB)).ToList();
List<MyEntityBId> ids = context.MyEntitiesB.Select(x => x.Id).ToList();
context.MyEntitiesA.Where(x => ids.Contains(x.EntityB.Id)).ToList();
Interesting that it works with MyEntityBId[]
but not with List<MyEntityBId>
. The first one we are currently using as a workaround.
Note that EF Core doesn't currently support value converters on generated properties (e.g. identity IDs, dotnet/efcore#11597), so I'm not sure this is a very good idea right now.
I know, we will possible stick to client generated guids.
@Dresel in that case, can you please open a new issue with the full code sample for the range issue? There are indeed some last points where lists aren't supported as well as arrays, I'm trying to get the support to parity.
Sure, give me some time.
@roji hello :) i have a some question. Like a author issue i have this type exception, but with type IEnumerable<Guid(or etc.)>. Can u support this to future version?
After upgrading to 5.0.1, some query service tests got failed. I put 2 sample below.
Exception: "Couldn't find array or element type mapping in ArrayAnyAllExpression"
I guess the root cause is 'Contains' but not sure about it.
Previous versions;
"Npgsql.EntityFrameworkCore.PostgreSQL" Version="3.1.4" "Microsoft.EntityFrameworkCore" Version="3.1.7"
Stack Trace:
"{\r\n \"errorId\": \"c1af4766-2bea-4cb0-989f-c831686bc6f8\",\r\n \"statusCode\": 500,\r\n \"errors\": [\r\n \"Couldn't find array or element type mapping in ArrayAnyAllExpression\",\r\n \" at Npgsql.EntityFrameworkCore.PostgreSQL.Query.NpgsqlSqlExpressionFactory.ApplyTypeMappingsOnItemAndArray(SqlExpression itemExpression, SqlExpression arrayExpression)\r\n at Npgsql.EntityFrameworkCore.PostgreSQL.Query.NpgsqlSqlExpressionFactory.ApplyTypeMappingOnAny(PostgresAnyExpression postgresAnyExpression)\r\n at Npgsql.EntityFrameworkCore.PostgreSQL.Query.NpgsqlSqlExpressionFactory.ApplyTypeMapping(SqlExpression sqlExpression, RelationalTypeMapping typeMapping)\r\n at Microsoft.EntityFrameworkCore.Query.SqlExpressionFactory.ApplyDefaultTypeMapping(SqlExpression sqlExpression)\r\n at Npgsql.EntityFrameworkCore.PostgreSQL.Query.NpgsqlSqlExpressionFactory.Any(SqlExpression item, SqlExpression array, PostgresAnyOperatorType operatorType)\r\n at Npgsql.EntityFrameworkCore.PostgreSQL.Query.ExpressionTranslators.Internal.NpgsqlArrayTranslator.gTranslateCommon|7_1(SqlExpression arrayOrList, IReadOnlyListb3(IMethodCallTranslator t)\r\n at System.Linq.Enumerable.SelectEnumerableIteratorgAwaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)\r\n at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)\r\n at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)\r\n at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)\r\n at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)\r\n at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.gAwaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)\r\n at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)\r\n at Microsoft.AspNetCore.Routing.EndpointMiddleware.g AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)\r\n at Microsoft.AspNetCore.Authorization.Policy.AuthorizationMiddlewareResultHandler.HandleAsync(RequestDelegate next, HttpContext context, AuthorizationPolicy policy, PolicyAuthorizationResult authorizeResult)\r\n at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)\r\n at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)\r\n at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)\r\n at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)\r\n at Infrastructure.Middleware.GlobalExceptionHandlerMiddleware.Invoke(HttpContext context, IResourceRepository resources) in C:\\source\\Pms.Backend\\src\\Infrastructure\\Middleware\\GlobalExceptionHandlerMiddleware.cs:line 34\"\r\n ]\r\n}"
1 arguments, <>c__DisplayClass7_0& )\\r\\n at Npgsql.EntityFrameworkCore.PostgreSQL.Query.ExpressionTranslators.Internal.NpgsqlArrayTranslator.Translate(SqlExpression instance, MethodInfo method, IReadOnlyList
1 arguments, IDiagnosticsLogger`1 logger)\r\n at Microsoft.EntityFrameworkCore.Query.RelationalMethodCallTranslatorProvider.<>cDisplayClass4_0.2.MoveNext()\\r\\n at System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable
1 source, Func2 predicate, Boolean& found)\\r\\n at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable
1 source, Func2 predicate)\\r\\n at Microsoft.EntityFrameworkCore.Query.RelationalMethodCallTranslatorProvider.Translate(IModel model, SqlExpression instance, MethodInfo method, IReadOnlyList
1 arguments, IDiagnosticsLogger`1 logger)\r\n at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCall)\r\n at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)\r\n at Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal.NpgsqlSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)\r\n at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression)\r\n at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression)\r\n at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateExpression(Expression expression)\r\n at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateLambdaExpression(ShapedQueryExpression shapedQueryExpression, LambdaExpression lambdaExpression)\r\n at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateWhere(ShapedQueryExpression source, LambdaExpression predicate)\r\n at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)\r\n at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>cDisplayClass12_01.<ExecuteAsync>b__0()\\r\\n at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func
1 compiler)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1.GetAsyncEnumerator(CancellationToken cancellationToken)\\r\\n at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable
1.GetAsyncEnumerator()\r\n at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken)\\r\\n at Pms.Core.Domain.Folio.FolioActivityDomainService.GetActivityTexts(String baseLang, List
1 activities) in C:\\source\\Pms.Backend\\src\\Pms.Core\\Domain\\Folio\\FolioActivityDomainService.cs:line 89\r\n at Pms.Core.Domain.Folio.FolioActivityDomainService.GetTransactionActivities(Guid transactionId, Guid userId, Guid propertyId) in C:\\source\\Pms.Backend\\src\\Pms.Core\\Domain\\Folio\\FolioActivityDomainService.cs:line 52\r\n at Pms.Core.Application.Folio.Queries.GetTransactionActivitiesQueryHandler.Handle(GetTransactionActivitiesQuery query, CancellationToken cancellationToken) in C:\\source\\Pms.Backend\\src\\Pms.Core\\Application\\Folio\\Queries\\GetTransactionActivitiesQueryHandler.cs:line 26\r\n at MediatR.Pipeline.RequestExceptionProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate
1 next)\r\n at MediatR.Pipeline.RequestExceptionProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate
1 next)\r\n at MediatR.Pipeline.RequestExceptionActionProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate
1 next)\r\n at MediatR.Pipeline.RequestExceptionActionProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate
1 next)\r\n at MediatR.Pipeline.RequestPostProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate
1 next)\r\n at MediatR.Pipeline.RequestPreProcessorBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate
1 next)\r\n at Infrastructure.StartupConfiguration.MediatR.RequestLoggerBehavior2.Handle(TRequest request, CancellationToken cancellationToken, RequestHandlerDelegate
1 next) in C:\\source\\Pms.Backend\\src\\Infrastructure\\StartupConfiguration\\MediatR\\RequestLoggerBehavior.cs:line 24\r\n at Pms.Api.Controllers.FoliosController.GetChargeActivities(Guid id, Guid chargeId) in C:\\source\\Pms.Backend\\src\\Pms.Api\\Controllers\\FoliosController.cs:line 200\r\n at lambda_method5930(Closure , Object )\r\n at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)\r\n at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.