Retrieve spatial data from SQL Server to ASP.NET Core 7 using C# and NetTopologySuite

Question

What is the correct way to retrieve spatial data from SQL Server and use it in my C# code?

I am using the aspnetzero Angular web framework with the following related packages:

• net7.0
• Microsoft.SqlServer.Types Version 160.1000.6
• NetTopologySuite version 2.5.0
• NetTopologySuite.IO.GeoJSON version 4.0.0.
• NetTopologySuite.IO.SqlServerBytes version 2.1.0
• Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite version 7.0.10

I am using SQL Server 2022 and IIS for development on localhost. I have confirmed that the geometry values stored in SQL Server are in the correct geometry format.

I have an entity called CoordinatesTable with the following structure:

public class CoordinatesTable : Entity<int>
{
    public int LocationId { get; set; }

    [Column(TypeName = "geometry")]
    public Point Coordinates { get; set; }
}

The SQL Server counterpart has the following structure:

CREATE TABLE [dbo].[CoordinatesTable] 
(
    [Id] int,
    [LocationId] int,
    [Coordinates] geometry,
    PRIMARY KEY ([Id])
)

My DbContext - OnModelCreating method looks like this:

modelBuilder.Entity<CoordinatesTable>(l =>
{
    l.Property(e => e.Coordinates)
        .HasColumnType("geometry")
        .HasColumnName("Coordinates")
        .HasConversion(
            v => v.AsText(),
            v => new WKTReader().Read(v) as Point
        );
});

My Startup.cs - ConfigureServices method has this code:

services.AddDbContext<ERADbContext>(opts =>
{
    opts.UseSqlServer(_appConfiguration.GetConnectionString("Default"),
        x => x.UseNetTopologySuite()
    );
});

The end goal is to retrieve the coordinates from the server and send them to the angular client as GeoJSON, but I can not seem to get past the initial data retrieval.

If I call this service layer method:

public List<CoordinatesTable> GetAllCoordinates()
{
    List<CoordinatesTable> coordinatesList = _dbContext.CoordinatesTable.ToList();
    return coordinatesList;
}

I get this server error

GenericArguments[0], 'System.String', on 'Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerGeometryTypeMapping[TGeometry]' violates the constraint of type 'TGeometry'.

If I attempt to retrieve data with this method:

public async Task LogCoordinatesPropertyAsync(int locationId)
{
    var coordinates = await _coordinatesRepository
        .GetAll()
        .Where(fe => fe.LocationId == locationId)
        .Select(fe => new CoordinatesDto
        {
            LocationId = fe.LocationId,
            Coordinates = fe.Coordinates
        })
        .FirstOrDefaultAsync();
  }

I get this server error:

ERROR 2024-02-11 11:16:04,085 [24 ]
Microsoft.EntityFrameworkCore.Query - An exception occurred while iterating over the results of a query for context type 'ERA.EntityFrameworkCore.ERADbContext'.
System.InvalidCastException: unable to cast object of type 'System.Byte[]' to type 'System.String'.

at Microsoft.Data.SqlClient.SqlBuffer.get_String()
at lambda_method4200(Closure, QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()

I have tried dozens of other method formations, but I get one of the two errors above.

I have also tried several different DbContext conversions, but none have worked

.HasConversion(
   g => new WKBWriter().Write(g), // Convert Geometry to WKB (byte[])
   b => new WKBReader().Read(b) // Convert WKB back to Geometry
);


.HasConversion(
    g => new SqlServerBytesReader().Read(g.AsBinary()),
    b => new WKBReader().Read(b)
);


.HasConversion(
    g => new SqlServerBytesReader().Read(g.AsBinary().Buffer),
    b => new WKBReader().Read(b)
);


.HasConversion(
    g => new SqlServerBytesReader().Read(g.STAsBinary().Buffer),
    b => new WKBReader().Read(b)
);

.HasConversion(
    g => g.AsBinary(), // Convert Geometry to binary representation
    b => Microsoft.SqlServer.Types.SqlGeometry.STGeomFromWKB(new System.Data.SqlTypes.SqlBytes(b), 0) // Convert binary back to Geometry
    );

.HasConversion(
    g => g.AsBinary(), // Convert Geometry to binary representation
    b => Microsoft.SqlServer.Types.SqlGeometry.STGeomFromWKB(b, 0) // Convert binary back to Geometry

.HasConversion(
    v => new NetTopologySuite.IO.WKTWriter().Write(v),
    v => new WKTReader().Read(v) as Geometry);

.HasConversion(
    g => g.ToText(),  // Convert Geometry to WKT (Well-Known Text)
    wkt => new NetTopologySuite.IO.WKTReader().Read(wkt) as Geometry);

Answer 1

The tool you are using Entity Framework Core with NetTopologySuite will automatically handle spatial data so you do not need to do all the manual data n trying. When you want to get your spatial data and send it over to your Angular app, use the tools provided by NetTopologySuite to convert your data into a format called GeoJSON. you could try this code:

public async Task<string> GetCoordinatesAsGeoJsonAsync()
{
    var coordinatesList = await _dbContext.CoordinatesTable.ToListAsync();

    var serializer = new NetTopologySuite.IO.GeoJSON.GeoJsonSerializer();

    using (var stringWriter = new StringWriter())
    using (var jsonWriter = new JsonTextWriter(stringWriter))
    {
        serializer.Serialize(jsonWriter, coordinatesList.Select(c => c.Coordinates).ToList());
        return stringWriter.ToString();
    }
}