Devart.Data.PostgreSql.Entity.EFCore can not create EFCore migration history table in custom schema

[jamir.araujo]

I'm using the package Devart.Data.PostgreSql.Entity.EFCore, version 7.14.1470.0, and I'm facing an error when I try to set a custom schema to the migration history table.

Here is some code:

Code: Select all

var builder = new DbContextOptionsBuilder<ContactContext>();

builder.UsePostgreSql(
      "Data Source=localhost;User Id=postgres;Password=admin;Database=Contacts;Unicode=true",
       b => b.MigrationsHistoryTable(HistoryRepository.DefaultTableName, "SomeSchema"));

If I try to initiate a database with the command Update-Database, the following error is thrown:

Code: Select all

Failed executing DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
-- Script
CREATE SCHEMA IF NOT EXISTS "TestSchema";
CREATE TABLE "TestSchema"."__EFMigrationsHistory" ( 
  "MigrationId" varchar(150) NOT NULL,
  "ProductVersion" varchar(32) NOT NULL,
  PRIMARY KEY ("MigrationId")
)
Devart.Data.PostgreSql.PgSqlException (0x80004005): cannot insert multiple commands into a prepared statement
   at   .(Boolean , Boolean , Char , Boolean )
   at   .      (   )
   at   .()
   at Devart.Data.PostgreSql.PgSqlCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteNonQuery()
   at   .ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
cannot insert multiple commands into a prepared statement

[Shalex]

Please add "UnpreparedExecute=true;" to your connection string. Refer to https://www.devart.com/dotconnect/postg ... tring.html.

[jamir.araujo]

I've added the "UnpreparedExecute=false" parameter to my connection string with no efect. The error is still the same.

The only thing that works is adding "Protocol=ver20" to the connection string.

[jamir.araujo]

This issue has surfaced again. Setting "UnpreparedExecute=true;" on the ConnectionString does not work, and now I can no longer set "Protocol=ver20", or I receive the following error "unsupported frontend protocol 2.0: server supports 3.0 to 3.0".

[jamir.araujo]

Hi! I did some more digging on this issue, and the UnpreparedExecute is not being set for commands involving migrations.

Here is the EFCore code for the MigrateAsync method of the Migrator class.

Code: Select all

public virtual async Task MigrateAsync(
    string? targetMigration = null,
    CancellationToken cancellationToken = default)
{
    _logger.MigrateUsingConnection(this, _connection);

    if (!await _historyRepository.ExistsAsync(cancellationToken).ConfigureAwait(false))
    {
        if (!await _databaseCreator.ExistsAsync(cancellationToken).ConfigureAwait(false))
        {
            await _databaseCreator.CreateAsync(cancellationToken).ConfigureAwait(false);
        }

        var command = _rawSqlCommandBuilder.Build(
            _historyRepository.GetCreateScript());

        await command.ExecuteNonQueryAsync(
                new RelationalCommandParameterObject(
                    _connection,
                    null,
                    null,
                    _currentContext.Context,
                    _commandLogger, CommandSource.Migrations),
                cancellationToken)
            .ConfigureAwait(false);
    }

    var commandLists = GetMigrationCommandLists(
        await _historyRepository.GetAppliedMigrationsAsync(cancellationToken).ConfigureAwait(false),
        targetMigration);

    foreach (var commandList in commandLists)
    {
        await _migrationCommandExecutor.ExecuteNonQueryAsync(commandList(), _connection, cancellationToken)
            .ConfigureAwait(false);
    }
}

Of our interest are the variables _rawSqlCommandBuilder, _historyRepository and command .

The _rawSqlCommandBuilder is the default EFCore implementation. _historyRepository is the de Devart implementation, and command is also the Devart implementation.

The call _historyRepository.GetCreateScript() returns the following scritp:

Code: Select all

-- Script
CREATE SCHEMA IF NOT EXISTS "cbfb002c-2b85-4453-882a-40157fa6e11c";
CREATE TABLE "cbfb002c-2b85-4453-882a-40157fa6e11c"."__EFMigrationsHistory" ( 
  "MigrationId" varchar(150) NOT NULL,
  "ProductVersion" varchar(32) NOT NULL,
  PRIMARY KEY ("MigrationId")
)

This is used as text for the command that will return from the call _rawSqlCommandBuilder.Build, and when the command is executed it will generate an error because the script has two commands.

Now, using ILSpy to look the Devart implementation of the ReleationCommand (yes, I went that far):

Code: Select all

internal class v : cz
{
	[CompilerGenerated]
	private bool m_a;

	public v(RelationalCommandBuilderDependencies A_0, string A_1, IReadOnlyList<IRelationalParameter> A_2)
		: base(A_0, A_1, A_2)
	{
	}

	public override DbCommand CreateDbCommand(RelationalCommandParameterObject A_0, Guid A_1, DbCommandMethod A_2)
	{
		//IL_0001: Unknown result type (might be due to invalid IL or missing references)
		//IL_0003: Unknown result type (might be due to invalid IL or missing references)
		DbCommand dbCommand = base.CreateDbCommand(A_0, A_1, A_2);
		((Devart.Data.PostgreSql.Entity.q)dbCommand).b(a());
		return dbCommand;
	}

	[SpecialName]
	[CompilerGenerated]
	public bool a()
	{
		return this.m_a;
	}

	[SpecialName]
	[CompilerGenerated]
	public void a(bool A_0)
	{
		this.m_a = A_0;
	}
}

This line ((Devart.Data.PostgreSql.Entity.q)dbCommand).b(a()); is suposed to set the UnpreparedExecute, but the variable m_a is never set, so when the command is executed the value is false.

To test if this was really the issue, I replaced the default implementation of the IRawSqlCommandBuilder interface with my own implementation that just inherits from the default implemention and overrides the method IRelationalCommand Build(string sql).

Here is my implementation:

Code: Select all

public class TnfRawSqlCommandBuilder : RawSqlCommandBuilder
{
    public TnfRawSqlCommandBuilder(
        IRelationalCommandBuilderFactory relationalCommandBuilderFactory,
        ISqlGenerationHelper sqlGenerationHelper,
        IParameterNameGeneratorFactory parameterNameGeneratorFactory)
        : base(relationalCommandBuilderFactory, sqlGenerationHelper, parameterNameGeneratorFactory)
    {
    }

    public override IRelationalCommand Build(string sql)
    {
        var command = base.Build(sql);

        var commandType = command.GetType();

        commandType.GetMethod("a", new[] { typeof(bool) }).Invoke(command, new object[] { true });

        return command;
    }
}

Here, through reflection, I call the method a(bool A_0) passing true to it. This in turn will set UnpreparedExecute to true when the actual command is created.

This made issue go away.

While this works, it's not a reliable solution. The source code of the class Devart.Data.PostgreSql.Entity.v can change and the method a can change its name.

I believe ither class Devart.Data.PostgreSql.Entity.v or Devart.Common.Entity.cz could get the UnpreparedExecute parameter from the connection that is available on the RelationalCommandParameterObject parameter of the method CreateDbCommand.

[Shalex]

Thank you for your report. We have reproduced and are investigating the issue. We will notify you about the result.