NetTopologySuite.Geometries.Polygon

[afust003]

We have an EF Core 5 migration against Postgres 13.6 server for a model that is generating the following migration for a PostGIS v3.2 field:

Field:

public ntsGeometries.Polygon ATDLocation { get; set; }

Migration:

The problem is that RPG brings in the field as a string with the following user-defined configuration. RPG field

public string AtdLocation { get; set; } // ATDLocation

RPG Configuration:

builder.Property(x => x.AtdLocation).HasColumnName(@"ATDLocation").HasColumnType("user-defined").IsRequired();

Does RPG support PostGIS data types? If not, what is a good work-around besides having to manually (a) excluding the table (b) manually including each model + configuration + their relationships for those models that contain PostGIS data types?

Thanks!

[afust003]

Note: on line 55 of this link (https://github.com/sjh37/EntityFramework-Reverse-POCO-Code-First-Generator/blob/390d2ac72d1f7bdbafb5ad7cbe6852004810b7c7/Generator/LanguageMapping/PostgresToCSharp.cs) I see the following dictionary mapping entry which seems to support Polygon?

  { "polygon",                     "NpgsqlPolygon" },

[sjh37]

Hi @afust003. I will need your help with this one. I have created a mappings as you found above in the PostgresToCSharp.cs class, but I don't have any test databases with those types in to fully test with. Something I still need to do!

The best way to change/play with the generator is not to edit the .ttinclude file as that is a T4 template. The .ttinclude file is actually generated by running the BuildTT project in the generator solution if you clone it. Edit the code in the Generator project as that is a normal project.

I used pages like https://www.npgsql.org/doc/types/basic.html to get the mapping correct between Postgres and C#.

The HasColumnType("user-defined") is also not correct,

Would it be possible for you to prove the DDL for this table so I can create it here and test with it.

[afust003]

Hi @sjh37, here's a representative DDL.

-- Table: dbo.TestPostGisTbl

-- DROP TABLE IF EXISTS dbo."TestPostGisTbl";

CREATE TABLE IF NOT EXISTS dbo."TestPostGisTbl"
(
    "Id" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    "TblMinValue" numeric(22,0) NOT NULL,
    "TblMaxValue" numeric(22,0) NOT NULL,
    "ATDLocation" geometry(Polygon) NOT NULL,
    CONSTRAINT "TestPostGisTbl_pk" PRIMARY KEY ("Id")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dbo."TestPostGisTbl"
    OWNER to postgres;

[afust003]

Partial success: I see BuiltTT proj is referencing Generator proj via dll reference and even managed to change the BuiltTT.cs file to reprocude the Database.tt/Database.cs with incorrect column output:

public string AtdLocation { get; set; } // ATDLocation

Stuck: However, I'm not sure how to debug the Generator proj? Can you provide guidance? Perhaps debugging CodeGenerator.cs can allow inspection of how PocoColumnModel is being generated?

[sjh37]

For debugging, you could modify one of the unit tests. Change the function SingleDatabaseTestPostgreSql.ReverseEngineerPostgreSQL to point to your database.

Run the test, and it should fail, but output the code in C:\Users\YourName\Documents\Northwind_PostgreSQL_EfCore3_FkLegacy.cs

[sjh37]

I ran the unit test and it output:

// TestPostGisTbl
public class dbo_TestPostGisTbl
{
    public int Id { get; set; } // Id (Primary key)
    public decimal TblMinValue { get; set; } // TblMinValue
    public decimal TblMaxValue { get; set; } // TblMaxValue
    public string AtdLocation { get; set; } // ATDLocation
}

// TestPostGisTbl
public class dbo_TestPostGisTblConfiguration : IEntityTypeConfiguration<dbo_TestPostGisTbl>
{
    public void Configure(EntityTypeBuilder<dbo_TestPostGisTbl> builder)
    {
        builder.ToTable("TestPostGisTbl", "dbo");
        builder.HasKey(x => x.Id).HasName("TestPostGisTbl_pk");

        builder.Property(x => x.Id).HasColumnName(@"Id").HasColumnType("integer").IsRequired().ValueGeneratedOnAdd().UseIdentityColumn();
        builder.Property(x => x.TblMinValue).HasColumnName(@"TblMinValue").HasColumnType("numeric(22,0)").IsRequired();
        builder.Property(x => x.TblMaxValue).HasColumnName(@"TblMaxValue").HasColumnType("numeric(22,0)").IsRequired();
        builder.Property(x => x.AtdLocation).HasColumnName(@"ATDLocation").HasColumnType("user-defined").IsRequired();
    }
}

[sjh37]

As you see I too am getting HasColumnType("user-defined"). I wonder if it might be best to exclude that for any user-defined returned types?

[afust003]

I like your idea of removing "user-defined" returned types, however, if that is done, it might be prudent for RPG to generate some comments stating that "user-defined" POCO field was left out. If the above is done, does that mean that PostGIS types won't be properly supported in the near future?

Tangential question, while waiting for this issue to be resolved, is there a configurable way to exclude a column from a specific table via the .tt file? It appears class ColumnFilter gets me close; (a) do we need to create a new class similar to ColumnFilter or can we somehow add a lamda function to an existing class (say Settings.UpdateColumn) that will filter out the column in question?

[sjh37]

Good idea about the comment. If it was removed you will still happily work with the database without an issue, however if you ever want to reverse engineer a database, delete the RPG and create a migration, you would be relying on the EntityFramework to work out what the best type was to use when creating a new database for you. So I'm pretty sure in all respects it will be fine.

[sjh37]

Column filters are global and hit all the tables that have that matching column name. To be specific, add some code to the Settings.UpdateColumn callback in your .tt file, about line 320 and mark the column as hidden.

if (table.NameHumanCase.Equals("SomeTable",  StringComparison.InvariantCultureIgnoreCase) &&
   column.NameHumanCase.Equals("SomeColumn", StringComparison.InvariantCultureIgnoreCase))
{
    column.Hidden = true; // Hidden means the generator does not generate any code for this column at all.
}

[afust003]

@sjh37 Thanks, yes, that work-around (excluding the troublesome RPG column via Settings.UpdateColumn) worked as expected.

[sjh37]

I'll update the generator to exclude those and add a comment. Thanks for your help @afust003 !

[sjh37]

That is done now @afust003 Grab the latest code from EF.Reverse.POCO.v3.ttinclude and replace your project file.

Any problems, let me know and I'll re-open this case. Thanks for your help.