NHibernate query with Projections.Cast to DateTime

Question

I'm experimenting with using a string for storing different kind of data types in a database. When I do queries I need to cast the strings to the right type in the query itself. I'm using .Net with NHibernate, and was glad to learn that there exists functionality for this.

For the example I'm using this simple class:

public class Foo
{
    public string Text { get; set; }
}

I successfully use Projections.Cast to cast to numeric values, e.g. the following query correctly returns all Foos with an interger stored as int - between 1-10.

var result = Session.CreateCriteria<Foo>()
    .Add(Restrictions.Between(Projections.Cast(NHibernateUtil.Int32, Projections.Property("Text")), 1, 10))
    .List<Foo>();

Now if I try using this for DateTime I'm not able to make it work no matter what I try. Why?!

var date = new DateTime(2010, 5, 21, 11, 30, 00);
AddFooToDb(new Foo { Text = date.ToString() } ); // Will add it to the database...

var result = Session
    .CreateCriteria<Foo>()
    .Add(Restrictions.Eq(Projections.Cast(NHibernateUtil.DateTime, Projections.Property("Text")), date))
    .List<Foo>();

Answer 1

Since Projections.Cast executes in the DB, your RDBMS probably doesn't like the format stored by date.ToString().

A quick fix that will also be easier in the DB is doing the conversion in the client instead:

.Add(Restrictions.Eq("Text", date.ToString()))

Now, in order for those dates to be used in sorting or range expressions, you need to make sure the format is appropriate.

So, instead of using .ToString(), which also has the problem of changing according to the current culture, use .ToString("o") or .ToString("s").