Working With Data In An Inheritance Hierarchy

Dapper includes a feature for working with data that might map to different types from row to row. This feature is particularly useful when working with an inheritance hierarchy that uses the Table Per Hierarchy storage pattern, i.e. where one table is used to represent all classes in the hierarchy. A "discriminator" column is used to differentiate between types.

The following class definitions represent an inheritance hierarchy based on an abstract Contract type. Three derived types are defined too, representing different types of contracts offered by a communications/media business. The ContractType property acts as the discriminator.

public abstract class Contract
{
    public int ContractId { get; set; }
    public DateTime StartDate { get; set; }
    public int DurationMonths { get; set;}
    public decimal Charge { get; set; }
    public ContractType ContractType { get; set; }
    public int CustomerId { get; set; }
}

public class MobileContract : Contract
{
    public MobileContract() => ContractType = ContractType.Mobile;
    public string MobileNumber { get; set; }
}

public class TvContract : Contract
{
    public TvContract() => ContractType = ContractType.TV;
    public TVPackageType TVPackageType { get; set; }
}

public class BroadBandContract : Contract
{
    public BroadBandContract() => ContractType = ContractType.Broadband;
    public int DownloadSpeed { get; set; }
}

public enum TVPackageType
{
    S, M, L, XL
}

public enum ContractType
{
    Mobile = 1, TV, Broadband 
}

These classes are mapped to a single table with the following schema (SQLite):

Image

The following code shows how to use the ExecuteReader and GetRowsParser<T> methods to populate three collections based on the type of contract currently being read:

var tvContracts = new List<TvContract>();
var mobileContracts = new List<MobileContract>();
var broadbandContracts = new List<BroadbandContract>();

var sql = @"select * from contracts";
using (var connection = new SQLiteConnection(connString))
using (var reader = connection.ExecuteReader(sql))
{
    var tvContractParser = reader.GetRowParser<TvContract>();
    var mobileContractParser = reader.GetRowParser<MobileContract>();
    var broadbandContractParser = reader.GetRowParser<BroadbandContract>();
    
    while(reader.Read())
    {
        var discriminator = (ContractType)reader.GetInt32(reader.GetOrdinal(nameof(ContractType)));
        switch(discriminator)
        {
            case ContractType.TV:
                tvContracts.Add(tvContractParser(reader));
                break;
            case ContractType.Broadband:
                broadbandContracts.Add(broadbandContractParser(reader));
                break;
            case ContractType.Mobile:
                mobileContracts.Add(mobileContractParser(reader));
                break;
        }
    }
}
Console.WriteLine("TV Contracts");
tvContracts.ForEach(c => Console.WriteLine($"Duration: {c.DurationMonths} months, Package Type: {c.TVPackageType.ToString()}"));
Console.WriteLine("Broadband Contracts");
broadbandContracts.ForEach(c => Console.WriteLine($"Duration: {c.DurationMonths} months, Cost: {c.Charge}, Download: {c.DownloadSpeed} Mbps"));
Console.WriteLine("Mobile Contracts");
mobileContracts.ForEach(c => Console.WriteLine($"Duration: {c.DurationMonths} months, Number: {c.MobileNumber}"));

Image

Last updated: 21/05/2019 13:52:27