Postgres 10 Geometry type throws exception

Posts   
 
    
dodyg
User
Posts: 42
Joined: 04-Dec-2014
# Posted on: 07-Jul-2018 15:18:39   

According to this thread (https://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=24713), Geometry is supported. Npgsql also supports it (http://www.npgsql.org/doc/types/basic.html).

I am using the latest LLBLGEN and NpgSql 4.0.1 (the same error in 3.2.7 as well)

This is the schema


-- Table: public.city

-- DROP TABLE public.city;

CREATE TABLE public.city
(
    id smallint NOT NULL,
    uuid uuid NOT NULL DEFAULT gen_random_uuid(),
    country_id character varying(3) COLLATE pg_catalog."default" NOT NULL,
    name_en character varying(255) COLLATE pg_catalog."default" NOT NULL,
    name_ar character varying(255) COLLATE pg_catalog."default",
    name_fr character varying(255) COLLATE pg_catalog."default",
    lat_long geometry,
    is_active boolean NOT NULL DEFAULT true,
    CONSTRAINT "PK_city" PRIMARY KEY (id),
    CONSTRAINT "FK_city_country_id_country_id" FOREIGN KEY (country_id)
        REFERENCES public.country (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.city
    OWNER to postgres;

-- Index: IX_city_country_id

-- DROP INDEX public."IX_city_country_id";

CREATE INDEX "IX_city_country_id"
    ON public.city USING btree
    (country_id COLLATE pg_catalog."default")
    TABLESPACE pg_default;

-- Index: IX_city_uuid

-- DROP INDEX public."IX_city_uuid";

CREATE UNIQUE INDEX "IX_city_uuid"
    ON public.city USING btree
    (uuid)
    TABLESPACE pg_default;

Generates the following error. The field 'LatLong' is mapped to Object at the designer so it should be OK.

NotSupportedException: The field 'LatLong' has type 'public.geometry', which is currently unknown to Npgsql. You can retrieve it as a string by marking it as unknown, please see the FAQ. Npgsql.NpgsqlDefaultDataReader.GetValue(int ordinal) in NpgsqlDefaultDataReader.cs Npgsql.NpgsqlDataReader.GetValues(object[] values) in NpgsqlDataReader.cs SD.LLBLGen.Pro.ORMSupportClasses.EntityMaterializerBase.MaterializeAsync(Func<IDataReader, object[], Exception, bool> valueReadErrorHandler, CancellationToken cancellationToken) SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.ExecuteMultiRowRetrievalQueryAsync(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, bool allowDuplicates, IEntityFields2 fieldsUsedForQuery, CancellationToken cancellationToken) SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityCollectionInternalAsync(QueryParameters parameters, CancellationToken cancellationToken) SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityCollectionAsync(QueryParameters parameters, CancellationToken cancellationToken) SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteWithActiveRecoveryStrategyAsync(Func<Task> toExecute, CancellationToken cancellationToken) SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteEntityProjectionAsync(QueryExpression toExecute, CancellationToken cancellationToken) SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpressionAsync(Expression handledExpression, Type typeForPostProcessing, bool performPostProcessing, CancellationToken cancellationToken) SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.PerformExecuteAsync(Expression expression, Type typeForPostProcessing, bool performPostProcessing, CancellationToken cancellationToken) SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteAsync<TResult>(Expression expression, CancellationToken cancellationToken) SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery<T>.ExecuteAsync<TResult>(CancellationToken cancellationToken) SD.LLBLGen.Pro.LinqSupportClasses.QueryableExtensionMethods.ToListAsync<TResult>(IQueryable<TResult> source, CancellationToken cancellationToken)

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 09-Jul-2018 07:26:14   

dodyg wrote:

I am using the latest LLBLGEN and NpgSql 4.0.1 (the same error in 3.2.7 as well)

It is not clear what LLBLGen version are you using.

David Elizondo | LLBLGen Support Team
Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 09-Jul-2018 17:05:44   

Did you install the NpgSql from GitHub as instructed here: http://www.llblgen.com/documentation/5.4/Designer/Concepts/DatabaseDrivers.htm#postgresql

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Jul-2018 17:10:16   

I think it's 5.4. Regardless, it's an issue with Npgsql: https://github.com/npgsql/npgsql/issues/807 in Npgsql v2 it worked, but in v3 they switched to a different way of reading the data and since then it doesn't work anymore (well, all types except PostGIS).

This thread might help finding a workaround: http://www.llblgen.com/TinyForum/GotoMessage.aspx?MessageID=141453&ThreadID=24814 as there's currently no other way of doing this: I don't know how to tell Npgsql to fetch the type as we simply call GetValues() and leave it to the ADO.NET provider to get us the array with values (so there's no way to specify things).

So overriding that method is I think the only 'solution' (well, more like a way to make it work) at the moment. It's clumsy but there's no other way. I have no idea if npgsql will ever get the support back for geometry/postgis types natively as the spatial types in .NET is one big mess (to put it lightly)

Frans Bouma | Lead developer LLBLGen Pro
dodyg
User
Posts: 42
Joined: 04-Dec-2014
# Posted on: 11-Jul-2018 10:23:43   

There's a bigger problem.

Previous versions have allowed basic usage of PostGIS's spatial types via built-in Npgsql types, which were limited in many ways. Thanks to a new plugin infrastructure, you can now use the Npgsql.NetTopologySuite plugin, which maps PostGIS types to the NetTopologySuite spatial library's types. NetTopologySuite's types are more complete, and support a variety of spatial operations and conversions you can perform after loading your spatial data from PostgreSQL.

If you prefer to use JSON for your spatial types, the Npgsql.GeoJSON plugin maps PostGIS types to GeoJSON.NET types. GeoJSON is a standard JSON format for spatial data.

Finally, if you prefer to use the previous Npgsql types (e.g. PostgisPoint), these are available via the Npgsql.LegacyPostgis plugin.

Thanks to @YohDeadfall for implementing both the NetTopologySuite and GeoJSON plugins.

http://www.npgsql.org/doc/release-notes/4.0.html#improved-spatial-support-postgis

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 11-Jul-2018 16:15:53   

This is just a mess, how am I suppose to enable these plugins... If one isn't using Entity Framework Core, things fall apart very quickly with this it seems. rage

I'll see if I can get this sorted with Npgsql...

(edit) https://github.com/npgsql/npgsql/issues/2056

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 11-Jul-2018 20:51:30   

Dodyg, can you try this: https://github.com/npgsql/npgsql/issues/2056#issuecomment-404265842 ? I.e. call the NpgsqlConnection method at startup of your app? You need to take a dependency on Npgsql, but if that's ok (and if you're using .net core you have to do that anyway) it could solve it simple_smile

Frans Bouma | Lead developer LLBLGen Pro
dodyg
User
Posts: 42
Joined: 04-Dec-2014
# Posted on: 12-Jul-2018 10:45:40   

Awesome. It works.


 public class TestGeometryModel : PageModel
    {
        public List<(string name, NetTopologySuite.Geometries.Geometry coordinate)> Cities = new List<(string name, NetTopologySuite.Geometries.Geometry coordinate)>();

        public async Task OnGetAsync()
        {
            var result = await Query.GetAsync(meta =>
              {
                  var query = from x in meta.City
                              select new
                              {
                                  Name = x.NameEnglish,
                                  Coordinate = x.LatLong as NetTopologySuite.Geometries.Geometry
                              };

                  return query.ToListAsync();
              });

            Cities = result.Select(x => (x.Name, x.Coordinate)).ToList();
        }
    }

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 12-Jul-2018 10:59:19   

Glad that's solved!

Frans Bouma | Lead developer LLBLGen Pro