SqlGeography does not implement IComparable interface, preventing DataSet.RejectChanges() from succeeding

Question

I'm working on an Enterprise application that has an SQL backend, and uses System.Data.DataSet etc to represent that data in C#.

Previously we turned the SqlGeography into DbGeography before putting it in our DataTables, and everything worked, until we found that doing this roundtrip for certain floating point values had a tiny imprecision that actually effected the underlying bytes, so we have to ditch DbGeography and keep it as SqlGeography the entire time. This MOSTLY works, except for a show stopper. dataSet.RejectChanges() now doesn't work because of the following exception:

------------System.Data.DataException: Type 'Microsoft.SqlServer.Types.SqlGeography, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' does not implement IComparable interface. Comparison cannot be done.
at System.Data.Common.SqlUdtStorage.CompareValueTo(Int32 recordNo1, Object value)at System.Data.Index.CompareRecords(Int32 record1, Int32 record2)
at System.Data.RBTree`1.RBInsert(Int32 root_id, Int32 x_id, Int32 mainTreeNodeID, Int32 position, Boolean append)
at System.Data.Index.InitRecords(IFilter filter)
at System.Data.DataTable.GetIndex(IndexField[] indexDesc, DataViewRowState recordStates, IFilter rowFilter)
at System.Data.DataColumn.get_SortIndex()
at System.Data.DataColumn.IsNotAllowDBNullViolated()
at System.Data.DataSet.EnableConstraints()
at System.Data.DataSet.set_EnforceConstraints(Boolean value)
at System.Data.DataSet.RejectChanges()
at CargoWise.EntityFramework.RowFactory.Rollback() in 

I checked how this used to work for DbGeography. It worked because since it wasn't an SQL type (didn't implement INullable, see System.Data.Common.DataStorage CreateStorage) it created an ObjectStorage instead of an SqlUdtStorage. In the case of data types that don't implement IComparable, ObjectStorage would eventually resort to doing .ToString() to compare them. And in this case, this was good enough, since we just needed to know 'null or not null?' for IsNotAllowDBNullViolated() to work (as far as I know there were no defects caused by this, anyway). But SqlUdtStorage has no fallback if IComparable is not implemented - it throws an exception.

This really feels like one of those cases where 'how did this ever work, for anyone, ever?' and 'how has no one else in the history of google run into this error, ever?'. Anyway, I'm considering my choices, and so far things look bleak.

I can't subclass SqlUdtStorage, it's internal sealed. Nor can I subclass SqlGeography, it's internal sealed. I could make a thin wrapper around SqlGeography just to implement IComparable and use that as the DataColumn's type instead of SqlGeography, and I THINK that would work. I'm also considering the possibility of, whenever I make a DataColumn with SqlGeography type (would have to find all the places in the codebase), using reflection to construct an ObjectStorage with the right parameters and assign it to DataColumn._storage. I could simply have all SqlGeography columns be nullable, but I'm not sure how easy this would be/if it would cause any defects.

But all these ideas share something in common - they feel way too hacky to be the intended way to solve this problem. IS there an intended way? If not, what is the cleanest workaround?

Answer 1

I ended up using the 'change the _storage field via reflection to ObjectStorage' solution. Code as follows. Note that it has to happen AFTER you add the DataColumn to the DataTable, because the act of doing so nulls out _storage.

destTable.Columns.Add(dataColumn);
//hack to fix https://stackoverflow.com/questions/73875363/sqlgeography-does-not-implement-icomparable-interface-preventing-dataset-reject
if (sourceSchema.DotNetType == typeof(SqlGeography))
{
  var storageField = typeof(DataColumn).GetField("_storage", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance);
  var objectStorageType = typeof(DataColumn).Assembly.GetType("System.Data.Common.ObjectStorage");
  var objectStorageConstructor = objectStorageType.GetConstructor(System.Reflection.BindingFlags.NonPublic | BindingFlags.Instance, null, new Type[2] { typeof(DataColumn), typeof(Type) }, null);
  var objectStorageInstance = objectStorageConstructor.Invoke(new object[2] { dataColumn, typeof(SqlGeography) });
  storageField.SetValue(dataColumn, objectStorageInstance);
}