NHibernate and log4net

Here’s a quick trick for people using NHibernate and log4net within their .NET applications. (…and if you’re not using NHibernate or log4net, you should seriously think about it!)

Add the following to your log4net configuration to find out what’s going on inside NHibernate:

  <logger name="NHibernate">
    <level value="DEBUG" />
  </logger>
  <logger name="NHibernate.SQL">
    <level value="DEBUG" />
  </logger>

As those kind folks at NHibernate have already set up the loggers behind the scenes, everything just works. The SQL logger is especially useful as it’ll show you explicitly which SQL statements are being generated by NHibernate. You can adjust the levels of NHibernate logging independently of your normal application logging, which is useful as the NHibernate logs tend to be pretty verbose.

The full configuration will look something like this:

<log4net>
  <appender name="TraceAppender" type="log4net.Appender.TraceAppender" >
    <layout type="log4net.Layout.PatternLayout">
      <param name="ConversionPattern" value="%d %-5p- %m%n" />
    </layout>
  </appender>
  <appender name="RollingFileAppender" type="log4net.Appender.RollingFileAppender">
    <file value="Application.log"/>
    <appendToFile value="true"/>
    <rollingStyle value="Date"/>
    <datePattern value="yyyy-MM-dd'.log'"/>
    <layout type="log4net.Layout.PatternLayout">
      <conversionPattern value="%date [%thread] %-5level %logger [%property{NDC}] - %message%newline"/>
    </layout>
  </appender>
  <root>
    <level value="DEBUG"/>
    <appender-ref ref="TraceAppender"/>
    <appender-ref ref="RollingFileAppender"/>
  </root>
  <logger name="NHibernate">
    <level value="ERROR" />
  </logger>
  <logger name="NHibernate.SQL">
    <level value="ERROR" />
  </logger>
</log4net>

Changing the default naming convention for foriegn keys when using Fluent NHibernate

By default, NHibernate will assume that foreign keys should be named using the convention ReferencedTableName_id (e.g. Person_id). I personally don’t like this conversion and would prefer ReferencedTableNameId (e.g. PersonId).

Fortunately, this is pretty easy to achieve by extending the ForeignKeyConvention class in Fluent NHibernate:

using System;
using FluentNHibernate;
using FluentNHibernate.Conventions;

namespace NHibernate.Helpers.Conventions
{
    public class CustomForeignKeyConvention : ForeignKeyConvention
    {
        protected override string GetKeyName(Member property, System.Type type)
        {
            if (property != null)
            {
                return property.Name + "Id";
            }

            if (type != null)
            {
                return type.Name + "Id";
            }

            // If both are null, we throw an exception:
            throw new ArgumentNullException("property", "The property and type parameters cannot both be null");
        }
    }
}

This needs to be wired into your SessionFactory creation code as follows:

sessionFactory = Fluently.Configure()
    .Database(DatabaseConfiguration)
    .Mappings(x => x.FluentMappings.AddFromAssembly(MappingsAssembly))
    .Mappings(x => x.FluentMappings.Conventions.Add(new CustomForeignKeyConvention()))
    .ExposeConfiguration(x => new SchemaUpdate(x).Execute(false, true))
    .BuildSessionFactory();

Note that the example above will also update the database schema.

Once you’ve done that, all foreign keys will use the desired naming convention.

NHibernate NullableDateTime custom type

NHibernate doesn’t support mappings for nullable DateTime types by default. Fortunately, this is easy to implement using the IUserType interface. To demonstrate this, create a new Console Application project and add FluentNHibernate using NuGet. Then add the following class:

using System;
using System.Data;
using NHibernate;
using NHibernate.SqlTypes;
using NHibernate.UserTypes;

namespace UserTypes
{
    /// <summary>
    /// Type to allow NHibernate to persist DateTime? objects
    /// </summary>
    public class NullableDateTimeType : IUserType
    {
        /// <summary>
        /// Gets a value indicating whether the value is mutable
        /// </summary>
        public bool IsMutable
        {
            get
            {
                // This item is immutable:
                return false;
            }
        }

        /// <summary>
        /// Gets the type returned by NullSafeGet()
        /// </summary>
        public Type ReturnedType
        {
            get
            {
                return typeof(DateTime?);
            }
        }

        /// <summary>
        /// Gets the SQL types for the columns mapped by this type. 
        /// </summary>
        public SqlType[] SqlTypes
        {
            get
            {
                return new[]
                {
                    new SqlType(DbType.DateTime)
                };
            }
        }

        /// <summary>
        /// Reconstruct an object from the cacheable representation. At the very least this method should perform a deep copy if the type is mutable.
        /// </summary>
        /// <param name="cached">The cached object</param>
        /// <param name="owner">The owner object</param>
        /// <returns>The assemled object</returns>
        public object Assemble(object cached, object owner)
        {
            // Used for caching. As our object is immutable we can return as is:
            return cached;
        }

        /// <summary>
        /// Return a deep copy of the persistent state, stopping at entities and at collections. 
        /// </summary>
        /// <param name="value">The item to copy</param>
        /// <returns>The copied item</returns>
        public object DeepCopy(object value)
        {
            // We deep copy the item by creating a new instance with the same contents.
            // Note that this happens for free with value types because of the way 
            // that method parameters work:
            if (value == null)
            {
                return null;
            }

            return value as DateTime?;
        }

        /// <summary>
        /// Transform the object into its cacheable representation. At the very least this method should perform a deep copy 
        /// if the type is mutable. That may not be enough for some implementations, however; for example, associations must 
        /// be cached as identifier values.
        /// </summary>
        /// <param name="value">The cached object</param>
        /// <returns>The dassassemled object</returns>
        public object Disassemble(object value)
        {
            // Used for caching. As our object is immutable we can return as is:
            return value;
        }

        /// <summary>
        /// Compare two instances of the class mapped by this type for persistent "equality" ie. equality of persistent state 
        /// </summary>
        /// <param name="x">The first item</param>
        /// <param name="y">The second item</param>
        /// <returns>A value indicating whether the items are equal</returns>
        public new bool Equals(object x, object y)
        {
            if (x == null && y == null)
            {
                return true;
            }

            if (x == null)
            {
                return false;
            }

            return x.Equals(y);
        }

        /// <summary>
        /// Get a hashcode for the instance, consistent with persistence "equality" 
        /// </summary>
        /// <param name="x">The value to get the hash code for</param>
        /// <returns>The hash code</returns>
        public int GetHashCode(object x)
        {
            if (x == null)
            {
                return 0;
            }

            return x.GetHashCode();
        }

        /// <summary>
        /// Retrieve an instance of the mapped class from a resultset. Implementors should handle possibility of null values. 
        /// </summary>
        /// <param name="rs">The reader</param>
        /// <param name="names">The item names</param>
        /// <param name="owner">The owner object</param>
        /// <returns>The object requested</returns>
        public object NullSafeGet(IDataReader rs, string[] names, object owner)
        {
            // We get the DateTime from the database using the NullSafeGet used to get strings from NHibernateUtil:
            return NHibernateUtil.DateTime.NullSafeGet(rs, names[0]) as DateTime?;
        }

        /// <summary>
        /// Write an instance of the mapped class to a prepared statement. Implementors should handle possibility of null values. A multi-column type should be written to parameters starting from index. 
        /// </summary>
        /// <param name="cmd">The command</param>
        /// <param name="value">The value to use</param>
        /// <param name="index">The index to set</param>
        public void NullSafeSet(IDbCommand cmd, object value, int index)
        {
            // Convert to the correct type and set:
            var dateTimeValue = value as DateTime?;

            if (dateTimeValue == null)
            {
                NHibernateUtil.DateTime.NullSafeSet(cmd, null, index);
            }
            else
            {
                NHibernateUtil.DateTime.NullSafeSet(cmd, dateTimeValue.Value, index);
            }
        }

        /// <summary>
        /// During merge, replace the existing (target) value in the entity we are merging to with a new (original) 
        /// value from the detached entity we are merging. For immutable objects, or null values, it is safe to 
        /// simply return the first parameter. For mutable objects, it is safe to return a copy of the first parameter. 
        /// For objects with component values, it might make sense to recursively replace component values. 
        /// </summary>
        /// <param name="original">The original value</param>
        /// <param name="target">The target value</param>
        /// <param name="owner">The owner object</param>
        /// <returns>The replacement object</returns>
        public object Replace(object original, object target, object owner)
        {
            // As our object is immutable we can just return the original  
            return original;
        }
    }
}

This class is responsible for telling NHibernate how to save and retrieve nullable DateTime values. The following demonstrates how this class can be used.

Given a class with a nullable DateTime property:

using System;

namespace UserTypes
{
    public class Student
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public DateTime? GraduationDate { get; set; }
    }
}

…the mapping would be as follows:

using FluentNHibernate.Mapping;
using UserTypes;

public class StudentMapping : ClassMap<Student>
{
    public StudentMapping()
    {
        Not.LazyLoad();
        Id(x => x.Id).GeneratedBy.Identity();
        Map(x => x.FirstName).Length(20).Not.Nullable();
        Map(x => x.LastName).Length(20).Not.Nullable();
        Map(x => x.GraduationDate).Nullable().CustomType(x => typeof(NullableDateTimeType));
    }
}

This can be tested by adding the following code to the Program class:

using System;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate.Cfg;
using NHibernate.Tool.hbm2ddl;

namespace UserTypes
{
    public static class Program
    {
        public static void Main()
        {
            var connectionString = "Data Source=(local);Initial Catalog=StudentTest;Integrated Security=True;Pooling=False";

            var sessionFactory = Fluently.Configure()
                .Database(MsSqlConfiguration.MsSql2008.ConnectionString(connectionString).ShowSql())
                .ExposeConfiguration(BuildSchema)
                .Mappings(x => x.FluentMappings.AddFromAssemblyOf<Student>())
                .BuildSessionFactory();

            // Create the session:
            using (var session = sessionFactory.OpenSession())
            {
                session.SaveOrUpdate(new Student { FirstName = "Not", LastName = "Graduated" });
                session.SaveOrUpdate(new Student { FirstName = "Already", LastName = "Graduated", GraduationDate = new DateTime(2000, 1, 1) });
            }

            using (var session = sessionFactory.OpenSession())
            {
                var students = session.QueryOver<Student>().List();
                foreach (var student in students)
                {
                    Console.WriteLine("{0} {1} {2}", student.FirstName, student.LastName, student.GraduationDate);
                }
            }

            Console.ReadLine();
        }

        private static void BuildSchema(Configuration configuration)
        {
            new SchemaExport(configuration).Create(false, true);
        }
    }
}

Note that the code above assumes there is a local instance of SQL Server running, with a database called StudentTest accessible using integrated authentication.

Running the code creates the following table, which includes a DateTime field that accepts null values as required:

NHibernateNullableDateTime01

…inserts the following data:

NHibernateNullableDateTime02

…and outputs the following:

NHibernateNullableDateTime03

This proves that the mapping can be used to store and retrieve null and non-null DateTime values as required.

Calculate an average using NHibernate

Yesterday I was writing a dashboard application to summarise data held in a SQL Server database. Part of the dashboard involves showing the average of a certain value in rows added in the last hour, day, week and month. In the good old days this would have been easy; I would have written a stored procedure involing the AVG function. However, as I was using NHibernate I thought I would try and do this without writing any SQL (or indeed HQL). While I am a big fan of NHibernate this was much less straightforward than writing some simple SQL.

Here is the code I came up with:

public double GetAverage(DateTime dateFrom, DateTime dateTo)
{
	var query = Session.QueryOver().SelectList(list => list.SelectAvg(item => item.Value))
		.Where(x => x.Date >= dateFrom && x.Date <= dateTo);

	try
	{
		return query.List().First();
	}
	catch (GenericADOException exception)
	{
		// Note that if there are no results for the period (and the average is null) then we will
		// get a specific error back from NHibernate. We filter this out and return 0:
		if (exception.Message.StartsWith("Unable to perform find[SQL: SQL not available]", StringComparison.OrdinalIgnoreCase) && 
		    exception.InnerException != null && 
		    exception.InnerException.Message.StartsWith("Value cannot be null", StringComparison.OrdinalIgnoreCase))
		{
		    return 0;
		}

		// In all other cases we re-throw:
		throw;
	}
}

I discovered that if the query is ran over a period containing no data NHibernate throws an exception, hence the error trapping which returns 0 if the exact error referring to zero records is encountered.

Interestingly, when I turned on Log4Net SQL logging I found that NHibernate generates exactly the same SQL I would have written myself in the good old days:

SELECT avg(cast(this_.Value as DOUBLE PRECISION)) as y0_ 
FROM [Item] this_ 
WHERE 
	(this_.Date >= @p0 and this_.Date <= @p1);
@p0 = 28/03/2013 20:17:04 [Type: DateTime (0)], 
@p1 = 28/03/2013 21:17:04 [Type: DateTime (0)]

Nice work NHibernate!