Getting class field names and table column names f

Question

Background

I am using a legacy database with all kinds of ugly corners. One bit is auditing. There is a table listing tablename/field combinations of fields that should have an audit trail. For example, if there is a row that has "WORKORDER" for the table name and "STATUS" for the fieldname, then I need to add row(s) to the auditing table whenever the Workorder.Status property changes in the application. I know the approach: NH events or interceptors, but I've got an issue to figure out before I get to that stage.

Question

What I need to know is how to get a list of key/value pairs for a single persistent class containing (a) the database field name and (b) the associated property name in the class. So for my example, I have a class called Workorder associated with a table called (no surprise) WORKORDER. I have a property on that Workorder class called CurrentStatus. The matching property in the WORKORDER table is STATUS. Notice the mismatch between the property name and table column name? I need to know the property name to access the before and after data for the audit. But I also need to know the backing column name so that I can query the stupid legacy "AuditTheseColumns" table.

What I've tried

in my application I change the Workorder.CurrentStatus from "TS" to "IP". I look in my audit tracking table and see that the WORKORDER.STATUS column is tracked. So after calling Session.SaveOrUpdate(workorder), I need to find the Workorder property associated with the STATUS column and do a Session.Save(auditRecord) telling it the old ("TS") and new ("IP") values.

As far as I can tell, you can get information about the class:

        var fieldNames = new List<string>();
        IClassMetadata classMetadata = SessionFactory(Resources.CityworksDatasource).GetClassMetadata(typeof(T));
        int propertyCount = 0;
        foreach (IType propertyType in classMetadata.PropertyTypes)
        {
            if (propertyType.IsComponentType)
            {
                var cp = (ComponentType)propertyType;

                foreach (string propertyName in cp.PropertyNames)
                {
                    fieldNames.Add(propertyName);
                }
            }
            else if(!propertyType.IsCollectionType)
            {
                fieldNames.Add(classMetadata.PropertyNames[propertyCount + 1]);
            }

            propertyCount++;
        }

And information about the table:

        var columnNames = new List<string>();
        PersistentClass mappingMeta = ConfigureCityworks().GetClassMapping(typeof(T));

        foreach (Property property in mappingMeta.PropertyIterator)
        {
            foreach (Column selectable in property.ColumnIterator)
            {
                if (columnNames.Contains(selectable.Name)) continue;
                columnNames.Add(selectable.Name);
            }
        }

But not at the same time. Any ideas? I'm at a loss where to look next.

Answer 1

Now if I understand correctly here is what you could do....

One way would be to read and parse the XML mapping files from the dll that are embedded before or even after the NHibernate session factory is build. This way you can get all the info you need from the XML files (with column corresponds to which property) and populate a global (probably static) collection of custom objects that will hold the entity's name and a dictionary with key the propery name and value the column name (or the other way around).

You can then access this global collection to get the info you need right after the call to SaveOrUpdate() as you described it. The downside of this approach is that you need to write your own XML parsing logic to retrive the info you need from the XML mapping files.

An alternative would be to create a custom attribute to decorate each property of your entities in order to get the column name that corresponds to each property. An example would be:

[ColumnName("MyColumn")]
public string Status { get; set; }

Using reflection you can easily get the property name and the from the attribute the column name that this property is mapped to.

The downside of this approach would be having to keep in sync your column names with the attribute values when the database schema is updated.

Answer 2

How to get the database column / field names and class property names for an entity mapped by NHibernate:

using System;
using System.Collections.Generic;
using System.Reflection;
using NHibernate;
using NHibernate.Persister.Entity;

namespace Stackoverflow.Example
{
    /// <summary>
    /// NHibernate helper class
    /// </summary>
    /// <remarks>
    /// Assumes you are using NHibernate version 3.1.0.4000 or greater (Not tested on previous versions)
    /// </remarks>
    public class NHibernateHelper
    {
        /// <summary>
        /// Creates a dictionary of property and database column/field name given an
        /// NHibernate mapped entity
        /// </summary>
        /// <remarks>
        /// This method uses reflection to obtain an NHibernate internal private dictionary.
        /// This is the easiest method I know that will also work with entitys that have mapped components.
        /// </remarks>
        /// <param name="sessionFactory">NHibernate SessionFactory</param>
        /// <param name="entity">An mapped entity</param>
        /// <returns>Entity Property/Database column dictionary</returns>
        public static Dictionary<string, string> GetPropertyAndColumnNames(ISessionFactory sessionFactory, object entity)
        {
            // Get the objects type
            Type type = entity.GetType();

            // Get the entity's NHibernate metadata
            var metaData = sessionFactory.GetClassMetadata(type.ToString());

            // Gets the entity's persister
            var persister = (AbstractEntityPersister)metaData;

            // Creating our own Dictionary<Entity property name, Database column/filed name>()
            var d = new Dictionary<string, string>();

            // Get the entity's identifier
            string entityIdentifier = metaData.IdentifierPropertyName;

            // Get the database identifier
            // Note: We are only getting the first key column.
            // Adjust this code to your needs if you are using composite keys!
            string databaseIdentifier = persister.KeyColumnNames[0];

            // Adding the identifier as the first entry
            d.Add(entityIdentifier, databaseIdentifier);

            // Using reflection to get a private field on the AbstractEntityPersister class
            var fieldInfo = typeof(AbstractEntityPersister)
                .GetField("subclassPropertyColumnNames", BindingFlags.NonPublic | BindingFlags.Instance);

            // This internal NHibernate dictionary contains the entity property name as a key and
            // database column/field name as the value
            var pairs = (Dictionary<string, string[]>)fieldInfo.GetValue(persister);

            foreach (var pair in pairs)
            {
                if (pair.Value.Length > 0)
                {
                    // The database identifier typically appears more than once in the NHibernate dictionary
                    // so we are just filtering it out since we have already added it to our own dictionary
                    if (pair.Value[0] == databaseIdentifier)
                        break;

                    d.Add(pair.Key, pair.Value[0]);
                }
            }

            return d;
        }
    }
}

Usage:

// Get your NHiberate SessionFactory wherever that is in your application
var sessionFactory = NHibernateHelper.SessionFactory;

// Get an entity that you know is mapped by NHibernate
var customer = new Customer();

// Get a dictionary of the database column / field names and their corresponding entity property names
var propertyAndColumnNamesDictionary =
    Stackoverflow.Example.NHibernateHelper.GetPropertyAndColumnNames(sessionFactory, customer);