Databases
StackOverflow2013
Postsdbo
POMapping and Querying UserTypes - Nullable in DB to Not-Nullable in Domain
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMapping and Querying UserTypes - Nullable in DB to Not-Nullable in Domain
    text
    copied!<p>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.</p> <p>Let's assume I have the following C# class that represents an entity.</p> <pre><code>class Discount { DateTime? ExpirationDate { get; set; } // ... etc. } </code></pre> <p>It turns out there are rules governing this <code>ExpirationDate</code> that I want to encapsulate, for instance, it must be at midnight and can have the value of <code>Infinity</code>. In the legacy app <code>NULL == Infinity</code> just like in the DB. I want to convert it to something more like this set of classes:</p> <pre><code>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... } </code></pre> <p>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...</p> <pre><code>IList&lt;Discount&gt; GetAll(OpenBusinessDate expiringAfterDate) { return (from d in session.Linq&lt;Discount&gt;() where d.ExperationDate &gt; expiringAfterDate select d).ToList(); } </code></pre> <p>...and have NH know to translate into this...</p> <pre><code>SELECT * FROM Discount WHERE (ExpirationDate IS NULL OR ExpirationDate &gt; @expiringAfterDate) /* ...or possibly this... */ SELECT * From Discount WHERE (COALESCE(ExpirationDate, '9999-12-31') &gt; @expiringAfterDate) </code></pre> <p>I've been taking a look at User Types in NH and have made an IUserType to convert from <code>Infinity</code> to <code>NULL</code> 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:</p> <pre><code>SELECT * FROM Discount WHERE (ExpirationDate &gt; 'Infinity') /* b/c OpenBusinessDate.ToString() on Infinity produces "Infinity" for debugging purposes */ </code></pre> <hr> <p><strong>Does anyone have any suggestions on where to look or have a similarly working example?</strong> 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?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload