Retrieving XML from database with Dapper

Question

I am using Dapper to query a table that includes an XML field:

CREATE TABLE Workflow
(
    Guid uniqueidentifier not null,
    State xml not null
)

which is then mapped to a property of type XDocument:

public class Workflow
{
    public Guid InstanceId { get;set; }
    public XDocument State { get;set; }
}

but when I try to query the table, I get the following error:

Error parsing column 1 (State= - String)
   at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) in d:\\Dev\\dapper-dot-net\\Dapper NET40\\SqlMapper.cs:line 4045
   at Deserialize038b29f4-d97d-4b62-b45b-786bd7d50e7a(IDataReader )
   at Dapper.SqlMapper.<QueryImpl>d__11`1.MoveNext() in d:\\Dev\\dapper-dot-net\\Dapper NET40\\SqlMapper.cs:line 1572
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in d:\\Dev\\dapper-dot-net\\Dapper NET40\\SqlMapper.cs:line 1443
   at MyProject.DapperBase.Query[TResult](String command, DynamicParameters parameters, IDbTransaction transaction, Boolean buffered, Int32 commandTimeout) in d:\\MyProject\\DapperBase.cs:line 122
   at MyProject.WorkflowData.Get(Guid identifier) in d:\\MyProject\\WorkflowData.cs:line 41
   at MyProject.WorkflowLogic.Save(Workflow workflow) in d:\\MyProject\\WorkflowLogic.cs:line 34
   at MyProject.WorkflowsController.Save(Guid id, WorkflowRequest request) in d:\\MyProject\\WorkflowsController.cs:line 97

InnerException: Invalid cast from 'System.String' to 'System.Xml.Linq.XDocument'.
    at System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider)at System.String.System.IConvertible.ToType(Type type, IFormatProvider provider)
    at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
    at System.Convert.ChangeType(Object value, Type conversionType)
    at Deserialize038b29f4-d97d-4b62-b45b-786bd7d50e7a(IDataReader )

Other than modifying my POCO to use a string datatype and then convert the string into an XDocument elsewhere, is there a way of getting Dapper to correctly deserialise the XML from the database?

Answer 1

In the end, I just brute-forced it:

public class Workflow
{
    public Guid InstanceId { get;set; }
    public XDocument StateIn { set { State = value.ToString(); } }
    public string State { get;set; }
    public XDocument StateOut { get { return XDocument.Parse(State); } }
}

Dapper plays with the State value, and I just set the value on StateIn and read it off StateOut. I feel a little bit dirty coming up with a solution like this, but hey, it works.

Answer 2

Perhaps creating a custom type handler can help? Something like:

public class XDocumentTypeHandler : SqlMapper.TypeHandler<XDocument>
{
    public override void SetValue(IDbDataParameter parameter, XDocument value)
    {
       // set value in db parameter.
    }

    public XDocument Parse(object value)
    {
       // parse value from db to an XDocument.
    }
}

You have to add the type handler with SqlMapper.AddTypeHandler().

See a sample implementation.