Efcore.pg: "Can't write CLR type System.Byte[] with handler type NetTopologySuiteHandler"

[futurify-ydang]

Hello everyone,

I am using Npgsql and NetopologySuite plugin to work with Postgres db.
Today I face an issue that Can't write CLR type System.Byte[] with handler type NetTopologySuiteHandler when save NetopologySuite.Geometries.MultiPoint into the database in geometry type column.
I don't know what is this problem?
Here are my configurations followed official docs from MS
````
protected override void OnModelCreating(ModelBuilder builder)
{
builder.HasPostgresExtension("postgis");
}

    services.AddDbContext<CSContext>(option =>
     {
            option.UseNpgsql(Configuration.GetConnectionString("DefaultConnection"),
                x => x.UseNetTopologySuite());
     });

````

[futurify-ydang]

NHibernate sql query
``````````
NHibernate:
INSERT
INTO
__Layer
(ServiceId, Name, XmlMapping, OdkForm, TableName, FilterExpression, State, SpatialReference, Type, Drawing, VisibleRange, IsEditable, IsVersioned, SignalRIsAddEventEnabled, SignalRIsUpdateEventEnabled, SignalRIsDeleteEventEnabled, PubNubIsAddEventEnabled, PubNubIsUpdateEventEnabled, PubNubIsDeleteEventEnabled, AzureIsAddEventEnabled, AzuresUpdateEventEnabled, AzureIsDeleteEventEnabled, IsODataEnabled, IsOdkEnabled, HasAttachments, IsSupportTime, TimeFieldId, MinDate, MaxDate, Extent, PopupType, PopupContent, MinScale, MaxScale, RelationshipsJson)
VALUES
(:p0, :p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11, :p12, :p13, :p14, :p15, :p16, :p17, :p18, :p19, :p20, :p21, :p22, :p23, :p24, :p25, :p26, :p27, :p28, :p29, :p30, :p31, :p32, :p33, :p34);
select
lastval();
:p0 = 17 [Type: Int32 (0:0:0)],
:p1 = 'Ss' [Type: String (0:0:0)],
:p2 = '

<cache usage="read-write" />

<id name="FID" column="`FID`" type="Int32">

  <generator class="hilo" />

</id>

<property name="ID" type="String">

  <column name="`ID`" sql-type="text" not-null="false" />

</property>

<property name="Name" type="String">

  <column name="`Name`" sql-type="text" not-null="false" />

</property>

<property name="Address" type="String">

  <column name="`Address`" sql-type="text" not-null="false" />

</property>

<property name="Latitude" type="Single">

  <column name="`Latitude`" sql-type="Real" not-null="false" />

</property>

<property name="Longitude" type="Single">

  <column name="`Longitude`" sql-type="Real" not-null="false" />

</property>

<property name="Altitude" type="Single">

  <colu...' [Type: String (0:0:0)],
:p3 = NULL [Type: String (0:0:0)],
:p4 = 'SS' [Type: String (0:0:0)],
:p5 = NULL [Type: String (0:0:0)],
:p6 = 0 [Type: Int32 (0:0:0)],
:p7 = NULL [Type: Int32 (0:0:0)],
:p8 = NULL [Type: Int32 (0:0:0)],
:p9 = NULL [Type: String (0:0:0)],
:p10 = 0 [Type: Int32 (0:0:0)],
:p11 = True [Type: Boolean (0:0:0)],
:p12 = False [Type: Boolean (0:0:0)],
:p13 = False [Type: Boolean (0:0:0)],
:p14 = False [Type: Boolean (0:0:0)],
:p15 = False [Type: Boolean (0:0:0)],
:p16 = NULL [Type: Boolean (0:0:0)],
:p17 = NULL [Type: Boolean (0:0:0)],
:p18 = NULL [Type: Boolean (0:0:0)],
:p19 = NULL [Type: Boolean (0:0:0)],
:p20 = NULL [Type: Boolean (0:0:0)],
:p21 = NULL [Type: Boolean (0:0:0)],
:p22 = False [Type: Boolean (0:0:0)],
:p23 = NULL [Type: Boolean (0:0:0)],
:p24 = False [Type: Boolean (0:0:0)],
:p25 = False [Type: Boolean (0:0:0)],
:p26 = NULL [Type: Int32 (0:0:0)],
:p27 = NULL [Type: Single (0:0:0)],
:p28 = NULL [Type: Single (0:0:0)],
:p29 = 0x010400002000000000020000000101000020000000000000000000000000000000000000000001010000200000000000000000000000000000000000000000 [Type: Object (0:0:0)],
:p30 = NULL [Type: Int32 (0:0:0)],
:p31 = NULL [Type: String (0:0:0)],
:p32 = NULL [Type: Int32 (0:0:0)],
:p33 = NULL [Type: Int32 (0:0:0)],
:p34 = NULL [Type: String (0:0:0)]

Exception thrown: 'System.InvalidCastException' in NHibernate.dll

``````````

[futurify-ydang]

An unhandled exception has occurred while executing the request. NHibernate.Exceptions.GenericADOException: could not execute query [ /* criteria query */ SELECT this_."Name" as y0_, this_."Location" as y1_, this_."Address" as y2_, this_."Zipcode" as y3_, this_."Island" as y4_, this_."Type" as y5_, this_."FID" as y6_, this_."Geom" as y7_ FROM "banks_1_190222150453" this_ WHERE 1=1 and ST_Intersects(this_."Geom"::text, :p0::text) limit :p1 ] Name:cp0 - Value:POLYGON ((-5009377.0856973119 -7.0811545516136221E-10, 0 -7.0811545516136221E-10, 0 5009377.0856973128, -5009377.0856973119 5009377.0856973128, -5009377.0856973119 -7.0811545516136221E-10))

Anyone can give me a help? I really need this kind of query working now. Thanks

[YohDeadfall]

It isn't clear how you are inserting data and how NHibernate is related to the issue. Could you provide a minimal repro?

The NTS handler doesn't support raw data, but RawPostgis can help.

[futurify-ydang]

@YohDeadfall thanks for your reply.
I will provide the saving data code here.

The first i have a geometry column with NHibernate mapping

<property name="Extent" type="NHibernate.Spatial.Type.GeometryType, NHibernate.Spatial">
      <column name="`Extent`" sql-type="geometry" not-null="true" />
</property>

using Nhibernate session to save database
`````
var initPoint = new Point(0, 0);
hashtableLayer[nameof(layer.Extent)] = new MultiPoint(new IPoint[] { initPoint, initPoint });

ContextPerRequest.Session.Save(nameof(Layer), hashtableLayer);
ContextPerRequest.Session.Flush();
``````
Does it make sense? i will prepare a repo if this does not enough clear.
After upgrade to Net Core and Npgsql.EntityFrameworkCore i used to implement this bunch of code with nhibernate and npgsql.Entityframework6. This exception used to throw that time.
I had to downgrade Npgsql to 3.2.7 and it worked Ok. But now i can not do this because Npgsql.EntityFramework core has Npgsql version 4.0.4 as a dependency.

[futurify-ydang]

DemoPostgis.zip

This is minimal project which reproduce my error.

[YohDeadfall]

Thanks, will investigate as soon as possible.

[YohDeadfall]

You're using NHibernate.Spatial which automatically converts an geometry to a byte array, but Npgsql expects a geometry object. This is because you call the UseNetTopologySuite method when configuring a DbContext, and this method registers the NTS plugin globally:

https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL/blob/10802aef53e3f61b9cc068986b52ccbfea630a84/src/EFCore.PG.NTS/Extensions/NpgsqlNetTopologySuiteDbContextOptionsBuilderExtensions.cs#L32-L33

So you need to use the RawPostgis plugin and do not register NTS in EF, or you need to teach NHibernate not to handle conversion automatically. Probably the last thing means that you shouldn't use NHibernate.Spatial.

Closing the issue as there is no bug in Npgsql, but feel free to continue the discussion.

[futurify-ydang]

Thanks you @YohDeadfall .
I found this comment said that Npgsql.RawPostgis was an undocumented plugin.
As requirements, i have to use NHibernate.Spatial to work with geometry data. So i want to know how to use this Npgsql.RawPostgis plugin?
Thanks and excuse for my bad english.

[futurify-ydang]

i finally find out the way

NpgsqlConnection.GlobalTypeMapper.UseRawPostgis();

Thanks you @YohDeadfall