Getting class field names and table column names from NHibernate metadata

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.