Search code examples
entity-framework-corepostgisnpgsql

EntityFramework PostgreSQL GIST index for geography column

I'm trying to create a index, like this one:

CREATE INDEX example1_gpx ON example1 USING GIST (geography(geomcol));

Using EF codefirst, I did this on codefirst:

    builder.HasIndex(x => new
        {
            x.GeoLocation
        })
        .HasMethod("GIST");

but I can't specify that it's a column of type geography.

How I can create this index using CodeFirst ?

Solution

  • Your index above seems to apply a geography() conversion to a geometry column; if so, it's an expression index (as opposed to a simple index over a column). The Npgsql EF Core provider doesn't currently support modeling expression indexes (see this issue), but you can always use raw SQL to create the index in migrations.

    However, I'd recommend thinking why you're converting a geometry column into geography in an expression index; you may want to consider making your column geography instead. Another option is to have an additional generated column of type geography alongside the geometry column.