[NHibernate] Map unknown amount of columns to Dictionary

Question

I have a legacy system that dynamically augments a table with additional columns when needed. Now I would like to access said table via C#/NHibernate.
There is no way to change the behaviour of the legacy system and I dynamically need to work with the data in the additional columns. Therefore dynamic-component mapping is not an option since I do not know the exact names of the additional columns.

Is there a way to put all unmapped columns into a dictionary (column name as key)? Or if that's not an option put all columns into a dictionary?

Again, I do not know the names of the columns at compile time so this has to be fully dynamic.

Example:

public class History
{
    public Guid Id { get; set; }
    public DateTime SaveDateTime { get; set; }
    public string Description { get; set; }
    public IDictionary<string, object> AdditionalProperties { get; set; }
}

So if the table History contains the Columns Id, SaveDateTime, Description, A, B, C and D I would like to have "A", "B", "C" and "D" in the IDictionary. Or if that's too hard to do simply throw all columns in there.

For starters I would also be fine with only using string columns if that helps.

Answer 1

You probably need an ADO.NET query to get this data out. If you use NH, even with a SQL query using SELECT *, you'll not getting the column names.

You can try using SMO (SqlServer management objects, a .NET Port to the SqlServer) or some other way to find the table definitions. Then you build up the mapping using Fluent NHibernate with a dynamic component. I'm not sure if you can change the mappings after you already used the session factory. It's worth a try. Good luck :-)

Answer 2

I think the best you can do is to find the columns at runtime, create a mapping for these extra columns then write the output to an xmlfile. Once that is done you can add the mapping at runtime...

ISessionFactory sessionFactory = new Configuration()
                  .AddFile("myDynamicMapping.hbm.xml")

How you would use this mapping is a good question as you would have to create your class dynamically as well then you are SOL

good luck.

Answer 3

What's not possible in sql is not possible in NHibernate.

It's not possible to write in insert query to insert into unknown columns.

Answer 4

I just posted the question to the nhusers group. Silvia

Answer 5

I assume that your program builds a single Configuration object on startup, by reading XML files, and then uses the Configuration object build ISessionFactory objects.

Instead of reading the XML files, building the Configuration object, and calling it a day, however, your program can send a query to the database to figure out any extra columns on this table, and then alter the Configuration, adding columns to the DynamicMapping programmatically, before compiling the Configuration object into an ISessionFactory.

Answer 6

NHibernate does have ways to get the database schema, provided it's supported by the database type/dialect. It is primarily used by the SchemaExport and SchemaUpdate functions.

If you're not scared of getting your hands a bit dirty;

Start by looking at the GenerateSchemaUpdateScript function in the Configuration class: https://nhibernate.svn.sourceforge.net/svnroot/nhibernate/trunk/nhibernate/src/NHibernate/Cfg/Configuration.cs

In particular, you'd be interested in this class, which is referenced in that method: https://nhibernate.svn.sourceforge.net/svnroot/nhibernate/trunk/nhibernate/src/NHibernate/Tool/hbm2ddl/DatabaseMetadata.cs

The DatabaseMetadata object will allow you to traverse the metadata for all tables and fields in the database, allowing you to figure out which fields are not mapped. If you look at the Configuration class again, it holds a list of it's mapping in the TableMappings collection. Taking hints from the GenerateSchemaUpdateScript function, you can compare a Table object from the TableMappings against the any object implementing ITableMetadata returned by the DatabaseMetadata.GetTableMetadata function to figure out which columns are unmapped.

Use this information to then rebuild the mapping file used by the "dynamic" class at runtime placing all the dynamic/runtime fields in the "AdditionalProperties" dynamic-component section of the mapping file. The mapping file will need to be included as an external file and not an embedded resource to do this, but this is possible with the Configuration AddFile function. After it is rebuilt, reload the configuration, and finally rebuild the session factory.

At this time it looks like Firebird, MsSQL Compact, MsSQL, MySQL, Oracle, SQLite, and SybaseAnywhere have implementations for ITableMetadata, so it's only possible with one of these(unless you make your own implementation).

Answer 7

I guess with the following code you can get your results in a Hashtable:

var hashTable = (Hashtable)Session.CreateSQLQuery("SELECT * FROM MyTable")
    .SetResultTransformer(Transformers.AliasToEntityMap)
    .UniqueResult();

Obviously all your data will be detached from the session...