NHibernate HQL Generator to support SQL Server 2016 temporal tables

Question

I am trying to implement basic support for SQL Server 2016 temporal tables in NHibernate 4.x. The idea is to alter SQL statement from

SELECT * FROM Table t0

to

SELECT * FROM Table FOR SYSTEM_TIME AS OF '2018-01-16 00:00:00' t0

You can find more info about temporal tables in SQL Server 2016 here

Unfortunately, I've not found any way to insert FOR FOR SYSTEM_TIME AS OF '...' statement between table name and its alias. I'm not sure if custom dialects supports this. The only working solution I have for now is to append FOR SYSTEM_TIME statement within extra WHERE and my output SQL looks like this

SELECT * FROM Table t0 WHERE FOR SYSTEM_TIME AS OF '2018-01-16 00:00:00'=1

To do so, I have implemented generator and dialect as follows:

public static class AuditableExtensions
{
    public static bool AsOf(this IAuditable entity, DateTime date)
    {
        return true;
    }

    public static IQueryable<T> Query<T>(this ISession session, DateTime asOf) where T : IAuditable
    {
        return session.Query<T>().Where(x => x.AsOf(asOf));
    }
}

public class ForSystemTimeGenerator : BaseHqlGeneratorForMethod
{
    public static readonly string ForSystemTimeAsOfString = "FOR SYSTEM_TIME AS OF";

    public ForSystemTimeGenerator()
    {
        SupportedMethods = new[]
        {
            ReflectionHelper.GetMethod(() => AuditableExtensions.AsOf(null, DateTime.MinValue))
        };
    }

    public override HqlTreeNode BuildHql(MethodInfo method, Expression targetObject, 
        ReadOnlyCollection<Expression> arguments,
        HqlTreeBuilder treeBuilder, 
        IHqlExpressionVisitor visitor)
    {
        return treeBuilder.BooleanMethodCall(nameof(AuditableExtensions.AsOf), new[]
        {
            visitor.Visit(arguments[1]).AsExpression()
        });
    }
}

public class MsSql2016Dialect : MsSql2012Dialect
{
    public MsSql2016Dialect()
    {
        RegisterFunction(nameof(AuditableExtensions.AsOf), new SQLFunctionTemplate(
            NHibernateUtil.Boolean, 
            $"{ForSystemTimeGenerator.ForSystemTimeAsOfString} ?1?2=1"));
    }
}

Can anyone provide any better approach or samples I could use to move forward and insert FOR SYSTEM_TIME AS OF statement between table name and its alias? At this moment the only solution I can see is to alter SQL in OnPrepareStatement in SessionInterceptor but I believe there is some better approach...

Fell

The latest dialect in the repo is MsSql2012 – Thegn

Furthermore, the FOR SYSTEM_TIME AS OF clause is a hint for the table, not a boolean predicate for the Where clause. – Thegn

I know, is it possible to append it from generator? – Fell

Based on the comment by @PanagiotisKanavos you could look at this as a staring point. You wouldn't be able to use a straight append but would have to search for the table name and insert...possibly after the alias? Just an idea. – Stipulation

Thanks but I am looking for a solution where I won't have to alter generated SQL manually as SqlString in SessionInterceptor. Still believe there is a better solution using HqlGenerator... :) – Fell

Did you ever come up with a better solution? – Rare

Not yet, sorry... We still struggle with this in our project. I will share better solution with you as soon I develop it :) – Fell

It is hacky, but possible to alter this invalid SqlString and move FOR SYSTEM_TIME AS OF part before table alias but it is possible only from OnPrepareStatement. It would be great to generate valid SQL in HqlGenerator but I am not sure if it is possible – Fell

Answer 1

There is information on using temporal tables with NHibernate in the NHibernate Reference 5.1 at NHibernate Reference

The example in section 19.1 shows how to use temporal tabvles:

First define a filter:

<filter-def name="effectiveDate">
<filter-param name="asOfDate" type="date"/>
</filter-def>

Then attach this to a class:

<class name="Employee" table="Employee For System_Time All" ...>
   ...
   <many-to-one name="Department" column="dept_id" class="Department"/>
   <property name="EffectiveStartDate" type="date" column="eff_start_dt"/>
   <property name="EffectiveEndDate" type="date" column="eff_end_dt"/>
   ...
   <!--
   Note that this assumes non-terminal records have an eff_end_dt set to
   a max db date for simplicity-sake
   -->
   <filter name="effectiveDate"
   condition=":asOfDate BETWEEN eff_start_dt and eff_end_dt"/>
</class>

Then you need to enable the filter on the session:

   ISession session = ...;
   session.EnableFilter("effectiveDate").SetParameter("asOfDate", DateTime.Today);
   var results = session.CreateQuery("from Employee as e where e.Salary > :targetSalary")
   .SetInt64("targetSalary", 1000000L)
   .List<Employee>();

Hope this gets people started.

Loge

And where in you example is temporal table? It's just a filter added to WHERE statement – Schmidt

Answer 2

Create an UDF (with the desired time as a parameter) and call it instead of using the table directly.

Platinotype

Answer 3

DECLARE @String1 NVARCHAR(100),
    @String2 NVARCHAR(100) ,
    @DateTime DATETIME= '2018-01-16 00:00:00';

 SET @String1 = 'SELECT * FROM Table2';
 SET @String2 = @String1 + ' FOR SYSTEM_TIME AS OF '''
 + CONVERT(VARCHAR(20), @DateTime, 120) + '''';
 SELECT  @String2;

Cowboy