Datatable with SqlGeography column can't be serialized to xml correctly with loss of Lat,Long and other elements

Question

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 serialize the datatable to xml:

            var writer = new StringWriter();             
            dt.WriteXmlSchema(writer); //get schema
            //serialize to xml
            dt.WriteXml(writer);
            Console.WriteLine(writer.ToString());

The generated xml string is not complete with the loss of all Geo elements Lat,Long,...

It contains only STSrid element.

The following is the generated xml string:

        <table>
            <f1 xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
              <STSrid>4326</STSrid>
            </f1>
            <id>1</id>
          </table>
          <table>
            <f1 xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
              <STSrid>4326</STSrid>
            </f1>
            <id>2</id>
          </table>

This means that you can't use the xml for deserialization, and you loose the SqlGeography data.

The schema is generated correctly:

        <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
          <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="table" msdata:UseCurrentLocale="true">
            <xs:complexType>
              <xs:choice minOccurs="0" maxOccurs="unbounded">
                <xs:element name="table">
                  <xs:complexType>
                    <xs:sequence>
                      <xs:element name="f1" msdata:DataType="Microsoft.SqlServer.Types.SqlGeography, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" type="xs:anyType" minOccurs="0" />
                      <xs:element name="id" type="xs:int" minOccurs="0" />
                    </xs:sequence>
                  </xs:complexType>
                </xs:element>
              </xs:choice>
            </xs:complexType>
          </xs:element>
        </xs:schema>  

You can find the complete C# program online to show the problem.

My Question:

1) What I missed to get a valid xml serialization for SqlGeography column?

2) Is't a bug in the DataTable.WriteXml method to handle a complex object?

Answer 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:

   public class SqlGeographyDTO
   {
       const int DefaultSRID = 4326; // TODO: check if this is the correct default.
   
       public int? STSrid { get; set; }
   
       public XElement Geography { get; set; }
   
       public static implicit operator SqlGeographyDTO(SqlGeography geography)
       {
           if (geography == null || geography.IsNull)
               return null;
           return new SqlGeographyDTO
           {
               STSrid = geography.STSrid.IsNull ? (int?)null : geography.STSrid.Value,
               Geography = geography.AsGml().ToXElement(),
           };
       }
   
       public static implicit operator SqlGeography(SqlGeographyDTO dto)
       {
           if (dto == null)
               return SqlGeography.Null;
           var sqlXml = dto.Geography.ToSqlXml();
           var geography = SqlGeography.GeomFromGml(sqlXml, dto.STSrid.GetValueOrDefault(DefaultSRID));
           return geography;
       }
   
       public override string ToString()
       {
           return Geography == null ? "" : Geography.ToString(SaveOptions.DisableFormatting);
       }
   }
   
   public static class XNodeExtensions
   {
       public static SqlXml ToSqlXml(this XNode node)
       {
           if (node == null)
               return SqlXml.Null;
           using (var reader = node.CreateReader())
           {
               return new SqlXml(reader);
           }
       }
   }
   
   public static class SqlXmlExtensions
   {
       public static XElement ToXElement(this SqlXml sql)
       {
           if (sql == null || sql.IsNull)
               return null;
           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:

   public class SqlGeographyDTO
   {
       const int DefaultSRID = 4326; // TODO: check if this is the correct default.
   
       public int? STSrid { get; set; }
   
       public string Geography { get; set; }
   
       public static implicit operator SqlGeographyDTO(SqlGeography geography)
       {
           if (geography == null || geography.IsNull)
               return null;
           return new SqlGeographyDTO
           {
               STSrid = geography.STSrid.IsNull ? (int?)null : geography.STSrid.Value,
               Geography = geography.ToString(),
           };
       }
   
       public static implicit operator SqlGeography(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.