Configure/Influence the generated SELECT statement for specific columns/properties

[faryu]

Hello,

I'm having some trouble using EF Core 2.1 together with PostGis. I succedded in writing geometries to the database using a ValueConverter and writing the WKB directly to the database. If I then try to read existing entries via iterating over the existing entries in the DbSet, than I receive an exception, because it cannot map the "public.geometry" type from PostgreSql to the required byte[].

The select query looks like this: SELECT p."Id", p."Position", ... FROM MyTable ...

What it has to look like, in order for it to provide the WKB data, is like this: SELECT p."Id", p."Position"::bytea , ... FROM MyTable ...

I just used the geometry type mapping as a specific example, but this issue should address a more general solution to this kind of problem: To influence the generated sql select statement for specific columns.

modelBuilder.Entity<MyTable>(e =>
            {
                e.Property(p => p.Position).HasSelectStatement(p => $"ST_AsText({p}}")
                // Generates: SELECT p."Id", ST_AsText(p."Position") , ... FROM MyTable ...

               // or
                e.Property(p => p.Position).HasSelectStatement(p => $"{p}::bytea")
                // Generates: SELECT p."Id", p."Position"::bytea , ... FROM MyTable ...
            });

The HasSelectStatement would then have to extract the correct way to address the column like before and combine it with the provided statement.

I know that spatial support is comming at some point in the future #1100 , but this could also enable the usage of other database specific features or types.

[faryu]

There may be an issue mapping the result to the properties, so either the HasSelectStatement function adds the "... As table.column" at the end automatically or it has to be added manually.

[roji]

Do you have a specific reason for rolling your own solution with value converters instead of using Npgsql's built-in support for spatial with NetTopologySuite? See http://www.npgsql.org/efcore/mapping/nts.html

[divega]

I also would like to hear the answer to @roji's question, but otherwise it sounds like https://github.com/aspnet/EntityFrameworkCore/issues/10861 would address the requirement, although being able to specify a SQL fragment directly sounds interesting too.

[faryu]

Thank you for the link! I only found http://www.npgsql.org/doc/types/nts.html so far and couldn't get it to work with it. I just tried the sample provided from your link and it doesn't work. I've created a sample like yours and succeeded in creating a migration. When I run the application and it's at the step to apply the migration, then I receive an exception:

System.InvalidOperationException: 'The property 'Point.Boundary' is of an interface type ('IGeometry'). If it is a navigation property manually configure the relationship for this property by casting it to a mapped entity type, otherwise ignore the property using the NotMappedAttribute or 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.'

var ctx = scope.ServiceProvider.GetService<DemoDbContext>();
ctx.Database.Migrate();

public class Location
    {
        public Guid Id { get; set; }
        public NetTopologySuite.Geometries.Point Position { get; set; }
    }

[DbContext(typeof(MissionNpgsqlDbContext))]
    partial class MissionNpgsqlDbContextModelSnapshot : ModelSnapshot
    {
        protected override void BuildModel(ModelBuilder modelBuilder)
        {
#pragma warning disable 612, 618
            modelBuilder
                .HasAnnotation("Npgsql:PostgresExtension:postgis", "'postgis', '', ''")
                .HasAnnotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.SerialColumn)
                .HasAnnotation("ProductVersion", "2.1.1-rtm-30846")
                .HasAnnotation("Relational:MaxIdentifierLength", 63);

            modelBuilder.Entity("Demo.Model.Set.Location", b =>
                {
                    b.Property<Guid>("Id")
                        .ValueGeneratedOnAdd();

                    b.Property<Point>("Position");

                    b.HasKey("Id");

                    b.ToTable("Locations");
                });
#pragma warning restore 612, 618
        }
    }

public partial class InitialCreate : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AlterDatabase()
                .Annotation("Npgsql:PostgresExtension:postgis", "'postgis', '', ''");

            migrationBuilder.CreateTable(
                name: "Locations",
                columns: table => new
                {
                    Id = table.Column<Guid>(nullable: false),
                    Position = table.Column<Point>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Locations", x => x.Id);
                });
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "Locations");
        }
    }

[roji]

@faryu can you please open an issue on http://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL/ with the above code sample?

[faryu]

I moved the question about using spatial data to the other repository.

The feature request, to be able to provide SQL segments which should be used in all SQL queries, remains open.

[ajcvickers]

Closing as a duplicate of #10861 with a reference there to consider this discussion.