Exception in EntityFrameworkSagaRepository on Postgre

[Sergey Kolevatykh]

Hi. I have statemachine with EntityFrameworkSagaRepository, using Postgre DB. When it comes to creating sagas, the following exception appears:

2018-08-15 18:51:05.715 +03:00 [ERR] R-FAULT rabbitmq://localhost/StateMachineTradeQueue 67db0000-36fd-6e15-60a1-08d602c6e8ca BusContracts.ITradeCreationRequestedEvent DAL.SagaModels.TradeSaga(00:00:00.5148368) 42601: syntax error at or near "WITH"

Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "WITH"

   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 1012

--- End of stack trace from previous location where exception was thrown ---

   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 1032

--- End of stack trace from previous location where exception was thrown ---

   at System.Threading.Tasks.ValueTask`1.get_Result()

   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming) in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 444

   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1217

   at System.Threading.Tasks.ValueTask`1.get_Result()

   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1042

   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues, CancellationToken cancellationToken)

   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.ExecuteSqlCommandAsync(DatabaseFacade databaseFacade, RawSqlString sql, IEnumerable`1 parameters, CancellationToken cancellationToken)

   at MassTransit.EntityFrameworkCoreIntegration.Saga.EntityFrameworkSagaRepository`1.MassTransit.Saga.ISagaRepository<TSaga>.Send[T](ConsumeContext`1 context, ISagaPolicy`2 policy, IPipe`1 next)

   at MassTransit.Saga.Pipeline.Filters.CorrelatedSagaFilter`2.GreenPipes.IFilter<MassTransit.ConsumeContext<TMessage>>.Send(ConsumeContext`1 context, IPipe`1 next)

[Alexey Zimarev]

It is an issue with EF not correctly forming SQL statements for Postgresql. Probably, you have a version mismatch between what Postgres version EF supports and what you actually have.