Setting the search path in EF Core dynamically in an interceptor

I discovered that setting the search path dynamically when creating the DbContext essentially creates a separate connection pool for each query string. This causes issues when using e.g. different search paths per tenant, so I'm trying to implement this now in a way that does not require modifying the database query string.

I'm using EF Core on .NET 6 with Postgres as the database. I implemented an interceptor that adds a SET search_path TO "some_schema"; statement at the top of each query that is executed. The schema name is passed in dynamically from the DbContext to the interceptor, so this avoids having to modify the query string in the DbContext. The full code of the interceptor is the following:

public class SchemaInterceptor : DbCommandInterceptor
{
  private readonly string path;

  public SchemaInterceptor(string path)
  {
    this.path = path;
  }

  private void SetSearchPath(DbCommand command)
  {
    command.CommandText = $"SET search_path TO \"{path}\";\n{command.CommandText}";
  }

  public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
  {
    SetSearchPath(command);
    return base.ReaderExecuting(command, eventData, result);
  }


  public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default)
  {
    SetSearchPath(command);
    return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
  }

  public override InterceptionResult<int> NonQueryExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<int> result)
  {
    SetSearchPath(command);
    return base.NonQueryExecuting(command, eventData, result);
  }

  public override ValueTask<InterceptionResult<int>> NonQueryExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<int> result, CancellationToken cancellationToken = default)
  {
    SetSearchPath(command);
    return base.NonQueryExecutingAsync(command, eventData, result, cancellationToken);
  }


  public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result)
  {
    SetSearchPath(command);
    return base.ScalarExecuting(command, eventData, result);
  }

  public override ValueTask<InterceptionResult<object>> ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<object> result, CancellationToken cancellationToken = default)
  {
    SetSearchPath(command);
    return base.ScalarExecutingAsync(command, eventData, result, cancellationToken);
  }

}

This interceptor works for SELECT queries and simple INSERTS, but curiously it fails for inserting entities with many-to-many relations:

[15:21:03 DBG] Executing DbCommand [Parameters=[@p0='?' (DbType = DateTime), @p1='?' (DbType = DateTime), @p2='?', @p3='?'], CommandType='Text', CommandTimeout='30']
INSERT INTO projects (date_added, date_last_modified, name, notes)
VALUES (@p0, @p1, @p2, @p3)
RETURNING projects_id;
[15:21:03 INF] Executed DbCommand (1ms) [Parameters=[@p0='?' (DbType = DateTime), @p1='?' (DbType = DateTime), @p2='?', @p3='?'], CommandType='Text', CommandTimeout='30']
SET search_path TO "test";
INSERT INTO projects (date_added, date_last_modified, name, notes)
VALUES (@p0, @p1, @p2, @p3)
RETURNING projects_id;
[15:21:03 DBG] The foreign key property 'Project.Id' was detected as changed. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see property values.
3
[15:21:03 DBG] The foreign key property 'projects_tags.projects_id' was detected as changed. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see property values.
[15:21:03 DBG] A data reader was disposed.
[15:21:03 DBG] Executing 3 update commands as a batch.
[15:21:03 DBG] Creating DbCommand for 'ExecuteReader'.
[15:21:03 DBG] Created DbCommand for 'ExecuteReader' (0ms).
INSERT INTO projects_tags (project_tags_id, projects_id)
VALUES (@p4, @p5);
INSERT INTO projects_tags (project_tags_id, projects_id)
VALUES (@p6, @p7);
INSERT INTO projects_tags (project_tags_id, projects_id)
VALUES (@p8, @p9);
[15:21:03 INF] Executed DbCommand (8ms) [Parameters=[@p4='?' (DbType = Int32), @p5='?' (DbType = Int32), @p6='?' (DbType = Int32), @p7='?' (DbType = Int32), @p8='?' (DbType = Int32), @p9='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SET search_path TO "test";
INSERT INTO projects_tags (project_tags_id, projects_id)
VALUES (@p4, @p5);
INSERT INTO projects_tags (project_tags_id, projects_id)
VALUES (@p6, @p7);
INSERT INTO projects_tags (project_tags_id, projects_id)
VALUES (@p8, @p9);

[...]

[15:21:03 DBG] Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
   at Npgsql.EntityFrameworkCore.PostgreSQL.Update.Internal.NpgsqlModificationCommandBatch.ConsumeAsync(RelationalDataReader reader, CancellationToken cancellationToken)

[...]

Now the error message does not make any kind of sense to me. There is no concurrency conflict detection active in this case and no possible way I can see that these insert statements could fail to insert rows that would not also cause a database error. The same code also works perfectly fine if I set the search_path in the DbContext without this interceptor. And inserting entities without relations works as well.

I'm wondering if I'm doing something inherently unsafe here with my interceptor, maybe I am violating some expectations EF Core has about how things happen. I originally considered an interceptor at the connection level instead of the command level, but I was not sure how connection pooling would affect this. Setting the search path for each command also should not cause problems by itself as far as I understand.

What could be the issue that these inserts fail with my interceptor? Is it possible to fix the interceptor, or is my approach here to the problem inherently flawed?