Global Query Filters and SetOutputIdentity = true don't work together for PostgreSQL

[PavelMudrechenkoCoherent]

Hi. I found that the library fails when you have SetOutputIdentity = true and some Global Query Filters together with PostgreSQL despite of the fact that support of Global Query Filters is stated in documentation. To reproduce it I used simple example:

public class Event
{

    [Key]
    public int Id { get; set; }

    [Required]
    public string AccountCode { get; set; }

    [Required]
    public string EventId { get; set; }
}

public class MyContext : DbContext
{
    public virtual DbSet<Event> Events { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<Event>().HasQueryFilter(x => x.AccountCode == "DEMO");
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseNpgsql("UserId=postgres;Password=postgres;Server=localhost;Port=5432;Database=postgres;")
            .LogTo(Console.Write);
    }
}

public class Program
{
    static void Main(string[] args)
    {

        using (var context = new MyContext())
        {
            var bigListOfEvents = Enumerable.Range(220, 10)
                .Select(i => new Event { EventId = $"EventId{i}", AccountCode = "DEMO" })
                .ToList();

            context.BulkInsert(bigListOfEvents, config => config.SetOutputIdentity = true);
        }
    }
}

This code fails with 42601: syntax error at or near "INTO" error. The thing is that it generates the following SQL which is not valid

SELECT g."Id",
       g."AccountCode",
       g."EventId"
FROM
  (INSERT INTO "Event" ("AccountCode",
                        "EventId")
     (SELECT "AccountCode",
             "EventId"
      FROM "EventTemp3d7ed29e") ON CONFLICT ("Id") DO UPDATE
   SET "AccountCode" = EXCLUDED."AccountCode",
       "EventId" = EXCLUDED."EventId" RETURNING "Id",
                                                "AccountCode",
                                                "EventId";) AS g
WHERE g."AccountCode" = 'DEMO'

You can turn this code into working by either removing global query filter modelBuilder.Entity<Event>().HasQueryFilter(x => x.AccountCode == "DEMO"); or by setting SetOutputIdentity as false, or both.

I used the following versions: Postgres 13 net6.0 Npgsql.EntityFrameworkCore.PostgreSQL 6.0.1 Microsoft.EntityFrameworkCore 6.0.1 EFCore.BulkExtensions 6.4.2

[borisdj]

Currently that feature is supported only on SqlServer. Will see to extend it soon for PG as well.

[borisdj]

For now fix is to set recently added config IgnoreGlobalQueryFilters = true;