Migration does not work with custom default schema

[newclaus]

Step:

1. Create new db with default schema 'public'. Then apply some EF migrations. Table __EFMIgrationsHistory must be created
2. Customize search_path in the db
   ALTER ROLE ALL SET search_path TO 'custom'
   Do not specify search path in connection string.
   3, Create new migration and apply it

There is such exception.

Unhandled exception. Npgsql.PostgresException (0x80004005): 42P07: relation "__EFMigrationsHistory" already exists
   at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder`1.StateMachineBox`1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16 token)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at [Microsoft.EntityFrameworkCore.Storage](http://microsoft.entityframeworkcore.storage/).RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at [Microsoft.EntityFrameworkCore.Storage](http://microsoft.entityframeworkcore.storage/).RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at [Microsoft.EntityFrameworkCore.Storage](http://microsoft.entityframeworkcore.storage/).RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateAsync(String targetMigration, CancellationToken cancellationToken)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Migrations.Internal.NpgsqlMigrator.MigrateAsync(String targetMigration, CancellationToken cancellationToken)

It's because repository falls back in schema 'public':

efcore.pg/src/EFCore.PG/Migrations/Internal/NpgsqlHistoryRepository.cs

Lines 28 to 44 in 18d07c5

	protected override string ExistsSql
	{
	get
	{
	var stringTypeMapping = Dependencies.TypeMappingSource.GetMapping(typeof(string));
	return
	$"""
	SELECT EXISTS (
	SELECT 1 FROM pg_catalog.pg_class c
	JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
	WHERE n.nspname={stringTypeMapping.GenerateSqlLiteral(TableSchema ?? "public")} AND
	c.relname={stringTypeMapping.GenerateSqlLiteral(TableName)}
	)
	""";
	}
	}

In my project I've by-passed this behavior by searching with schema only when TableSchema is specified, there is no fallback in public. If schema is not specified, searchin is performed only with table name

[RoystonS]

I hit exactly this issue last week. It's inconsistent behaviour because if you use the command-line tooling to generate the corresponding SQL script (ef migrations script), there's no mention of the explicit public schema.

Btw, the workaround I ended up putting in was to add a DbCommandInterceptor which intercepts the above SQL running and removes public and replaces it with a schema of my choice.