NHibernate QueryOver in IUserType

Question

First let me apologize for the length of this post, this is basically code, although I hope you all carry with me!

I have a script for working with an outdated database where I needed to write IUserType using NHibernate 3.2 to take a 2-digit status field and return a boolean value from it. The status field may contain 3 possible values:

* 'DI' // 'Disabled', return false * ' ' // blank or NULL, return true * NULL 

Here is what I have simplified.

Table definition:

 CREATE TABLE [dbo].[Client]( [clnID] [int] IDENTITY(1,1) NOT NULL, [clnStatus] [char](2) NULL, [clnComment] [varchar](250) NULL, [clnDescription] [varchar](150) NULL, [Version] [int] NOT NULL ) 

Free match:

 public class ClientMapping : CoreEntityMapping<Client> { public ClientMapping() { SchemaAction.All().Table("Client"); LazyLoad(); Id(x => x.Id, "clnId").GeneratedBy.Identity(); Version(x => x.Version).Column("Version").Generated.Never().UnsavedValue("0").Not.Nullable(); OptimisticLock.Version(); Map(x => x.Comment, "clnComment").Length(250).Nullable(); Map(x => x.Description, "clnDescription").Length(250).Nullable(); Map(x => x.IsActive, "clnStatus").Nullable().CustomType<StatusToBoolType>(); } } 

IUserType implementation:

 public class StatusToBoolType : IUserType { public bool IsMutable { get { return false; } } public Type ReturnedType { get { return typeof(bool); } } public SqlType[] SqlTypes { get { return new[] { NHibernateUtil.String.SqlType }; } } public object DeepCopy(object value) { return value; } public object Replace(object original, object target, object owner) { return original; } public object Assemble(object cached, object owner) { return cached; } public object Disassemble(object value) { return value; } public new bool Equals(object x, object y) { if (ReferenceEquals(x, y)) return true; if (x == null || y == null) return false; return x.Equals(y); } public int GetHashCode(object x) { return x == null ? typeof(bool).GetHashCode() + 473 : x.GetHashCode(); } public object NullSafeGet(IDataReader rs, string[] names, object owner) { var obj = NHibernateUtil.String.NullSafeGet(rs, names[0]); if (obj == null) return true; var status = (string)obj; if (status == " ") return true; if (status == "DI") return false; throw new Exception(string.Format("Expected data to be either empty or 'DI' but was '{0}'.", status)); } public void NullSafeSet(IDbCommand cmd, object value, int index) { var parameter = ((IDataParameter) cmd.Parameters[index]); var active = value == null || (bool) value; if (active) parameter.Value = " "; else parameter.Value = "DI"; } } 

However, this does not work. This unit test fails with an inaccurate count.

 [TestMethod] public void GetAllActiveClientsTest() { //ACT var count = Session.QueryOver<Client>() .Where(x => x.IsActive) .SelectList(l => l.SelectCount(x => x.Id)) .FutureValue<int>().Value; //ASSERT Assert.AreNotEqual(0, count); Assert.AreEqual(1721, count); } 

The reason it fails is because it generates the following SQL:

 SELECT count(this_.clnID) as y0_ FROM Client this_ WHERE this_.clnstatus = @p0; /* @p0 = ' ' [Type: String (0)] */ 

But I need this to generate this:

 SELECT count(this_.clnID) as y0_ FROM Client this_ WHERE (this_.clnstatus = @p0 <b> OR this_.clnstatus IS NULL);</b> 

After some debugging, I saw that the NullSafeSet () method in my StatusToBoolType class was called before the request was created, so I managed to get around this by writing some hacker code in this method to control SQL in cmd. CommandText Property

 ... public void NullSafeSet(IDbCommand cmd, object value, int index) { var parameter = ((IDataParameter) cmd.Parameters[index]); var active = value == null || (bool) value; if (active) { parameter.Value = " "; if (cmd.CommandText.ToUpper().StartsWith("SELECT") == false) return; var paramindex = cmd.CommandText.IndexOf(parameter.ParameterName); if (paramindex > 0) { // Purpose: change [columnName] = @p0 ==> ([columnName] = @p0 OR [columnName] IS NULL) paramindex += parameter.ParameterName.Length; var before = cmd.CommandText.Substring(0, paramindex); var after = cmd.CommandText.Substring(paramindex); //look at the text before the '= @p0' and find the column name... var columnSection = before.Split(new[] {"= " + parameter.ParameterName}, StringSplitOptions.RemoveEmptyEntries).Reverse().First(); var column = columnSection.Substring(columnSection.Trim().LastIndexOf(' ')).Replace("(", ""); var myCommand = string.Format("({0} = {1} OR {0} IS NULL)", column.Trim(), parameter.ParameterName); paramindex -= (parameter.ParameterName.Length + column.Length + 1); var orig = before.Substring(0, paramindex); cmd.CommandText = orig + myCommand + after; } } else parameter.Value = "DI"; } 

But this is NHibernate !!! Hacking sql statements like this might not be the right way to handle this? Right?

Since this is a common obsolete database, I cannot change the table schema to NOT NULL, otherwise I would just do this and avoid this scenario.

So finally, after all this foreplay, my question is just like this, where can I tell NHibernate to create a custom SQL criteria statement for this IUserType?

Thank you all in advance!

Answer 1

I decided!

After I submitted my question, I returned to the drawing board, and I came up with a solution that does not require hacking the generated SQL in the IUserType implementation. In fact, this solution does not need IUserType at all!

Here is what I did.

First, I modified the IsActive column to use a formula to handle null validation. This fixed my problem with the QueryOver error, because now every time NHibernate deals with the IsActive property, it introduces my sql formula to handle the null value.

The disadvantage of this approach was that after I placed the formula, all of my save tests failed. It turns out that the properties of the formula are indeed ReadOnly properties.

So, to get around this problem, I added a protected entity property to store the status value from the database.

I then changed the IsActive property to set the protected status property to either "or" DI. And finally, I changed FluentMapping to show the protected Status property in NHibernate so that NHibernate can track it. Now that NHibernate knows about the status, it can include it in its INSERT / UPDATE statements.

I am going to include my solution below if anyone is interested.

Customer class

 public class Client { ... protected virtual string Status { get; set; } private bool _isActive; public virtual bool IsActive { get { return _isActive; } set { _isActive = value; Status = (_isActive) ? " " : "DI"; } } } 

Free Display Changes

 public class ClientMapping : CoreEntityMapping<Client> { public ClientMapping() { .... Map(Reveal.Member<E>("Status"), colName).Length(2); Map(x => x.IsActive).Formula("case when clnStatus is null then ' ' else clnStatus end"); } }