The problem was not in Microsoft.SqlServer.Types as this answer previously stated. It was caused by Esri changing the ID it uses for the projected coordinate system my software deals with routinely.
DbGeometry instances created with wrong SRID
The method below is supposed to create an instance of DbGeometry that reflects the input SqlGeometry. My application and the data behind it work solely with SRID 102605 (this is a custom projection), but all of the static creational methods on the DbGeometry class I've tried have always yielded objects with SRID = 8826 and so the method below throws the exception commemorating this act. All of those creational methods use DbSpatialServices to do their work, and logging typeof(DbSpatialServices).Assembly.FullName yields: "EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089", which contains two implementations of DbSpatialServices. How can I convince those implementations that 102605 means 102605?
public static DbGeometry ToDbGeometry(this SqlGeometry thisSqlGeometry)
{
var dbGeometry = DbGeometry.FromBinary(
thisSqlGeometry.STAsBinary().Buffer,
thisSqlGeometry.STSrid.Value); // Hard-coded values here have no effect. All values of dbGeometry.CoordinateSystemId below are 8826.
if (dbGeometry.CoordinateSystemId != thisSqlGeometry.STSrid)
throw new Exception($"Cannot set DbGeometry's SRID properly -- {thisSqlGeometry.STSrid.Value} vs {dbGeometry.CoordinateSystemId}");
return dbGeometry;
}
This is an extension method used in an extension to Esri ArcMap that (among other things) manages native SQL geometries in our SQL Server databases (MS SQL 2017). The SqlGeometry objects being converted may have been created two different ways:
- geometries stored in the aforementioned databases, in which case the objects were created from the database by Entity Framework as DbGeometry instances and converted in a similar but opposite extension method to the one shown above
- geometries drawn in ArcMap, in which case they were converted to a SqlGeometry by a method similar to this.
Either way the SqlGeometry is created, the results of conversion to DbGeometry are the same--CoordinateSystemId is incorrectly assigned to 8826. The extension method shown above is used in lots of contexts. One of them is in preparing geometries to be used as parameters in spatial queries via Entity Framework as below:
private IList<IQuarterQuarter> GetQuarterQuartersFromGeometry2(SqlGeometry geometry, bool aggregateLots)
{
using (var plsEntities =
(IPublicLandSurveyEntities) _dataAccessStrategy.CreateContext<IPublicLandSurveyEntities>(
_configProvider))
{
if (geometry is null) return new List<IQuarterQuarter>();
var dbGeometry = geometry.ToDbGeometry();
_log.Info($"GetQQBlah dbGeom CoordSysId: {dbGeometry.CoordinateSystemId}"); // This indicates 8826
_log.Info($"GetQQBlah sqlGeom CoordSysId: {geometry.STSrid}"); // This indicates 102605
var locQqList = plsEntities.locPLSQQs // This DbSet of course comes from our database and all geometries therein have SRID = 102605
.Where(
qq => qq.SHAPE.Intersects(dbGeometry) // TODO: This filtering yields nothing because of the SRID mismatch
)
.ToList();
var qqList = locQqList
.Select(ToQuarterQuarter)
.ToList();
return aggregateLots
? AggregateSimilarQQs(qqList).ToList()
: qqList;
}
}