I have a scenario dealing with a legacy database, I need to write an IUserType using NHibernate 3.2 to get a 2-character “status” field and return a boolean value from it. The status field can contain 3 possibilities Value:
*'DI' //'Disabled', return false
* '' // blank or NULL, return true
* NULL
This is what I 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
)
Fluent mapping:
public class ClientMapping: CoreEntityMapping
{
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").Lengt h(250).Nullable();
Map(x => x.Description, "clnDescription").Length(250).Nullable();
Map(x => x.IsActive, " clnStatus").Nullable().CustomType();
}
}
My 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;
}
< br /> public new bool Equals(obje ct 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 = valu e == null || (bool) value;
if (active)
parameter.Value = "";
else
parameter.Value = "DI";
}
}
But this does not work. This unit test failed and the count is inaccurate.
[TestMethod]
public void GetAllActiveClientsTest()
{
//ACT
var count = Session.QueryOver()
.Where(x => x.IsActive)
.SelectList(l => l.SelectCount(x => x.Id))
.FutureValue().Value;
//ASSERT
Assert.AreNotEqual (0, count);
Assert.AreEqual(1721, count);
}
The reason for the failure is that it generates the following SQL:
SELECT count(this_.clnID) as y0_ FROM Client this_ WHERE this_.clnstatus = @p0;
/* @p0 = '' [Type: String (0)] */
< p>But I need it to generate this:
SELECT count(this_.clnID) as y0_ FROM Client this_ WHERE (this_.clnstatus = @p0 OR this_. clnstatus IS NULL);
After some debugging, I found that the NullSafeSet() method in my StatusToBoolType class was called before generating the query, so I was able to write some h ackish code to manipulate the SQL in the cmd.CommandText property to solve this problem.
...
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! Isn’t it possible that cracking SQL statements like this is the right way to deal with this problem? Correct?
Because it is a shared legacy database, I cannot change the table mode to NOT NULL, otherwise I will do this and avoid this situation.
So in the end I After the question, my question is like this, where can I tell NHibernate to generate a custom SQL conditional statement for this IUserType?
Thank you everyone!
After I posted my question, I went back to the drawing board and I came up with a solution that does not require cracking the generated SQL in the IUserType implementation. Actually this solution No need for IUserType at all!
This is what I did.
First, I changed the IsActive column to use a formula to handle the null check. This solved the problem of QueryOver failing because now every time NHibernate handles IsActive It will inject my sql formula to deal with null when it comes to attributes.
The disadvantage of this method is that after I enter the formula, all my save tests fail. It turns out that the formula attributes are valid ReadOnly attribute.
So, in order to solve this problem, I added a protected attribute to the entity to save the state value in the database.
Next, I changed the IsActive attribute to The protected status attribute is set to “” or “DI”. Finally I changed the FluentMapping to display the protected Status attribute as NHibernate so that NHibernate can track it. Now that NHibernate knows the Status, it can include it in the INSERT/UPDATE statement .
I will include my solution below in case anyone else 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";
}
}
}
Changes to Fluent mapping
public class ClientMapping: CoreEntityMapping
{
public ClientMapping()
{
....
Map(Reveal.Member("Status"), colName).Length(2);
Map(x => x.IsActive). Formula("case when clnStatus is null then '' else clnStatus end");
}
}
First let me write for this article Apologies for the length, it is mainly code, so I hope you all bear with me!
I have a scenario dealing with a legacy database, I need to write an IUserType using NHibernate 3.2 to get a 2-character “status” field and return a boolean value from it. The status field can contain 3 possibilities Value:
*'DI' //'Disabled', return false
* '' // blank or NULL, return true
* NULL
This is what I 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
)
Fluent mapping:
public class ClientMapping: CoreEntityMapping
{
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).Nul lable();
Map(x => x.Description, "clnDescription").Length(250).Nullable();
Map(x => x.IsActive, "clnStatus").Nullable ().CustomType();
}
}
My 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";
}
}
But this does not work. This unit test failed and the count is not accurate.
[TestMethod]
public void GetAllActiveClientsTest()< br />{
//ACT
var count = Session.QueryOver()
.Where(x => x.IsActive)
.SelectList(l => l.SelectCount(x => x.Id))
.FutureValue().Value;
//ASSERT
Assert.AreNotEqual(0, count);
Assert.AreEqual(1721, count);
}
The reason for the failure is that 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 it To generate this:
SELECT count(this_.clnID) as y0_ FROM Client this_ WHERE (this_.clnstatus = @p0 OR this_.clnstatus IS NULL);< /b>
After some debugging, I found that the NullSafeSet() method in my StatusToBoolType class was called before generating the query, so I was able to manipulate cmd by writing some hackish code in this method. CommandT The SQL in the ext attribute solves this problem.
...
public void NullSafeSet(IDbCommand cmd, object value, int index)
{< br /> var parameter = ((IDataParameter) cmd.Parameters[index]);
var active = value == null || (bool) value;
if (active)
{< br /> 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.Parame terName}, 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! Isn’t it possible that cracking SQL statements like this is the right way to deal with this problem? Correct?
Because it is a shared legacy database, I cannot change the table mode to NOT NULL, otherwise I will do this and avoid this situation.
So in the end I After the question, my question is like this, where can I tell NHibernate to generate a custom SQL conditional statement for this IUserType?
Thank you everyone!
Solved!
After I posted my question, I went back to the drawing board and I came up with a solution that does not require cracking the generated SQL in the IUserType implementation. Actually this solution No need for IUserType at all!
This is what I did.
First, I changed the IsActive column to use a formula to handle the null check. This solved the problem of QueryOver failing because now every time NHibernate handles IsActive It will inject my sql formula to deal with null when it comes to attributes.
The disadvantage of this method is that after I enter the formula, all my save tests fail. It turns out that the formula attributes are valid ReadOnly attribute.
So, in order to solve this problem, I added a protected attribute to the entity to save the state value in the database.
Next, I changed the IsActive attribute to The protected status attribute is set to “” or “DI”. Finally I changed the FluentMapping to display the protected Status attribute as NHibernate so that NHibernate can track it. Now that NHibernate knows the Status, it can include it in the INSERT/UPDATE statement .
I will include my solution below in case anyone else 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";
}
}
}
Changes to Fluent mapping
public class ClientMapping: CoreEntityMapping
{
public ClientMapping()
{
. ...
Map(Reveal.Member("Status"), colName).Length(2);
Map(x => x.IsActive).Formula(" case when clnStatus is null then '' else clnStatus end");
}
}