Using OffsetDateTime with NHibernate

Question

I'm trying to map NodaTime's OffsetDateTime type to SQL Server but I'm not sure how to solve the impedance between NodaTime's OffsetDateTime and SQL Server's DateTimeOffset types.

The main problem I have is getting LINQ support to work correctly because OffsetDateTime does not have comparison operators such as <. The systems also differ on how they handle equality. NodaTime considers both the instant in time and the offset while SQL Server only considers the instant in time.

2015-12-24 11:18+01:00 and 2015-12-24 10:18+00:00 are considered equal in SQL Server, but are not equal in NodaTime.

I considered using ICompositeUserType to store the the UTC date/time and the offset in separate columns (similar to before SQL Server 2008) but OffsetDateTime does not have a UTC/Instant property. As such I can't see how to get date.ToInstant() in LINQ queries to map correctly to a property in the ICompositeUserType.

Answer 1

OffsetDateTime should map to a SQL Server datetimeoffset. NHibernate's support for custom type mappings via the IUserType as described in this article will be essential to making this work.

Though OffsetDateTime doesn't directly implement IComparable, you can use OffsetDateTime.Comparer.Instant to compare them. That still may be difficult to use in LINQ queries, but it's one route to explore.

Someone should probably write an NHibernate-NodaTime integration package to make this simpler. Considering I've done this before for RavenDB and for Dapper, I'll take it under consideration. :)

Sorry, I don't have a better actual "do this" kind of answer for you.

---

UPDATE

I started working on this, and successfully built an IUserType for OffsetDateTime, but it doesn't work for comparison operators - for the very reason you described. I believe the solution involves extending the linq provider similar to the technique described in this blog post. I don't have a complete working example yet, but I will update here when I do.

Ultimately, you won't be able to write:

session.Query<Foo>().Where(x => x.SomeODT > value)

Because OffsetDateTime doesn't have the comparison operators so it won't compile.

Instead, the LINQ provider should be extended to support something like this:

session.Query<Foo>().Where(x => OffsetDateTime.Comparer.Instant.Compare(x.SomeODT, value) > 0)

Or perhaps more cleanly as:

session.Query<Foo>().Where(x => x.SomeODT.ToInstant() > value.ToInstant())

Or perhaps both. Both will compile, but will throw an exception without proper support in the LINQ provider.

---

SECOND UPDATE

Someone beat me to it. There are now a set of extensions for NHibernate to support Noda Time data types in this project. :)