Using NTS with Dapper

ConsoleApp.csproj

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp3.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Dapper" Version="2.0.4" />
    <PackageReference Include="NetTopologySuite.IO.SqlServerBytes" Version="2.0.0" />
    <PackageReference Include="System.Data.SqlClient" Version="4.7.0-preview9.19421.4" />
  </ItemGroup>

</Project>

Program.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
using Dapper;
using NetTopologySuite.Geometries;
using NetTopologySuite.IO;

class City
{
    public int CityID { get; set; }
    public string CityName { get; set; }
    public Point Location { get; set; }
}

class GeometryHandler<T> : SqlMapper.TypeHandler<T>
    where T : Geometry
{
    readonly bool _geography;
    readonly SqlServerBytesWriter _writer;
    readonly SqlServerBytesReader _reader;

    public GeometryHandler(bool geography = false)
    {
        _geography = geography;
        _writer = new SqlServerBytesWriter { IsGeography = geography };
        _reader = new SqlServerBytesReader { IsGeography = geography };
    }

    public override T Parse(object value)
        => (T)_reader.Read((byte[])value);

    public override void SetValue(IDbDataParameter parameter, T value)
    {
        parameter.Value = _writer.Write(value);

        ((SqlParameter)parameter).SqlDbType = SqlDbType.Udt;
        ((SqlParameter)parameter).UdtTypeName = _geography ? "geography" : "geometry";
    }
}

class Program
{
    static async Task Main()
    {
        SqlMapper.AddTypeHandler(new GeometryHandler<Point>(geography: true));

        var connection = new SqlConnection(
            @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=WideWorldImporters");

        await connection.OpenAsync();

        var nearestCity = await connection.QueryFirstAsync<City>(
            @"
                SELECT CityID, CityName, Location.Serialize() AS Location
                FROM Application.Cities
                ORDER BY Location.STDistance(@CurrentLocation)
            ",
            new
            {
                CurrentLocation = new Point(-122.128822, 47.643703) { SRID = 4326 }
            });

        Console.WriteLine($"{nearestCity.CityName} ({nearestCity.Location.Y}, {nearestCity.Location.X})");
    }
}

tnx man you are best

Works great, thanks! But did notice a slight performance hit having to call Serialize() - still much better than calling ToString() as we had before and working with the WKT! 👍

You saved my nerves =) Thank you!

Thanks @bricelam, @AndyMeps! This really made my day. I've been banging my head against this problem for a day. I had tried all sorts of variations and solutions - it would have never occurred to me to fiddle with the SQL statement and use Serialize()