Search code examples
postgresqlentity-framework-corenpgsql

Preventing a shadow property to be included in queries

I'm trying to use PostGIS with EF Core 2.1. Considering that EF Core doesn't support spatial data yet, my approach is to have a geometry column that's set by a trigger function when inserting a row, and I would use that column in my queries with a FromSql clause - so that property is not present at all in my entity.

I've declared the column as a shadow property like this: modelBuilder.Entity<MyEntity>().Property<byte[]>("LocationBlob").HasColumnType("geometry"); and it is correctly created during the migration. My issue is that every time I query this table, the generated SQL SELECTs this column, which leads to an error The field 'LocationBlob' has type 'public.geometry', which is currently unknown to Npgsql.

I actually don't need this property to be retrieved, so is there a way to prevent EF (or the Postgres provider) to include shadow properties in the queries?

Solution

  • EF Core 2.1 (which is currently still in preview) will fully support spatial data - the current situation where there's a lack of support is very temporary, so there's no need to set up any special tricks.

    In fact, improved spatial support will be one of the main areas of improvement. The pre-2.1 spatial support (via the built-in types such as PostgisPoint) will be brought back via a plugin, but in addition PostGIS types will also be supported via the NetTopologySuite .NET spatial library, even providing support for translating the main spatial operations into SQL. So a LINQ query calling NetTopologySuite's x.Covers(y) will be translated to PostGIS ST_Covers(x, y).

    Regarding your specific workaround (which again shouldn't be done), the current problem is that Npgsql's ADO.NET layer (not EF Core) doesn't handle the PostGIS geometry data type, since support has been taken out to an external plugin. This means that at the moment there is no way to either read or write geometry, as byte[] or anything else.

    If spatial is important for you, I'd recommend continuing to work with EF Core 2.0 and Npgsql 3.2.7, until 2.1.0-rc1 is released, at which point support should be there.