Normalise flat table into dictionary

Question

I've modified a table shown below

ALTER TABLE [Plant]
  ADD
    Name nvarchar(128) NULL,
    GLN nvarchar(15) NULL,
    LicenceInformation nvarchar(1024) NULL,
    MondayStart smallint NULL,
    MondayEnd smallint NULL,
    TuesdayStart smallint NULL,
    TuesdayEnd smallint NULL,
    WednesdayStart smallint NULL,
    WednesdayEnd smallint NULL,
    ThursdayStart smallint NULL,
    ThursdayEnd smallint NULL,
    FridayStart smallint NULL,
    FridayEnd smallint NULL,
    SaturdayStart smallint NULL,
    SaturdayEnd smallint NULL,
    SundayStart smallint NULL,
    SundayEnd smallint NULL

Which I'd like to map to something like:

public class Plant : ProjectAggregateRootEntity<int>
{
    public virtual string Code { get; set; }
    public virtual string Name { get; set; }
    public virtual string Gln { get; set; }
    public virtual string LicenceInformation { get; set; }
    public virtual IDictionary<DayOfWeek, PlantOpeningHours> OpeningHours
    {
        get; set;
    }
}

public class PlantOpeningHours
{
    public virtual DayOfWeek Day { get; set; }
    public virtual int StartMinutes { get; set; }
    public virtual int DurationMinutes { get; set; }
}

So I can enumerate the opening hours per day or reference one specifically like: plant.OpeningHours[DayOfWeek.Monday]

So it's sort of like a component mapping... but not quite.

Cache.ReadWrite().Region("ReferenceData");
Table("Plant");
Map(x => x.Code);
Map(x => x.Name);
Map(x => x.Gln);
Map(x => x.LicenceInformation);
Component(x => x.OpeningHours, x =>
{
    //??
});

Answer 1

You need to create your own UserType for this:

public class CustomDictionaryType : IUserType
{
    public CustomDictionaryType()
    {
        SqlTypes = Enumerable.Range(0, 14).Select(x => SqlTypeFactory.Int32).ToArray();
    }

    public bool Equals(object x, object y)
    {
        // should do a key/value comparison here
        return ReferenceEquals(x, y);
    }

    public int GetHashCode(object x)
    {
        return x.GetHashCode();
    }

    public object NullSafeGet(IDataReader rs, string[] names, object owner)
    {
        // DayOfWeek.Sunday = 0, so have to shift days down by one since we start with Monday in our columns
        return names.Select((x, i) => new {DayOfWeek = GetDay(i/2), value = (int) rs[x]})
            .GroupBy(x => x.DayOfWeek, x => x.value)
            .ToDictionary(g => g.Key, g => new PlantOpeningHours
                                               {
                                                   DayOfWeek = g.Key,
                                                   StartMinutes = g.First(),
                                                   DurationMinutes = g.First() - g.Last()
                                               });
    }

    private DayOfWeek GetDay(int index)
    {
        return (DayOfWeek) ((index + 6)%7);
    }

    public void NullSafeSet(IDbCommand cmd, object value, int index)
    {
        var map = value as IDictionary<DayOfWeek, PlantOpeningHours>;
        if (map == null)
            throw new ArgumentException("Cannot handle null dictionary");
        for(var i = 0; i < 7; i++)
        {
            var day = GetDay(i);
            var entry = map[day];
            ((IDataParameter) cmd.Parameters[index + i*2]).Value = entry.StartMinutes;
            ((IDataParameter) cmd.Parameters[index + i*2 + 1]).Value = entry.StartMinutes + entry.DurationMinutes;
        }
    }

    public object DeepCopy(object value)
    {
        return new Dictionary<DayOfWeek, PlantOpeningHours>((IDictionary<DayOfWeek, PlantOpeningHours>) value);
    }

    public object Replace(object original, object target, object owner)
    {
        return new Dictionary<DayOfWeek, PlantOpeningHours>((IDictionary<DayOfWeek, PlantOpeningHours>)original);
    }

    public object Assemble(object cached, object owner)
    {
        return DeepCopy(cached);
    }

    public object Disassemble(object value)
    {
        return DeepCopy(value);
    }

    public SqlType[] SqlTypes { get; private set; }
    public Type ReturnedType { get { return typeof (IDictionary<DayOfWeek, PlantOpeningHours>); } }
    public bool IsMutable { get { return true; } }
}

When you map it, you will need to specify the columns in order:

var propMap = x.Map(t => t.Duration).CustomType<CustomDictionaryType>()
    .Columns.Clear();
for(var i = 0; i < 7; i++)
{
    var day = (DayOfWeek) ((i + 6)%7);
    propMap.Columns.Add(day + "Start");
    propMap.Columns.Add(day + "Duration");
}