Views with underyling indicies are not used in translation

[michael-celani]

I cannot configure a View to use an underlying existing GIN index, hamstringing its contains operators.

For the following query:

        var xQuery = db.DeckSearchView
            .AsNoTracking()
            .Where(d => d.DeckTags.Contains("x"))
            .ToQueryString();

View Configuration

        modelBuilder.Entity<DeckSearchView>()
            .ToView("DeckSearchView", "public")
            .Metadata.SetIsTableExcludedFromMigrations(true);

        modelBuilder.Entity<DeckSearchView>()
            .HasIndex(e => e.CardIds)
            .HasMethod("gin");

        modelBuilder.Entity<DeckSearchView>()
            .HasIndex(e => e.DeckTags)
            .HasMethod("gin");

SELECT ...
FROM public."DeckSearchView" AS d
WHERE 'x' = ANY (d."DeckTags")

Nonview Configuration (Underlying View in PG):

        modelBuilder.Entity<DeckSearchView>()
            //.ToView("DeckSearchView", "public") Required to use GIN index
            .Metadata.SetIsTableExcludedFromMigrations(true);

        modelBuilder.Entity<DeckSearchView>()
            .HasIndex(e => e.CardIds)
            .HasMethod("gin");

        modelBuilder.Entity<DeckSearchView>()
            .HasIndex(e => e.DeckTags)
            .HasMethod("gin");

SELECT ...
FROM public."DeckSearchView" AS d
WHERE d."DeckTags" @> ARRAY['x']::text[]

[hiSandog]

The surprising part is that index metadata appears to change SQL translation at all: normally HasIndex(...).HasMethod("gin") describes schema/migrations, while the LINQ translator should choose the same array operator whether the entity maps to a table or a view. Could the reproduction include the EF Core/Npgsql versions and the property type/converter for DeckTags? A focused translation test for ToView versus ToTable would show whether the regression is in array Contains translation; then the existing PostgreSQL view index can be verified separately with EXPLAIN.

[roji]

Yeah, you're right - this is a bug, the current GIN index detection logic is for tables only. I'll fix and backport a patch to 10.0.3.

[roji]

10.0.3 is out with the patch.