Converting IQueryable to List using EF core with postgres DB

Question

I am using a postgres DB and EF core. I have the following code get data using DB context

IQueryable<string> projectIds = context.Projects.Where(x => x.Collaborators.Contains(userId)).Select(x => x.Id);
List<string> ids = projectIds.ToList();

When I execute projectIds.ToList(), I get the exception (copied below).

What is the right way of using IQueryable? Or is there any other way to convert IQueryable to List?

Any suggestions would help a lot..

Exception details:

  HResult=0x80004005
  Message=42883: operator does not exist: character varying[] @> text[]
  Source=Npgsql
  StackTrace:
   at Npgsql.NpgsqlConnector.<<ReadMessage>g__ReadMessageLong|194_0>d.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at Npgsql.NpgsqlDataReader.<NextResult>d__44.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.<ExecuteReader>d__100.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Npgsql.NpgsqlCommand.<ExecuteReader>d__100.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Threading.Tasks.ValueTask`1.get_Result()
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)```

The problem is not `ToList()` it's likely something in the previous line. `ToList()` only caused the `IQueryable` to be queried/enumerated. I'm guessing that `.Contains()` is causing some broken SQL to be generated that postgresql doesn't understand. — phuzi, Apr 08 '22 at 10:32
Show generated SQL. But looks like a bug in Npgsql EF Core provider. Which version do you use? — Svyatoslav Danyliv, Apr 08 '22 at 10:50
Interesting, what array is doing here. Show your model and mapping. — Svyatoslav Danyliv, Apr 08 '22 at 11:11
Anyway, looks like `Npgsql.EntityFrameworkCore.PostgreSQL` incorrectly generated parameter. — Svyatoslav Danyliv, Apr 08 '22 at 12:07
Please provide a full code sample, including the model and its configuration; the code above should definitely work. — Shay Rojansky, Apr 11 '22 at 18:24

score 0 · Accepted Answer · answered Apr 13 '22 at 15:28

Thanks for all the suggestions in comments. After hours of trial found out that the error was not during converting to ToList().

The error lies with using contains operator on Character varying[]

[Not sure, if it is any kind of bug]

Modifying the Character varying[] array to text[] in postgres fixed the issue.

Converting IQueryable to List using EF core with postgres DB

1 Answers1