I have a legacy app with a nullable DateTime column in the database -- a NULL value is used to represent +Infinity. My app uses a fairly standard NHibernate + DDD setup, including Fluent-NHibernate and Linq2NHib.
Let's assume I have the following C# class that represents an entity.
class Discount
{
DateTime? ExpirationDate { get; set; }
// ... etc.
}
It turns out there are rules governing this ExpirationDate
that I want to encapsulate, for instance, it must be at midnight and can have the value of Infinity
. In the legacy app NULL == Infinity
just like in the DB. I want to convert it to something more like this set of classes:
class Discount
{
OpenBusinessDate ExpirationDate { get; set; } // assume not nullable
}
class OpenBusinessDate // immutable
{
private DateTime _Value;
private bool _IsInfinity;
OpenBusinessDate(DateTime? value)
{
if (null == value)
{
_IsInfinity = true;
_Value = DateTime.MaxValue; // or SqlDateTime.MaxValue if you must
}
else
{
ErrorIfNotMidnight(value);
_Value = value;
}
}
DateTime ToDateTime() { return _Value; }
// ... casters, comparison methods, etc...
}
I do not currently have the option to convert all existing NULLs in the DB to a constant, but I would love to query inside my domain with something like this...
IList<Discount> GetAll(OpenBusinessDate expiringAfterDate)
{
return (from d in session.Linq<Discount>()
where d.ExperationDate > expiringAfterDate
select d).ToList();
}
...and have NH know to translate into this...
SELECT * FROM Discount
WHERE (ExpirationDate IS NULL
OR ExpirationDate > @expiringAfterDate)
/* ...or possibly this... */
SELECT * From Discount
WHERE (COALESCE(ExpirationDate, '9999-12-31') > @expiringAfterDate)
I've been taking a look at User Types in NH and have made an IUserType to convert from Infinity
to NULL
and back (along with actual DateTime), but I haven't discovered how to get the query to be written like I want. That is, right now, the above Linq and my code would produce the query:
SELECT * FROM Discount
WHERE (ExpirationDate > 'Infinity')
/* b/c OpenBusinessDate.ToString() on Infinity
produces "Infinity" for debugging purposes */
Does anyone have any suggestions on where to look or have a similarly working example? I can't seem to find the right set of keywords to find a match to something I assume is a solved problem. Is this purely a NH problem to solve, or will this also involve some work with Linq2NH?
The way I would do it is map a protected property for the ExpirationDate and then expose a public readonly property for the OpenBusinessDate like so:
And then override the mapping of the discount class like so:
then in your linq query you'll be able to apply the infinity logic yourself like so