My ASP.NET Core 8 Web API & EF Core 8 app doesn't recognize the geography type from the PostGIS extension. This is the error I get :
System.InvalidCastException: Reading as 'NetTopologySuite.Geometries.Point' is not supported for fields having DataTypeName 'public.geography'
As the extension was installed in the public schema, I tried changing the search_path
to include "public" but it still didn't work.
Here are the installed extensions
List of installed extensions
Name | Version | Schema | Description
-------------+---------+------------+------------------------------------------------------------
aws_commons | 1.2 | public | Common data types across AWS services
aws_lambda | 1.0 | public | AWS Lambda integration
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 3.4.2 | public | PostGIS geometry and geography spatial types and functions
uuid-ossp | 1.1 | public | generate universally unique identifiers (UUIDs)
This is how the data type is mapped in the entity class:
[Column("spatiallocation", TypeName = "geography")]
public Point? SpatialLocation { get; set; }
This is how the DbContext
is registered:
services.AddDbContext<GeodataDbContext>(options =>
{
options.UseNpgsql(_configuration.GetConnectionString("GeodataConnectionString"),
b => b
.UseNetTopologySuite()
);
});
Double-check that the search path is correctly set to include the public schema. You can set the search path in your PostgreSQL configuration or via the connection string like so:
services.AddDbContext<GeodataDbContext>(options =>
{
options.UseNpgsql("Host=myserver;Database=mydb;Username=myuser;Password=mypassword;Search Path=public",
b => b.UseNetTopologySuite());
});
Or you can do it in SQL wherever the PostGIS extension is installed:
SET search_path TO public;