c# - NHibernate Dynamic Columns Number

Question

In my C# project I need create in code one database with one table with dynamic columns number. Like this

------------------------------------------------------------------------------     
| Time        | Element #1       | Element#2        | ... | Element#N        |
------------------------------------------------------------------------------
| TimeValue#1 | Element#1Value#1 | Element#2Value#1 | ... | Element#NValue#1 |
| TimeValue#2 | Element#1Value#2 | Element#2Value#2 | ... | Element#NValue#2 |
                                      ...
| TimeValue#M | Element#1Value#M | Element#2Value#M | ... | Element#NValue#M |
------------------------------------------------------------------------------

I use simple SQL query 'SQL CREATE TABLE'

public void AddTable(string TableName, Dictionary<string,Type> columns)
{        
    ...
    string query = @"CREATE TABLE " + TableName + "(";

    foreach (var c in columns)
    {
        SqlParameter temp = new SqlParameter();
        string t = SetSQLType(ref temp, c.Value).ToString();
        query += c.Key + " " + (t == SqlDbType.NVarChar.ToString() ? (t + "(200)") : (t)) + ",";
    }

    query = query.Substring(0, query.Length - 1);
    query += " )";
    ...
}

But I think, maybe, I can use more comfortable ORM for this, for example - NHibernate. Can i? I read about mapping-by-code and dynamic component, but I'm not sure, what exactly I need to do in my case.

I think, I need something like this:

public class Elements
{
    public virtual DateTime Timestamp { get; set; }
    public virtual IEnumerable<double> Values { get; set; }
}

Can I mapping this class?

Answer 1

This topic is interesting. And maybe not so clear at first look. Take my answer as an overview, a summary from all the sources listed below. And maybe it will give you the answer.

From the C# point of view, you can think about these dynamic properties this way

public virtual IDictionary<keyType, valueType> Additional { get; set; } 
                 // (e.g. <string, object>

public virtual IDictionary Additional { get; set; }

Both of these are dynamic. There is no compile-time checking for handling IDictionary. Better situation is the generic arguments checking for IDictinary<,>. But because we are talking about dynamic mapping, the compile time check is what we can sacrifice...

To load data into one of these dictionaries we have to (in most cases) do different mapping and have different structure of tables. The Generic would be handy for transpostion of data contained in rows. The Non-Generic could be used for column mapping (as the example in the question). Let's discuss both

1) Generic IDictionary<,> - Dynamic rows

Let's start with more type safe scenario. Then touch solution close to the question

1a) Ternary Associations

For example, let's map a few Persons to some Entity (e.g. Contract) based on their roles/types. 1) Manager 2) Leader 3) Tester. If we know that there could be only one person per type/role (only one Tester or none), we can explain it as:

public virtual IDictionary<PersonType, Person> Persons { get; set; }

We are dynamic right now. There could 0, 1 or 1+ Persons related to the Contract. Each of them has to be unique by the PersonType. And we can also introduce new PersonTypes in a runtime, and extend any Contract's related Person set...

The mapping should be like this

<map name="Persons" table="ContractPerson" >
    <key column="ContractId"/>
     <index-many-to-many column="PersonTypeId" class="PersonType"/>
     <one-to-many class="Person"/>
</map>

This is an example of 6.9. Ternary Associations. There are three entities involved, and we still do have flexibility in the runtime. As already said, we can insert new PersonTypes and amend these Contract-Person relations.

This scenario (in comparison with IDictiniary<string, object>) still provides lot of compile-time checking.

1b) Closer to the Question

In the scenario described above, if we would like to use <map> and be dynamic from a rows perspective - we need a table like this:

ElemntId| TheKey       | TheValue (e.g. nvarchar)
1       | "name"       | "Element A"
1       | "time"       | "20:02"
1       | "date"       | "2013-05-22"
1       | "value"      | "11.22"

C# would look like this

public class Elements
{
    ...
    public virtual IDictionary<string, string> Values { get; set; }
}

mapping:

<map name="Values" table="DynamicElementValues" >
   <key column="ElementId"/>
    <index column="TheKey" type="string"/>
    <element column="TheValue" type="string"/>
</map>

Because we've used IDictionary<string, string> all values are strings. We would need some MetaData to correctly interpret its value

We gained:

• dynamic collection of many values of many types

We lost:

• ability to put any value into SELECT or ORDER BY clause
• have to convert data types (from a string into any other)

1c) Key as a string

In fact, dictionary with a string key is dynamic, but too much. As shown in the 1a) it is always better idea, to somehow manage the set of values to be used as a key. That's why we've discussed Ternary associations. Because we sooner or later have to interpret the data in this dictionary - with some MetaData, it could be handy to use them also as a key...

2) Non-Generic IDictionary - Dynamic columns

This time we will really try to do dynamic solution over columns.

The NHibernate features, we can use here are:

• 4.4. Dynamic models,
• 5.1.13. component, dynamic-component,
• 5.1.18. join

This kind of mapping is very close to the Question, to our requirement. We will have this C# representation

public class Elements
{
    ...
    public virtual IDictionary DynamicValues { get; set; }
}

And this could be the mapping:

<join table="ElemntValues" >

  <key column="ElementId" />
  <dynamic-component name="DynamicValues"   >

    <property name="Time"        type="TimeSpan" />
    <property name="Date"        type="DateTime" />
    <property name="Salary"      type="decimal" />
    <property name="Color"       type="string" />
    <property name="WorkingDays" type="integer"  />
    <many-to one....
    ...

  </dynamic-component>
</join>

In this case, we do have separted table ElementValues, joined to our Parent entity (as a part of its <class> mapping).

This is not the only mapping. There could be other mapping types e.g. with 4.4. Dynamic models

<class entity-name="DynamicValues"...

These could require some more special handling (insert, udpate, delete)

Join would simplify lot of stuff, but will be used in a SQL statment always (even if only Parent core properties are required)

Is it dynamic?

Well we gained:

• On a C# we have only property IDictionary ElementValues
• NHibernate does for us runtime checks. Only correct types of value could be inserted to the keys (Salary must be decimal)
• with some MetaData model we can really be dynamic to the user
• Any of the Mapped properties could be used in SELECT (projections) and ORDER BY (user will love it)

We lost:

• some performance, because all data (e.g. session.Get<>(id)) will be loaded
• we are not dynamic, in case of adding or removing columns. All the mapping is part of the application distribution, and cannot be change in runtime. Well, we can always re-deploy just new mapping...

2b) IDictionary and MetaData

While the IDictinary is from a C# perspective very dynamic (it could contain any key/pair value), due to NHibernate mapping, the content is managed. Only integer values could be added to the properties mapped as integer. But how would we in a runtime know: what keys we do have? what values could be placed there and retrieved from there? Again, we need some MetaData... not acting the role of a key, but they will be crucial in the runtime. NHibernate checks are last line of defense.

3) How to change mapping in the runtime (new column added)?

Well, what is stated in the documentation? 7.5. Dynamic components:

The advantage of this kind of mapping is the ability to determine the actual properties of the component at deployment time, just by editing the mapping document. (Runtime manipulation of the mapping document is also possible, using a DOM parser.)

...using a DOM parser. To be honest, I do not know what that mean, how to implement that.

And also Adam Bar in Mapping-by-Code - dynamic component stated (see comments)

I think that dynamic component can't be really dynamic at both object and database level. Note that the component parts are stored as ordinary columns, so we need to know its list...

But there is a nice idea from Firo - NHibernate dynamic mapping (take a look, too complex for some extract). If really needed, this could be solution to a real dynamic world... new columns in runtime ... new keys mapping in IDictionary

Summary

With a <map> mapping we can be very dynamic. Different keys and values in runtime. No need to redeploy. But we cannot use these dynamic properties in a select or order by. It is hard to filter by these values

With a <dynamic-component> we are (out of the box) dependent on the mapping. But we do have our data in columns and therefore we can use joins to retrieve them. Easy to filter, sort. And/but there must be some metadata to guide us what we do have and what we can do.

Other Sources:

• Ayende Dynamic component
• Adam Bar, Mapping-by-Code - dynamic component
• Firo and runtime mapping replacement
• 4.4. Dynamic models
• 7.5. Dynamic components