How to fix NHibernate unnecessary update statements

Almost every application nowadays consist of data manipulations. Except for heavy algorithmic applications; we usually insert what user entered to a database (maybe apply a couple of business logic before inserting), execute a select query against database, update or delete one of the records. It does not matter if you are developing a mobile app or a web app, you will easily find yourself in a situation where you type lots of SQL statements in your application. Whenever you create a table in your favorite DMBS, you will definitely write a select query to list the records, an insert query to populate it, and presumably update / delete statements for future use. This situation happens a lot, and such code is seen to be useless most of the time. However, recent developments in software engineering resulted in ORM tools, where Entity Framework and NHibernate are two major players for .NET framework in this field.

I have been using NHibernate (for ORM) and  Fluent NHibernate (for mapping by code) excessively in my projcets. NHibernate serves a great purpose, allowing developer to focus on business logic instead of writing tons of useless code. However, I face problems with NHibernate every now and then, and this is one of the most interesting situation among them.

You can directly jump to
Steps to reproduce
Problem
Solution

Steps

Assume we have a class for Customers as described here:

public class Customer
{
    public virtual int Id { set; get; }
    public virtual string Name { set; get; }
    public virtual string Surname { set; get; }
    public virtual string Address { set; get; }
    public virtual CustomerStatus Status { set; get; }
}

Although all properties are self explanatory,  we need to focus on Status property of which type is enum. CustomerStatus enum is described as such:

public enum CustomerStatus
{
    Active,
    Pending,
    Blocked,
    Blacklisted
}

Lets also define mapping class for our model:

public class CustomerMap: ClassMap
{
	public CustomerMap()
	{
		Id(c => c.Id);
		Map(c => c.Name);
		Map(c => c.Surname);
		Map(c => c.Address);

		Map(c => c.Status);
	}
}

When mapping is done this way, NHibernate will map Status property to a column of which type is varchar. When you force NHibernate to build database schema, this is what you will get:

Database schema generated by NHibernate

At this point, you may want to insert a couple of records to our newly created table. Here is a sample:

var session = DB.CurrentSession;
using (var tx = session.BeginTransaction())
{
	var customer = new Customer();
	customer.Name = "Sadullah";
	customer.Surname = "Ceran";
	customer.Address = "Istanbul, Turkey";
	customer.Status = CustomerStatus.Active;
	session.Save(customer);

	customer = new Customer();
	customer.Name = "Ozben";
	customer.Surname = "Micoogullari";
	customer.Address = "Bratislava, Slovakia";
	customer.Status = CustomerStatus.Pending;
	session.Save(customer);

	customer = new Customer();
	customer.Name = "Mark";
	customer.Surname = "Zuckerberg";
	customer.Address = "California, USA";
	customer.Status = CustomerStatus.Pending;
	session.Save(customer);

	tx.Commit();
}

When you insert those records, here is how our table look in our favorite client:

Database filled with our sample data

Problem

Up to this point, everything looks perfect. However, you may want to use integer values of enumerations in database instead of string values for a couple of reasons. For instance, size of an integer is generally is less than size of a string, indexing is better when you use an integer, you may need to change texts of enumeration in the future etc. Simply, you may change mapping class of our precious model “Customer” to something like this:

public class CustomerMap: ClassMap
{
	public CustomerMap()
	{
		Id(c => c.Id);
		Map(c => c.Name);
		Map(c => c.Surname);
		Map(c => c.Address);

		Map(c => c.Status).CustomType<int>();
	}
}

When you make NHibernate build database scheme with mapping above and insert same records as previously, you will get this table:

Alternative scheme generated by NHibernate

Populated Database with alternative mapping

That is exactly what we want. Let us select our records for tabular display, and also observe what queries are executed by nhibernate.
Here is piece of code to do that:

var logger = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
var session = DB.CurrentSession;

var customerList = session.QueryOver<Customer>().List<Customer>();

foreach(var customer in customerList)
{
	logger.DebugFormat("Customer is acquired. Id:{0}, Name:{1}, Surname:{2}", customer.Id, customer.Name, customer.Surname);
}

Here is what is printed on log file (to be sure we selected records):

Customer is acquired. Id:1, Name:Sadullah, Surname:Ceran
Customer is acquired. Id:2, Name:Ozben, Surname:Micoogullari
Customer is acquired. Id:3, Name:Mark, Surname:Zuckerberg

Let’s see which sql queries Nhibernate executed during the process above:

DEBUG 2012-09-01 20:54:19,295  1867ms SQL                    LogCommand         - 
SELECT
	this_.Id as Id0_0_,
	this_.Name as Name0_0_,
	this_.Surname as Surname0_0_,
	this_.Address as Address0_0_,
	this_.Status as Status0_0_ 
FROM
	`Customer` this_;
UPDATE
	`Customer` 
SET
	Name = ?p0,
	Surname = ?p1,
	Address = ?p2,
	Status = ?p3 
WHERE
	Id = ?p4;
?p0 = 'Sadullah' [Type: String (8)], ?p1 = 'Ceran' [Type: String (5)], ?p2 = 'Istanbul, Turkey' [Type: String (16)], ?p3 = Active [Type: Int32 (0)], ?p4 = 1 [Type: Int32 (0)]
UPDATE
	`Customer` 
SET
	Name = ?p0,
	Surname = ?p1,
	Address = ?p2,
	Status = ?p3 
WHERE
	Id = ?p4;
?p0 = 'Ozben' [Type: String (5)], ?p1 = 'Micoogullari' [Type: String (12)], ?p2 = 'Bratislava, Slovakia' [Type: String (20)], ?p3 = Pending [Type: Int32 (0)], ?p4 = 2 [Type: Int32 (0)]
UPDATE
	`Customer` 
SET
	Name = ?p0,
	Surname = ?p1,
	Address = ?p2,
	Status = ?p3 
WHERE
	Id = ?p4;
?p0 = 'Mark' [Type: String (4)], ?p1 = 'Zuckerberg' [Type: String (10)], ?p2 = 'California, USA' [Type: String (15)], ?p3 = Pending [Type: Int32 (0)], ?p4 = 3 [Type: Int32 (0)]

“Dennis Ritchie’s beard!. What the hell are those update statements? I just wanted to select bloody rows, I have never changed those properties” you may say (I did at least). To understand what is happening, we should understand how nhibernate updates work.

NHibernate holds an “isModified” flag for all persistent objects. This isModified flag is initially “false” for persistent records. When you flush a session or commit a transaction, NHibernate checks persistent objects if their attributes have been changed. When you change name of a customer for example, nhibernate compares old value and the new one, and decides if the record is dirty (aka ismodified). On the other hand, when you map an enumeration property as integer to database, NHibernate fails to properly compare old and new values, thus resulting an update for the record in any case. That is the cause of unnecessary update statements seen in our logs.

Solution

Solution to this problem is very simple compared to the problem itself. Changing a single line in our mapping class will fix the issue. No unnecessary updates will be executed against database.

public class CustomerMap: ClassMap
{
	public CustomerMap()
	{
		Id(c => c.Id);
		Map(c => c.Name);
		Map(c => c.Surname);
		Map(c => c.Address);

		Map(c => c.Status).CustomType(typeof(CustomerStatus));
	}
}

This configuration results the following log instead of the one above:

DEBUG 2012-09-01 20:54:19,295  1867ms SQL                    LogCommand         - 
SELECT
	this_.Id as Id0_0_,
	this_.Name as Name0_0_,
	this_.Surname as Surname0_0_,
	this_.Address as Address0_0_,
	this_.Status as Status0_0_ 
FROM
	`Customer` this_;

You can download source code here.

About Sadullah Ceran

Ex-Paraglider, Motorcycle rider, Computer Engineer.