search

tmsmith / Dapper-Extensions

Dapper Extensions is a small library that complements Dapper by adding basic CRUD operations (Get, Insert, Update, Delete) for your POCOs. For more advanced querying scenarios, Dapper Extensions provides a predicate system. The goal of this library is to keep your POCOs pure by not requiring any attributes or base class inheritance.
1.79k stars 585 forks source link

PostgreSQL problem: '[' in SQL statement #273

Closed eliezeralmeida closed 2 years ago

eliezeralmeida commented 3 years ago

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

dotnet project image

connection configs image

Originally posted by @eliezeralmeida in https://github.com/tmsmith/Dapper-Extensions/issues/205#issuecomment-921413498

eliezeralmeida commented 3 years ago 
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 commented 3 years ago

news...

GetListAsync not work. GetList works.

valfrid-ly commented 3 years ago

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

abukhalid-abdurrahman commented 3 years ago 
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)

image

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

marceln commented 2 years ago

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

valfrid-ly commented 2 years ago

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.