Datatable With Sqlgeography Column Can't Be Serialized To Xml Correctly With Loss Of Lat,long And Other Elements

I tried to serialize a DataTable object to xml. The DataTable has a column with type 'geography', which contains instances of type SqlGeography. The following code is used to seria

Solution 1:

DataTable.WriteXml() internally uses XmlSerializer to serialize complex column values. And since Lat and Long are both get-only, and SqlGeography doesn't implement IXmlSerializable, there's no way this is going to work, since XmlSerializer doesn't serialize get-only properties.

Instead, you're going to need to replace instances of SqlGeography with some appropriate data transfer object in order to serialize it. But, what should that DTO contain and how should it be created? There are several options:

1. Manually convert to and from GML using SqlGeography.AsGml() and SqlGeography.GeomFromGml().

   In this scenario, your DTO would look like something this:

   publicclassSqlGeographyDTO
   {
       constint DefaultSRID = 4326; // TODO: check if this is the correct default.publicint? STSrid { get; set; }
   
       public XElement Geography { get; set; }
   
       publicstaticimplicitoperatorSqlGeographyDTO(SqlGeography geography)
       {
           if (geography == null || geography.IsNull)
               returnnull;
           returnnew SqlGeographyDTO
           {
               STSrid = geography.STSrid.IsNull ? (int?)null : geography.STSrid.Value,
               Geography = geography.AsGml().ToXElement(),
           };
       }
   
       publicstaticimplicitoperatorSqlGeography(SqlGeographyDTO dto)
       {
           if (dto == null)
               return SqlGeography.Null;
           var sqlXml = dto.Geography.ToSqlXml();
           var geography = SqlGeography.GeomFromGml(sqlXml, dto.STSrid.GetValueOrDefault(DefaultSRID));
           return geography;
       }
   
       publicoverridestringToString()
       {
           return Geography == null ? "" : Geography.ToString(SaveOptions.DisableFormatting);
       }
   }
   
   publicstaticclassXNodeExtensions
   {
       publicstatic SqlXml ToSqlXml(this XNode node)
       {
           if (node == null)
               return SqlXml.Null;
           using (var reader = node.CreateReader())
           {
               returnnew SqlXml(reader);
           }
       }
   }
   
   publicstaticclassSqlXmlExtensions
   {
       publicstatic XElement ToXElement(this SqlXml sql)
       {
           if (sql == null || sql.IsNull)
               returnnull;
           using (var reader = sql.CreateReader())
               return XElement.Load(reader);
       }
   }
   

   Since GML is an XML-based format the result will be parsable by any XML parser. Note, however, that conversion from and to GML is documented not to be precise.

   Working .Net fiddle.

   (As an aside: AsGml() returns an object of type SqlXml which implements IXmlSerializable, so it would seem possible to include the returned SqlXml directly in the DTO. Unfortunately, testing reveals that either AsGml() or SqlXml.WriteXml() seem to have a bug: an XML declaration is always included, even when the XML is being written as a nested child element of an outer container element. Thus the resulting, serialized XML will be nonconformant and broken. Parsing to an intermediate XElement avoids this bug by stripping the unwanted declaration.)

2. Manually convert to and from Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation augmented with any Z (elevation) and M (measure) values by using ToString() with SqlGeography.STGeomFromText().

   In this implementation your DTO would look something like:

   publicclassSqlGeographyDTO
   {
       constint DefaultSRID = 4326; // TODO: check if this is the correct default.publicint? STSrid { get; set; }
   
       publicstring Geography { get; set; }
   
       publicstaticimplicitoperatorSqlGeographyDTO(SqlGeography geography)
       {
           if (geography == null || geography.IsNull)
               returnnull;
           returnnew SqlGeographyDTO
           {
               STSrid = geography.STSrid.IsNull ? (int?)null : geography.STSrid.Value,
               Geography = geography.ToString(),
           };
       }
   
       publicstaticimplicitoperatorSqlGeography(SqlGeographyDTO dto)
       {
           if (dto == null)
               return SqlGeography.Null;
           var geography = SqlGeography.STGeomFromText(new SqlChars(dto.Geography), dto.STSrid.GetValueOrDefault(DefaultSRID));
           return geography;
       }
   }
   

   Again this implementation might lose precision, but has the advantage of being usable with many formats and serializers including JSON and Json.NET. This seems to be the approach used by ServiceStack.OrmLite.SqlServer.Converters/SqlServerGeographyTypeConverter, for instance.

   Working .Net fiddle courtesy of @M.Hassan.

3. Manually convert from and to a byte [] binary representation using SqlGeography.Write(BinaryWriter) with SqlGeography.Read (BinaryReader).

   In this implementation your DTO class would contain public byte [] Geography { get; set; }.

   These methods are documented not to lose precision, but since the binary is opaque you won't be able to document your interchange format. This seems to be the approach used by Dapper.EntityFramework.DbGeographyHandler. Note however that Dapper is calling STAsBinary(), which, according to the documentation, returns a value that will not contain any Z or M values carried by the instance.

Once you have your DTO, you could replace it in your current DataTable using one of the answers from, e.g., How To Change DataType of a DataColumn in a DataTable?.

Note - above DTOs are not fully tested.