casting string to bool using nHibernate Criteria

Question

I have an nHibernate query using Criteria, and I am trying to cast a string to bool in the query itself. I have done the same with casting a string to int, and that works well (the "DataField" property is "1" as a string):

var result = Session
   .CreateCriteria<Car>()
   .Add(Restrictions.Eq((Projections.Cast(NHibernateUtil.Int32,
    Projections.Property("DataField"), 1))
   .List<Car>();

tx.Commit();

But I am trying to do the same with bool, but I do not get the expected result:

var result = Session
   .CreateCriteria<Car>()
   .Add(Restrictions.Eq((Projections.Cast(NHibernateUtil.bool,
    Projections.Property("DataField"), true))
   .List<Car>();

tx.Commit();

"DataField" is the string "True", but the result in an empty list, where it should contain 100 elements with the "DataField" property string set to "True". I have tried with the string "true", and "1", but the result is still an empty List.

[EDIT]

As Commented below, I could check for the string "True" or "False", but I would say this is a more general question than just for the Boolean.

Note that the idea is to have some sort of key value representation of the data, where the value can be different data types. I need the value table to contain all data, so storing the data as string seems like the cleanest solution!

I have been able to use the method above to store both int and double as string, and to the cast in the query, but I have not succeeded using the same method for DateDime and Boolean.

And for DateTime it is crucial to have the actual DateTime object.

How can I make the cast from string to bool, and string to DateTime work in the queries?

Thanks

Answer 1

Unfortunately, what you are trying to achieve is going to be hard to do, because it goes against the grain of what the nHibernate and the RDBMs is trying to do for you. By using untyped data you will be sidestepping a lot of the gains that using a RDBMs gives you.

Without a full schema, I can only guess. How do you know what the correct type for the field is? I'm guessing you have a 'type' column that indicates if the value is an integer, boolean, date etc. If you do, continue with a type identifier column, plus a separate column for each data type. This is no more complex than what you are doing, since there is already separate queries for each data type, and you get clarity, type checking, and the possibility of indexing.

If you want to guard against the possibility of there being more than one value defined (i.e. values in multiple type columns) you can create a constraint on the table that verifies that each row only defines a value for at most one datatype. (You may also verify that the column for the specified type is not null, if that's appropriate for your case.)

Altenratively, you can have nHibernate manage the different types and let it do all the heavy lifting for you. nHibernate can map class hiearchies to tables, so you could create entities like this:

public class AbstractProperty
{
   // concrete name - persisted
   public String Name { get; set; etc.. }

   // owner property as well?

   // abstract value provided by subclasses. This property is not persisted.
   // used simply to provide polymorphic access to the value.
   public abstract Object Value { get; set; }
}

public class DateProperty : AbstractProperty
{
   // concrete date property
   public Date date { get; set; etc.. } 

   // value delegates to date property
   public Object value { get; set; }
}

With this scheme in place, you have the option to retrieve values with a specific data type, where, for example, the query uses the DateProperty entity explicity returns DateProperty instances. You can also write queries that might return multiple types, where the static type is AbstractProperty. You can then use the visitor pattern or 'is' checks on the AbstractProperty to determine the type and cast to a concrete type to fetch the value.

A nHibernate guru may be able to help you fix the cast, but in the long run, I recommend using real datatypes in your database. It will save you headaches later on.