Wednesday, May 20, 2009

NHibernate and DateTime

I’ve been working on a WPF application which is maybe another post. The work is done and I have started localizing the applications. Localization was mostly chanding the FlowDirection and lables / texts for other languages, but when testing the application, I came across an intresting problem with Nhibernate. The application worked correctly in default (en-us) culture, but not in fa-ir, as NHibernate reported problems when running the working query. Here’s the problem:

[Test]
public void Can_Run_Query_In_Persian_Culture()
{
using(new CultureContext(new PersianCultureInfo()))
{
using (UnitOfWork.Start())
{
var todayStart = new DateTime(DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day, 0, 0, 0, CultureHelper.DefaultCalendar);
var todayEnd = new DateTime(DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day, 23, 59, 59, CultureHelper.DefaultCalendar);

var queryable = UnitOfWork.CurrentSession.Linq<Purchase>();
var query = from sales in queryable
where sales.PurchaseDate >= todayStart && sales.PurchaseDate <= todayEnd
select sales;

Assert.DoesNotThrow(() => query.ToList());
}
}
}
The test which is totally working on other cultures, fails miserably in fa-ir. The reason is somewhere in the criteria parameter being converted to string. Here’s the output of the query being run on the database:

[SELECT this_.PurchaseId as PurchaseId4_0_, this_.PaidValue as PaidValue4_0_, this_.PurchaseDate as Purchase3_4_0_, this_.Quantity as Quantity4_0_, this_.IsOwed as IsOwed4_0_, this_.Product_id as Product6_4_0_, this_.PurchaseRegisteredBy_id as Purchase7_4_0_, this_.Purchaser_id as Purchaser8_4_0_ from `Purchase` this_ WHERE (this_.PurchaseDate >= ? and this_.PurchaseDate <= ?) ]

Positional parameters: #0>1388/02/30 12:00:00 ق.ظ #1>1388/02/30 11:59:59 ب.ظ

It is obvious that date values are converted to String using current Culture of the running thread, while the conversion should use invariant culture instead:

WRONG:

var dateValue = todayStart.ToString();
RIGHT:
var dateValue = todayStart.ToString(CultureInfo.InvariantCulture);
Any workaround for this? I haven’t found any yet. In fact I still do not exactly know if this is NH core problem or the Linq extensions and since I’m running from the trunk, I’m not sure if this is reproducible on released versions.

Update : The problem was actually related to JET Driver of NHibernate Contrib project. A patch is created and sent to authorities.
Submit this story to DotNetKicks Shout it

No comments: