PostgreSQL problem: '[' in SQL statement

[eliezeralmeida]

This not work for me, I did use DappterExtensions.SqlDialect and DappterExtensions.Configure.
I'm using _context.Connection.GetListAsync

dotnet project

connection configs

Originally posted by @eliezeralmeida in #205 (comment)

[eliezeralmeida]

Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "["
   at Npgsql.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) in /_/Dapper/SqlMapper.cs:line 1055
   at Dapper.SqlMapper.QueryImpl[T](IDbConnection cnn, CommandDefinition command, Type effectiveType)+MoveNext() in /_/Dapper/SqlMapper.cs:line 1083
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at DapperExtensions.DapperImplementor.MappColumns[T](IEnumerable`1 values)
   at DapperExtensions.DapperImplementor.GetListAutoMap[T](IDbConnection connection, IList`1 colsToSelect, IClassMapper classMap, IPredicate predicate, IList`1 sort, IDbTransaction transaction, Nullable`1 commandTimeout, Boolean buffered, IList`1 includedProperties)
   at DapperExtensions.DapperImplementor.InternalGetListAutoMap[T](IDbConnection connection, Object predicate, IList`1 sort, IDbTransaction transaction, Nullable`1 commandTimeout, Boolean buffered, IList`1 colsToSelect, IList`1 includedProperties)
   at DapperExtensions.DapperAsyncImplementor.InternalGetListAutoMapAsync[T](IDbConnection connection, Object predicate, IList`1 sort, IDbTransaction transaction, Nullable`1 commandTimeout, Boolean buffered, IList`1 colsToSelect, IList`1 includedProperties)
   at DapperExtensions.DapperAsyncImplementor.GetListAsync[T](IDbConnection connection, Object predicate, IList`1 sort, IDbTransaction transaction, Nullable`1 commandTimeout, Boolean buffered, IList`1 colsToSelect, IList`1 includedProperties)
   at DapperExtensions.DapperAsyncExtensions.GetListAsync[T](IDbConnection connection, Object predicate, IList`1 sort, IDbTransaction transaction, Nullable`1 commandTimeout, Boolean buffered)

[eliezeralmeida]

news...

GetListAsync not work.
GetList works.

[valfrid-ly]

I'm gonna check this @eliezeralmeida . Both should work

[abukhalid-abdurrahman]

Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "["
   at Npgsql.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) in /_/Dapper/SqlMapper.cs:line 1055
   at Dapper.SqlMapper.QueryImpl[T](IDbConnection cnn, CommandDefinition command, Type effectiveType)+MoveNext() in /_/Dapper/SqlMapper.cs:line 1083
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at DapperExtensions.DapperImplementor.MappColumns[T](IEnumerable`1 values)
   at DapperExtensions.DapperImplementor.GetListAutoMap[T](IDbConnection connection, IList`1 colsToSelect, IClassMapper classMap, IPredicate predicate, IList`1 sort, IDbTransaction transaction, Nullable`1 commandTimeout, Boolean buffered, IList`1 includedProperties)
   at DapperExtensions.DapperImplementor.InternalGetListAutoMap[T](IDbConnection connection, Object predicate, IList`1 sort, IDbTransaction transaction, Nullable`1 commandTimeout, Boolean buffered, IList`1 colsToSelect, IList`1 includedProperties)
   at DapperExtensions.DapperAsyncImplementor.InternalGetListAutoMapAsync[T](IDbConnection connection, Object predicate, IList`1 sort, IDbTransaction transaction, Nullable`1 commandTimeout, Boolean buffered, IList`1 colsToSelect, IList`1 includedProperties)
   at DapperExtensions.DapperAsyncImplementor.GetListAsync[T](IDbConnection connection, Object predicate, IList`1 sort, IDbTransaction transaction, Nullable`1 commandTimeout, Boolean buffered, IList`1 colsToSelect, IList`1 includedProperties)
   at DapperExtensions.DapperAsyncExtensions.GetListAsync[T](IDbConnection connection, Object predicate, IList`1 sort, IDbTransaction transaction, Nullable`1 commandTimeout, Boolean buffered)

Because of the generated SQL query, PostgreSQL does not support this syntax.

[marceln]

Same happens to me, although I explicitly set the dialect. Any news on a fix or workaround?

[valfrid-ly]

What I could notice is the error is in Async method and the settings are being made to DapperExtensions and not to DapperAsyncExtensions.

DapperAsyncExtensions is using a default Dialect to SqlServer instead of the one from the configuration as Sync methods come from DapperExtensions and AsyncMethods comes from DapperAsyncExtensions.