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?
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;
}
}
}