Fluent NHibernate: How to set default values for intermediate table

Question

I am working on an .NET MVC app which uses NHibernate and Fluent NHibernate for mapping. I have the User and Role tables/classes, and they are mapped through an intermediary table, UserRole. So my mapping is working correctly; I have in the user mapping:

HasManyToMany(user => user.Roles).Cascade.SaveUpdate()

So when I go to save a user with, say, a new role, it will try to insert a new UserRole row. Great.

The only issue is that I have audit columns(like "UpdatedBy" and "UpdatedDate") on the UserRole table that are not nullable. I tried setting them in the UserRole constructor, but apparently that never gets called because fluent Nhibernate is just going directly to the database and trying to insert new UserRoles without the audit columns.

So I was just wondering if there is any clean way to set default values for these columns in Fluent Nhibernate. I could just turn cascade off, and do everything manually on saving Users, but I was wondering if there is a more clean and straightforward way to do this.

Using the "Default" method in the mapping doesn't work. I think that is for generating a DB from the mapping, and if you do that it gives it a default value in the DB, but I want to set the value from the code side so that I can get the current user id in the code and then have that be the updated_by value in the user role. — dhouse42

Frédéric Frédéric · Accepted Answer · 2016-03-18T22:18:53

Either use a full blown audit system like Envers, or add some interceptors for your custom audit use cases.

But adding additional property to an association table cause it to be hard to handle as a pure association table, mapped only through many-to-many relationship.

You probably would have it easier to map it as an intermediate entity (and then preferably, add to it a surrogate key), linked through many-to-one relationships to your User and Role entities.

An interceptor would react to any update or insert and set auditing properties accordingly. For entities and mapped audit properties, this is easy. For your case, you would have to patch the emitted SQL.

Here is the one I use, which set creator (mapped on some user entity and not nullable), create date (mapped and not nullable), updater, update date (mapped and nullables). (Adapted from this NH reference example and from this blog post.)

For your case, you would need to add overrides to SqlString OnPrepareStatement(SqlString sql) probably in combination with void OnCollectionUpdate(object collection, object key) and void OnCollectionRecreate(object collection, object key).

// Interceptor setting up audit properties.
[Serializable]
public class AuditInterceptor : NHibernate.EmptyInterceptor
{
    public YourAppUser AppUser { get; set; }

    public override bool OnFlushDirty(object entity,
        object id,
        object[] currentState,
        object[] previousState,
        string[] propertyNames,
        NHibernate.Type.IType[] types)
    {
        var modified = false;
        for (int i = 0; i < propertyNames.Length; i++)
        {
            switch (propertyNames[i])
            {
                case "UpdateDate":
                    currentState[i] = DateTimeOffset.Now;
                    modified = true;
                    break;
                case "Updater":
                    currentState[i] = AppUser;
                    modified = true;
                    break;
            }
        }
        return modified;
    }

    public override bool OnSave(object entity,
        object id,
        object[] state,
        string[] propertyNames,
        NHibernate.Type.IType[] types)
    {
        var modified = false;
        for (int i = 0; i < propertyNames.Length; i++)
        {
            switch (propertyNames[i])
            {
                case "CreationDate":
                    state[i] = DateTimeOffset.Now;
                    modified = true;
                    break;
                case "Creator":
                    state[i] = AppUser;
                    modified = true;
                    break;
            }
        }
        return modified;
    }
}

Inject your interceptor instance when opening sessions :

yourNHibernateSessionFactory.OpenSession(yourInterceptorInstance);

And you should set on your interceptor who is current user before your business logic handles its work. In my case, I do that in an action filter OnActionExecuting method, using dependency resolver to get my interceptor, which have a per http request lifetime manager.

This interceptor assumes that any property named Creator or Updater is a AppUser property, and any property named CreateDateor UpdateDate are datetimeoffset. You may by example want to guarantees such assumptions by checking that entity does implement some custom interface (some check like if (!(entity is IYourAuditableInterface)) return false;, as the reference example is doing). Or you can check types argument too.

Fluent NHibernate: How to set default values for intermediate table

1 Answers