Querying abstract models in dapper

Question

I'm using Table Per Hierarchy database inheritance where columns for all derived types are in a single table. Each derived table is identified using a string Discriminator field that holds the name of the derived class:

---------------------
| tanimal           |
---------------------
| animalid          |
| discriminator     |
| furcolour         |
| feathercolour     |
---------------------

public abstract class Animal
{
    public int AnimalId { get; set; }
    public string Discriminator { get { return GetType().Name; } }
}

public class Bird : Animal
{
    public string FeatherColour { get; set; }
}

public class Dog : Animal
{
    public string FurColour { get; set; }
}

As expected, when retrieving this via Dapper's query method I receive Instances of abstract classes cannot be created. I would hope that this would return a list of Animal with their values being the respective derived types.

var animals = Connection.Query<Animal>("SELECT * FROM tanimal")

My attempts to add support for this have been unsuccessful. Before SqlMapper.cs::GetTypeDeserializer() is called if the type being passed in is an abstract class then I replace the type with the one returned in the following method:

static Type GetDerivedType(Type abstractType, IDataReader reader)
{
    var discriminator = abstractType.GetProperty("Discriminator");
    if (discriminator == null)
        throw new InvalidOperationException("Cannot create instance of abstract class " + abstractType.FullName + ". To allow dapper to map to a derived type, add a Discriminator field that stores the name of the derived type");

    return Type.GetType((string)reader["Discriminator"]);
}

However it looks like at this point the reader hasn't been opened so it fails with Invalid attempt to read when no data is present.

Is this the correct approach to take? Has there been any effort to support this elsewhere?

Answer 1

You can make this work but it will be less efficient than using Dapper's default behaviour with separate tables.

GetDeserializer needs to be called for every row, which means it needs to happen inside while (reader.Read())

By modifying QueryImpl<T> you can achieve the result you want. Assuming you're getting the results with:

var results = connection.Query<Animal>("SELECT * FROM tanimal");

Then the beginning of the try {} block of QueryImpl<T> will be:

try
{
cmd = command.SetupCommand(cnn, info.ParamReader);

if (wasClosed) cnn.Open();

// We can't use SequentialAccess any more - this will have a performance hit.
reader = cmd.ExecuteReader(wasClosed ? CommandBehavior.CloseConnection : CommandBehavior.Default);
wasClosed = false; 

// You'll need to make sure your typePrefix is correct to your type's namespace
var assembly = Assembly.GetExecutingAssembly();
var typePrefix = assembly.GetName().Name + ".";

while (reader.Read())
{
    // This was already here
    if (reader.FieldCount == 0) //https://code.google.com/p/dapper-dot-net/issues/detail?id=57
        yield break;

    // This has been moved from outside the while
    int hash = GetColumnHash(reader);

    // Now we're creating a new DeserializerState for every row we read 
    // This can be made more efficient by caching and re-using for matching types
    var discriminator = reader["discriminator"].ToString();
    var convertToType = assembly.GetType(typePrefix + discriminator);

    var tuple = info.Deserializer = new DeserializerState(hash, GetDeserializer(convertToType, reader, 0, -1, false));
    if (command.AddToCache) SetQueryCache(identity, info);

    // The rest is the same as before except using our type in ChangeType
    var func = tuple.Func;

    object val = func(reader);
    if (val == null || val is T)
    {
        yield return (T)val;
    }
    else
    {
        yield return (T)Convert.ChangeType(val, convertToType, CultureInfo.InvariantCulture);
    }
}
// The rest of this method is the same

This will make the method work only with the discriminator field, so you may want to create your own QueryImpl<T> if you need this to work normally with other queries. Also I can't guarantee this will work in every case, only tested with two rows, one of each type - but this should be a good starting point.

Answer 2

I want to share my solution as well. Inputs:

C#

abstract class Stock {}
class Bond: Stock {}
class Equity : Stock {}

SQL

CREATE TABLE [dbo].[Stocks] (
....some columns....
    [Descriminator] VARCHAR (100) NOT NULL,
);

In SQL I have a Descriminator column which determines C# type for each row "Equity" or "Bond". Basicaly, this is a standard implementation if Table-Per-Hierarchy strategy.

I used Dapper's paremeter-less Query syntax

connection.Query(sql); 

to get a dynamic object which Dapper sees as DapperRow. Although DapperRow is a private class, it implements IDictionary<string, object>. String - name of a property, Object - properties value.

Function Convert IDictionary<string, object> to class (strongly typed):

public static T GetObject<T>(IDictionary<string, object> dict)
{
    Type type = typeof(T);
    var obj = Activator.CreateInstance(type);

    foreach (var kv in dict)
    {
        type.GetProperty(kv.Key).SetValue(obj, kv.Value);
    }
    return (T)obj;
}

And Mapper between descriminator column and C# class:

public static Stock ConvertToStock(object value)
{
    var dapperRowProperties = value as IDictionary<string, object>;
    switch (dapperRowProperties["Descriminator"])
    {
        case "Bond":
            return GetObject<Bond>(dapperRowProperties);
        case "Stock":
            return GetObject<Stock>(dapperRowProperties);
        default:
            return null;
    }
}

Usage of convertor:

public Stock GetStock(int id)
{
    Stock stock;
    var sql = "select * from Stocks where Id = @id";
    using (var connection = ConnectionFactory.GetOpenConnection())
    {
        stock = connection.Query(sql, new { id }).Select(ConvertToStock).Single();
    }
    return stock;
}