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?

Philtre

github.com/StackExchange/dapper-dot-net/issues/262 – Philtre

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.

Chungchungking

Awesome! Thanks for this :) Even if I was using TPT inheritance with separate tables, you'd still have a problem querying abstract types. I've created an issue on github if you wanted to post this there too: github.com/StackExchange/dapper-dot-net/issues/262 – Philtre

Yes you'd still have a problem with the abstract types, and the performance hit with the above code may be negligible if you have a small dataset. – Chungchungking

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;
}

Transportation

+1 great thanks for this. Presumably this would be fairly inefficient when dealing with a large dataset but it's good to have something here all the same. – Philtre

Answer 3

Created generic dapper extension method to query class hierarchy per table. Perhaps would be usefull for someone.

    public static async Task<IEnumerable<TValue>> QueryHierarchyAsync<TValue, TKey>(
        this IDbConnection connection,
        CommandDefinition command,
        string discriminator,
        Func<TKey, Type> typeProvider)
    {
        int discriminatorIndex = -1;
        var parsers = new Dictionary<TKey, Func<IDataReader, TValue>>();

        var result = new List<TValue>();

        using (var reader = await connection.ExecuteReaderAsync(command))
        {
            while (reader.Read())
            {
                if (discriminatorIndex < 0) discriminatorIndex = reader.GetOrdinal(discriminator);
                var objectValue = reader.GetValue(discriminatorIndex);
                if (!(objectValue is TKey value))
                    throw new Exception($"Discriminator value is not assignable to '{typeof(TKey).Name}'");

                if (!parsers.TryGetValue(value, out var parser))
                {
                    var type = typeProvider(value);
                    if (type == null)
                        throw new Exception($"Type for discriminator value '{value}' was not found");

                    if (!typeof(TValue).IsAssignableFrom(type))
                        throw new Exception($"Type '{type.Name}' is not assignable from '{typeof(TValue).Name}'");

                    parser = reader.GetRowParser<TValue>(type);
                    parsers.Add(value, parser);
                }

                result.Add(parser(reader));
            }
        }

        return result;
    }

Touslesmois

Can you add an example of how to call the method, specifically what the typeProvider looks like. – Teenybopper

Answer 4

May 2022 : Updated with improved code with type-caching and optional base class.

For a similar problem in EFCore - How to Automatically Map TPH Derived Classes in EF Core? , I came up with this extension method that gets the derived sub-classes of a (usually abstract) class.

public static Type[] GetDerivedTypes(this Type type, 
                                          string[] ignoreTypeNames = null, 
                                          bool includeBaseType = false, 
                                          Assembly otherAssembly = null)
{
   ignoreTypeNames = ignoreTypeNames ?? Array.Empty<string>();
   
   var assembly = otherAssembly ?? Assembly.GetAssembly(type);

   var types =  assembly
                .GetTypes()
                .Where (t => t.IsSubclassOf(type) && 
                       (!ignoreTypeNames?.Any(t.Name.Contains) ?? false) )
                .ToList();

   if (includeBaseType)
       types.Add(type);

   return types.OrderBy(o => o.Name).ToArray();
}

Given a list of sub-types derived from a base type, you can build a dictionary of all sub-type parsers for the base class. Here is an extension method that brings back the typed records for any sub-type without manually having to map them. For my rules engine, this was a much simpler solution.

public static List<T> MapSubClassesOf<T>( this IDataReader reader, 
                                               Assembly assembly,
                                               string discriminator = "Discriminator",  
                                               bool includeBaseType = false )
{
    var list                = new List<T>();
    var derivedTypes        = typeof(T).GetDerivedTypes(includeBaseType: includeBaseType, otherAssembly: assembly); 
    var parsers             = new Dictionary<string, Func<IDataReader, T>>();

    while (reader.Read())
    {
        string typeName = reader.GetString(reader.GetOrdinal(discriminator));

        if (!parsers.ContainsKey(typeName))
        {
            Type derivedType = derivedTypes.Where(w => w.Name == typeName).FirstOrDefault();

            if (derivedType != null)
            {
                parsers.Add(typeName, reader.GetRowParser<T>(derivedType));
            }
        }
        else
        {
            throw new Exception($"The type {typeName} does not exist in the database.");
        }

        if (parsers.TryGetValue(typeName, out Func<IDataReader, T> parser))
        {
            var subType = parser(reader);

            list.Add(subType);
        }
    }
    return list;
}

Here is the code to call it. It will throw the exception above if the Discriminator in the table does not exist in the derived classes.

string sql = @"SELECT SymbolRuleId, SortOrder, RuleGroup,
                      Discriminator, Description
               FROM   SymbolRules";

using (var reader = GetConnection().ExecuteReader(sql) )
{
    return reader.MapSubClassesOf<SymbolRule>();
}

Teenybopper