I'm not sure this is the right spot for the information below, it seemed strongly related so wasn't sure a new issue would actually be helpful. Happy to move, edit or change this as needed, let me know.

Querying a point from SpatiaLite with a Z value returns an entity with the expected values for X and Y but with NaN for the Z value even when there is a value with either EF 2.2 or EF Preview 3.

To reproduce:
-Construct an entity with an IPoint with .ForSqliteHasDimension(Ordinates.XYZ)
-Have EF construct the database
-Insert a point via ExecuteSqlCommand using GeomFromText('POINT Z(... As mentioned in #14257 I don't believe you can currently add and save an XYZ point via EF's .Add()
-Query the point thru EF
-The returned entity has the expected X and Y values but the Z returns incorrectly as NaN

The console program below shows the problem and also uses a .FromSql query to attempt to verify that the problem is in the EF Query return not the column properties or point.

using System;
using System.Linq;
using GeoAPI.Geometries;
using Microsoft.EntityFrameworkCore;
using NetTopologySuite;
using NetTopologySuite.Geometries;

namespace ConsoleTest
{
    public class RawSqlXyzPoint
    {
        public int Id { get; set; }
        public string Note { get; set; }
        public double X { get; set; }
        public double Y { get; set; }
        public double Z { get; set; }
    }

    public class SpatialTestContext : DbContext
    {
        public DbQuery<RawSqlXyzPoint> RawXyzQuery { get; set; }
        public DbSet<Xyz> Xyzs { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.EnableSensitiveDataLogging();
            optionsBuilder.UseSqlite("Filename=./Xyz.sqlite", x => x.UseNetTopologySuite());
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Xyz>().Property(c => c.XyzPoint)
                .ForSqliteHasSrid(4326);
            modelBuilder.Entity<Xyz>().Property(c => c.XyzPoint)
                .ForSqliteHasDimension(Ordinates.XYZ);
        }
    }

    public class Xyz
    {
        public int Id { get; set; }
        public string Note { get; set; }
        public IPoint XyzPoint { get; set; }
    }

    internal class Program
    {
        private static void Main(string[] args)
        {
            using (var context = new SpatialTestContext())
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();

                var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);

                Console.WriteLine("Create New Xyz Point");

                var xyzPoint = geometryFactory.CreatePoint(new Coordinate(-122.121512, 47.6739882, 1000));

                //Insert the point via ExecuteSql - saving a point XYZ Point via EF currently throws -
                //https://github.com/aspnet/EntityFrameworkCore/issues/14257

                context.Database.ExecuteSqlCommand("INSERT INTO Xyzs (Note, XyzPoint) " +
                                                   $"VALUES('Manual Insert', GeomFromText('POINT Z({xyzPoint.X} {xyzPoint.Y} {xyzPoint.Z})', 4326));");

                var manualTestPointEf = context.Xyzs.Single(x => x.Note == "Manual Insert");

                Console.WriteLine($"Manual XYZ Point EF Return - X: {manualTestPointEf.XyzPoint.X}");
                Console.WriteLine($"Manual XYZ Point EF Return - Y: {manualTestPointEf.XyzPoint.Y}");
                Console.WriteLine($"Manual XYZ Point EF Return - Z: {manualTestPointEf.XyzPoint.Z}");

                Console.WriteLine($"Manual XYZ Point EF Return - X OK: {manualTestPointEf.XyzPoint.X == xyzPoint.X}");
                Console.WriteLine($"Manual XYZ Point EF Return - Y OK: {manualTestPointEf.XyzPoint.Y == xyzPoint.Y}");
                Console.WriteLine($"Manual XYZ Point EF Return - Z OK: {manualTestPointEf.XyzPoint.Z == xyzPoint.Z}");

                var fromSqlReturn = context.RawXyzQuery.FromSql(
                    "SELECT Id, Note, X(XyzPoint) as X, Y(XyzPoint) as Y, Z(XyzPoint) as Z FROM Xyzs Where Note = 'Manual Insert'").Single();

                Console.WriteLine($"Manual XYZ Point FromSql Return - X: {fromSqlReturn.X}");
                Console.WriteLine($"Manual XYZ Point FromSql Return - Y: {fromSqlReturn.Y}");
                Console.WriteLine($"Manual XYZ Point FromSql Return - Z: {fromSqlReturn.Z}");

                Console.WriteLine($"Manual XYZ Point FromSql Return - OK: {fromSqlReturn.X == xyzPoint.X}");
                Console.WriteLine($"Manual XYZ Point FromSql Return - OK: {fromSqlReturn.Y == xyzPoint.Y}");
                Console.WriteLine($"Manual XYZ Point FromSql Return - OK: {fromSqlReturn.Z == xyzPoint.Z}");

                Console.ReadLine();
            }
        }
    }
}

Sqlite Spatial: Test Z and M #13767

Description

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions