NHibernate Formula based property + PostgreSQL interval

Question

I'm using NHibernate with Mapping by Code and I have a property that is created by this formula.

Property(x => x.IsInOverdue,
    mapper => mapper
      .Formula("(SELECT (state_ <> 3 AND invoice_uniqueAlias.duedate_ < NOW()) " + 
               " FROM _invoice AS invoice_uniqueAlias  "+ 
               " WHERE invoice_uniqueAlias.invoice_id = issuedinvoice_key)"));

It works perfectly, this sql is inserted as subselect in all queries...

But I would need to add 1 day to invoice_uniqueAlias.duedate_ value. We are using PostgreSQL where the syntax for it is: invoice_uniqueAlias.duedate_ + interval '1 day'

But when I put it in mapper.Formula, NHibernate thinks that interval is a name of column and in all queries tries to add table prefix before interval keyword. The generated SQL then looks like:

... (SELECT (issuedinvo0_.state_ <> 3 
  AND (invoice_uniqueAlias.duedate_ + (issuedinvo0_.interval '1 day')) < NOW()) ...

I tried to put interval keyword in [, `, put statement interval + '1 day' to brackets, but it didn't help. Any suggestions how to handle it correctly in NHibernate or how it is possible to write it in Postgres without using + interval syntax?

Answer 1

In case, we need NHibernate to treat some words (key words) as part of the underlying DB engine dialect, we have to just extend it.

One way would be the create custom dialect:

public class CustomPostgreDialect : PostgreSQL82Dialect
{
    public CustomPostgreDialect()
    {
        RegisterKeyword("interval");
    }
}

And now just use it:

<property name="dialect">My.Namespace.CustomPostgreDialect,My.Data</property>

Some similar issue - Using SQL CONVERT function through nHibernate Criterion (with the similar solution in this answer)