PetaPoco Fails to Pull Geography Records

Question

I'm using C#, .NET framework 4.5 (with intentions to upgrade to .NET 5), and PetaPoco as my ORM.

I have a table named Jurisdiction with the following field definition:

CREATE Table [Jurisdiction]
  ...
  [GeographicArea] [geography] NULL
  ...
);

In my database layer, I have the following:

var sql = @"
    SELECT
        Jurisdiction.*,
        State.StateName
    FROM
        Jurisdiction
        LEFT OUTER JOIN State ON Jurisdiction.StateId = State.StateId
";

if (where.Count > 0)
{
    sql += $" WHERE {string.Join(" AND ", where)}";
}

sql += orderBy;


var jurisdictions = _database.Query<T>(sql, parameters.ToArray()).ToList();

However, when this method runs I get the following exception:

'Could not load file or assembly 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.'

This was causing an issue when I let the database.tt file automatically generate the POCO definition for Jurisdiction so what I did to the tt file was add the following so that it would stop trying to automatically use the SqlServers.Geography type:

tables["Jurisdiction"]["GeographicArea"].PropertyType="string";

However, even with defining the field as a string, it is still throwing the exception, regardless of if I have the Microsoft.SqlServer.Types library added to the project or not.

How can I can not fool with the Microsoft.SqlServer.Types library using PetaPoco?

Answer 1

I was ultimately able to resolve my issue, though it was very much involved. In my query, instead of doing a SELECT * I had to spell out each column and manually convert the geography value using:

SELECT
    Jurisdiction.JurisdictionId,
    Jurisdiction.CreatedBy,
    Jurisdiction.CreatedOn,
    -- etc...
    CASE WHEN Jurisdiction.GeographicArea IS NULL THEN NULL ELSE Jurisdiction.GeographicArea.ToString() END AS GeographicArea -- CASE/WHEN/THEN/ELSE speeds up query
FROM
    Jurisdiction

Then in my view model, I set the insert and update templates using:

[Column(InsertTemplate = "geography::STMPolyFromText({0}{1}, 4326)", UpdateTemplate = "{0} = geography::STMPolyFromText({1}{2}, 4326)")] new public string GeographicArea { get; set; }

Finally in my service layer, I created the following two methods to get/update the geography column using:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;

namespace MyApplication
{
    public class Coordinate
    {
        private float _latitude;
        private float _longitude;

        public float Latitude {
            get => _latitude;
            set {
                if (value < -90 || value > 90)
                {
                    throw new ArgumentOutOfRangeException(nameof(Latitude), "The latitude is not between -90 and 90.");
                }
                _latitude = value;
            }
        }
        public float Longitude
        {
            get => _longitude;
            set
            {
                if (value < -180 || value > 180)
                {
                    throw new ArgumentOutOfRangeException(nameof(Longitude), "The longitude is not between -180 and 180.");
                }
                _longitude = value;
            }
        }

        public Coordinate()
        {
            Latitude = 0;
            Longitude = 0;
        }

        public Coordinate(string latitude, string longitude)
        {
            if (!float.TryParse(latitude, out float latitudeFloat))
            {
                throw new ArgumentException("Latitude must be a valid number.");
            }

            if (!float.TryParse(longitude, out float longitudeFloat))
            {
                throw new ArgumentException("Longitude must be a valid number.");
            }
            Latitude = latitudeFloat;
            Longitude = longitudeFloat;
        }

        public Coordinate(float latitude, float longitude)
        {
            Latitude = latitude;
            Longitude = longitude;
        }
    }

    public class SpatialConverterService
    {
        // find everything but ([^...]): numbers (\d), decimal points (\.), spaces (\s), and commas (,)
        private readonly static Regex _geographyIrrelevantData = new Regex(@"[^\d\.\s\-,]");

        /// <summary>
        /// Takes a SQL geography string and converts it to a collection of Coordinate values
        /// </summary>
        /// <param name="geography"><see cref="string"/> the SQL geography string</param>
        /// <returns><see cref="IEnumerable{Coordinate}"/> the collection of coordinates</returns>
        public static IEnumerable<Coordinate> ConvertSqlGeographyToCoordinates(string geography)
        {
            var geographyPoints = _geographyIrrelevantData.Replace(geography, string.Empty);
            geographyPoints = geographyPoints.Trim();

            var coordinateStrings = geographyPoints.Split(new[] { ',' });
            var coordinates = coordinateStrings.Select(coordinate =>
            {
                coordinate = coordinate.Trim();
                var points = coordinate.Split(new[] { ' ' });
                if (points.Count() != 2)
                {
                    throw new Exception($"Coordinate is not in a valid format, expecting longitude and latitude separated by a space but got: {coordinate}");
                }

                // SQL represents points as: lng lat
                return new Coordinate(points[1], points[0]);
            });

            return coordinates;
        }

        /// <summary>
        /// Takes a collection of <see cref="Coordinate"/> and converts it to a SQL geography string
        /// </summary>
        /// <param name="coordinates"><see cref="IEnumerable{Coordinate}"/> the collection of coordinates to convert</param>
        /// <returns><see cref="string"/> the SQL geography string</returns>
        public static string ConvertCoordinatesToSqlGeography(IEnumerable<Coordinate> coordinates)
        {
            if (!coordinates.Any())
            {
                throw new ArgumentNullException(nameof(coordinates), "There are no coordinates in the collection.");
            }

            var sqlConversion = string.Join(", ", coordinates.Select(coordinate => $"{coordinate.Longitude} {coordinate.Latitude}"));
            if (coordinates.First() != coordinates.Last() || coordinates.Count() == 1)
            {
                // SQL requires that the geography get completed by ending on the first coordinate
                var firstCoordinate = coordinates.First();
                sqlConversion += $", {firstCoordinate.Longitude} {firstCoordinate.Latitude}";
            }
            
            var multipolygon = $"MULTIPOLYGON((({sqlConversion})))";
            return multipolygon;
        }
    }
}