Smooth nhibernate error when converting 't' 'f' to boolean "String was not recognized as a valid boolean".

Question

I had a problem getting a record from a database with a boolean column. I cannot change the database structure.
The database type is character (1) (PostgreSQL), where they used 't' for true and 'f' for false. I used PostgreSQLDialect.

I tried putting this in a hibernate configuration

<property name="query.substitutions">1 't',0 'f'</property> 

I tried to override the dialect

  public override string ToBooleanValueString(bool value) { return value ? "t" : "f"; } 

Display:

 Map(x => x.IsTemplate).Column("template_p"); 

Still not working, Any help?

Answer 1

You may need to create your own user type. Here is an example of creating your own:

http://lostechies.com/rayhouston/2008/03/23/mapping-strings-to-booleans-using-nhibernate-s-iusertype/

Then your mapping will look something like this:

 Map(x => x.IsTemplate).Column("template_p").CustomType<MyCustomType>(); 

Edit:

You can also use the standard YesNo type by doing something with your wildcard queries. I have not tested this, but there may be something like this:

 <property name="query.substitutions">yes 't', no 'f'</property> 

Your mapping will look something like the above, except that you will use the YesNo type.

Answer 2

Indeed, Cole W, I made this style and works like a charm (source link: https://lostechies.com/rayhouston/2008/03/23/mapping-strings-to-booleans-using-nhibernate-s-iusertype/ )

 public class TFType : IUserType { public bool IsMutable { get { return false; } } public Type ReturnedType { get { return typeof(TFType); } } public SqlType[] SqlTypes { get { return new[]{NHibernateUtil.String.SqlType}; } } public object NullSafeGet(IDataReader rs, string[] names, object owner) { var obj = NHibernateUtil.String.NullSafeGet(rs, names[0]); if(obj == null ) return null; var truefalse = (string) obj; if( truefalse != "t" && truefalse != "f" ) throw new Exception(string.Format("Expected data to be 't' or 'f' but was '{0}'.", truefalse)); return truefalse == "t"; } public void NullSafeSet(IDbCommand cmd, object value, int index) { if(value == null) { ((IDataParameter) cmd.Parameters[index]).Value = DBNull.Value; } else { var yes = (bool) value; ((IDataParameter)cmd.Parameters[index]).Value = yes ? "t" : "f"; } } 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(); } } 

Answer 3

Didn't try, but maybe this might work:

 Map(x => x.IsTemplate).Formula("case when template_p = 't' then 1 else 0 end") 

Answer 4

We use postgres 8.3-8.4 with boolean values ​​without any problems. However, when using an ANTLR3-based HQL analyzer, it began to be discriminating about boolean substitution, so we made our own dialect with the following redefinition:

 public override string ToBooleanValueString( bool value ) { return value ? "true" : "false"; } 

However, we used NHibernate.Dialect.PostgreSQL82Dialect, not a generic one. What version of postgres are you working on?

EDIT: Oh, sorry, I did not follow that you had a character column (1). maybe this will help instead (with quotes)?

 return value ? "'t'" : "'f'";