Mapping and Querying UserTypes - Nullable in DB to Not-Nullable in Domain

260 views Asked by At

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?

1

There are 1 answers

1
lomaxx On

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:

public class Discount
{
    private DateTime _value;
    protected DateTime? ExpirationDate 
    {
        get { return _value; }
        set { 
                _value = value; 
                ExpirationDate = new OpenBusinessDate(value); 
            }
    }
    public OpenBusinessDate OpenExpirationDate {get; private set;}
}

And then override the mapping of the discount class like so:

public PersonMap : ClassMap<Discount>
{  
    public PersonMap()  
    {  
        Map(Reveal.Property<Discount>("ExpirationDate ")) 
    }  
}

then in your linq query you'll be able to apply the infinity logic yourself like so

 return (from d in session.Linq<Discount>
           where d.ExpirationDate > expiringAfterDate || d.ExpirationDate != null
           select d).ToList();