Mapping dynamic component against non-friendly datatable structure?

Question

I have a datatable with the following structure:

MyTable
-------------------------------------
rule_name      char(20)     PK
value_type     char(20)     PK
value          varchar(50)  not null
dt_last_update datetime     not null

Among the data represented in this table, I have a rule_name of interest:

MailboxSC

With which I have two distinct properties or call it value_type:

numberOfEmails
dateTimeReceived

Over which I have no control nor any power.

Using DynamicObject, I have followed what I could from Ayende Rahien:

Support dynamic fields with NHibernate and .NET 4.0

So now, my model.

EmailIndicator

public class EmailIndicator {
    public EmailIndicator(HashtableDynamicObject hastableDynamicObject) {
        this.hashtableDynamicObject = hashtableDynamicObject;
    }

    public virtual dynamic Attributes { get { return hashtableDynamicObject; } }

    private readonly HashtableDynamicObject hashtableDynamicObject;
}

HashtableDynamicObject

public class HashtableDynamicObject : DynamicObject {
    public HashtableDynamicObject(IDictionary dictionary) {
        this.dictionary = dictionary;
    }

    public override bool TryGetMember(GetMemberBinder binder
        , object[] indexes
        , out object result) {
        result = dictionary[binder.Name];
        return dictionary.Contains(binder.Name);
    }

    public override bool TrySetMember(SetMemberBinder binder
        , object[] indexes
        , object value) {
        dictionary[binder.Name] = value;
        return dictionary.Contains(binder.Name);
    }

    public override bool TryGetIndex(GetIndexBinder binder
        , object[] indexes
        , out object result) {
        result = dictionary[indexes[0]];
        return dictionary.Contains(indexes[0]);
    }

    public override bool TrySetIndex(SetIndexBinder binder
        , object[] indexes
        , object value) {
        dictionary[indexes[0]] = value;
        return dictionary.Contains(indexes[0]);
    }

    private readonly IDictionary dictionary;
}

Now, I'd like to map dynamic properties to each row of rule_name MailboxSC so that:

declare @emailCount             int
declare @oldestDateTimeReceived datetime

select (
        select valeur 
            from bqt001_pilotage_indicateurs 
            where nom_regle like 'boiteCourrielSC'
                and type_valeur like 'nombreCourriels'
      ) as emailCount
      , (
        select valeur as olsdestDateTimeReceived
            from bqt001_pilotage_indicateurs
            where nom_regle like 'boiteCourrielSC'
                and type_valeur like 'dateHeurePlusVieux'
      ) as oldestDateTimeReceived
    into @emailCount
        , @oldestDateTimeReceived

So I get my required values, the number of emails and the oldest date/time received.

How to map it using either classic NHibernate XML mapping or Fluent NHibernate?

Linked Ayende Rahien's article provides with a first approach:

<class name="Customer"
       table="Customers">

    <id name="Id">
        <generator class="identity"/>
    </id>
    <property name="Name" />

    <join table="Customers_Extensions" optional="false">
        <key column="CustomerId"/>
        <dynamic-component name="Attributes" access="field.lowercase">
            <property name="EyeColor" type="System.String"/>
        </dynamic-component>
    </join>
</class>

So my guess would be:

<class name="EmailIndicatorModel">
    <join table="MyTable" optional="false">
        <dynamic-component name="Attributes" access="field.lowercase">
            <property name="EmailCount" type="System.Int32" />
            <property name="OldestDateTimeReceived" type="System.DateTime" />
        <dynamic-component>
    </join>
</class>

And then again, this doesn't allow me to mention the discriminator columns rule_name and value_type for each of the properties.

Am I condemned to use old-style ADO.NET?

Here are some interesting articles regarding what interests me:

dynamic-component fluent automapping
Mapping-by-Code - dynamic component
NHibernate mapping class properties to Rows not Columns (This article led me to Ayende Rahien's article)

score 1 · Answer 1 · edited May 23 '17 at 11:44

I tried to put together possibilities we have for dynamic mapping with NHibernate:

NHibernate Dynamic mapping

I personally do use and profit a lot from <dynamic-component>, but as explained in the resource above, that requires different structure.

I. `IDictionary`

So firstly a very usual/standard table for a Customer:

// [Customer] table with key Customer_ID
Customer_ID, Name, Code,...
          1,  Abc,  xxx,.. 
          2,  Def,  yyy,..

Key column Customer_ID, and columns describing Customer. Now, if we would like to use IDictionary (or C# dynamic object) and <dynamic-component> to extend the Customer, we need table like this:

// [CustomerAttributes] - with a key referencing Customer table via Customer_ID
Customer_ID, Attribute1, Attribute2, Attribute3, ...
          1,        123, 2014-01-01,      FALSE, ...
          2,        456, 2015-01-01,       TRUE, ...

And this is candidate for

<join table="CustomerAttributes" optional="false">
    <key column="Customer_ID"/>
    <dynamic-component name="Attributes">
        <property name="Prop1" column="Attribute1" type="int"/>
        <property name="Prop2" column="Attribute2" type="DateTime"/>
        <property name="Prop3" column="Attribute3" type="bool"/>
    </dynamic-component>
</join>

Advantage? we can use ORDER BY. In fact, we can use anything (SELECT - Projections - see this custom ResultTransformer working with <dynamic-component>, WHERE...)

BUT there must be standard table, where properties (keys of IDictionary) are represented by columns.

`IDictionary<T,U>`

In case of a Table mentioned above in the question

MyTable
-------------------------------------
rule_name      char(20)     PK
value_type     char(20)     PK
value          varchar(50)  not null
dt_last_update datetime     not null

We would need IDictinary<T, U> and that is mapped with a <map>:

<map name="Attributes" table="MyTable" >
    <key column="rule_name"/>
     <index-many-to-many column="value_type" class="ValueType"/>
     <element column="value" type="string"/>
</map>

In this case, we are mapping ROWS not columns. This approach is not supporting ORDER BY, nor SELECT (projections)...

SUMMARY: Table, which 1) has values related to our parent table in rows, 2) discriminated by some column is candidate for <map>

Mapping dynamic component against non-friendly datatable structure?

1 Answers1

NHibernate Dynamic mapping

I. IDictionary

IDictionary<T,U>

I. `IDictionary`

`IDictionary<T,U>`