Querying abstract models in dapper

c# dapper inheritance table-per-hierarchy


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?

Popular Answer

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:

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

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why