NHibernate 4.x实现SQL Server 2016时态表支持的优化方案咨询

Your current workaround using a WHERE clause hack gets the job done, but it’s not semantically correct and could lead to unexpected behavior down the line. Let’s explore cleaner approaches that properly inject the FOR SYSTEM_TIME AS OF clause between the table name and its alias—matching the correct temporal table syntax.

Approach 1: Custom Entity Persister #

NHibernate relies on entity persisters to generate SQL for entity operations. By creating a custom persister, you can override how table references are rendered in SELECT statements, directly inserting the temporal clause where it belongs.

First, create a persister that extends NHibernate’s default implementation:

public class TemporalTableEntityPersister : SingleTableEntityPersister
{
    private readonly DateTime? _asOfDate;

    public TemporalTableEntityPersister(
        IEntityMetadata metadata, 
        ISessionFactoryImplementor sessionFactory, 
        IMapping mapping,
        DateTime? asOfDate) : base(metadata, sessionFactory, mapping)
    {
        _asOfDate = asOfDate;
    }

    protected override string GetTableName(string alias)
    {
        if (_asOfDate.HasValue && typeof(IAuditable).IsAssignableFrom(EntityType))
        {
            var formattedDate = _asOfDate.Value.ToString("yyyy-MM-dd HH:mm:ss");
            return $"{TableName} FOR SYSTEM_TIME AS OF '{formattedDate}' {alias}";
        }
        return base.GetTableName(alias);
    }
}

Next, register this persister via a custom factory:

public class TemporalEntityPersisterFactory : DefaultEntityPersisterFactory
{
    private readonly DateTime? _asOfDate;

    public TemporalEntityPersisterFactory(DateTime? asOfDate)
    {
        _asOfDate = asOfDate;
    }

    public override IEntityPersister CreateEntityPersister(
        IEntityMetadata metadata, 
        ISessionFactoryImplementor sessionFactory, 
        IMapping mapping)
    {
        if (typeof(IAuditable).IsAssignableFrom(metadata.EntityType))
        {
            return new TemporalTableEntityPersister(metadata, sessionFactory, mapping, _asOfDate);
        }
        return base.CreateEntityPersister(metadata, sessionFactory, mapping);
    }
}

Use this factory when opening sessions that need temporal context—this approach works great if you need temporal support across all operations (not just queries) for IAuditable entities.

Approach 2: Custom HQL AST Visitor #

If you primarily use HQL queries, a custom AST visitor can modify the FROM clause nodes to include the temporal clause during query parsing.

Create the visitor:

public class TemporalTableHqlVisitor : DefaultHqlSqlWalker
{
    private readonly DateTime _asOfDate;

    public TemporalTableHqlVisitor(ISessionFactoryImplementor factory, DateTime asOfDate) : base(factory)
    {
        _asOfDate = asOfDate;
    }

    public override IASTNode Visit(IASTNode node)
    {
        if (node is FromElement fromElement && typeof(IAuditable).IsAssignableFrom(fromElement.EntityType))
        {
            var formattedDate = _asOfDate.ToString("yyyy-MM-dd HH:mm:ss");
            fromElement.TableName += $" FOR SYSTEM_TIME AS OF '{formattedDate}'";
        }
        return base.Visit(node);
    }
}

Apply it to your HQL queries:

public IQueryable<T> QueryAsOf<T>(ISession session, DateTime asOfDate) where T : IAuditable
{
    var hqlQuery = session.CreateQuery($"from {typeof(T).Name}");
    var walker = new TemporalTableHqlVisitor(session.SessionFactory as ISessionFactoryImplementor, asOfDate);
    hqlQuery.SetHqlSqlWalker(walker);
    return hqlQuery.List<T>().AsQueryable();
}

Approach 3: Refined Session Interceptor #

While you considered SessionInterceptor.OnPrepareStatement, you can make this more robust using a SQL parser (like Microsoft.SqlServer.TransactSql.ScriptDom) to safely modify the FROM clause instead of error-prone string manipulation:

public class TemporalTableInterceptor : EmptyInterceptor
{
    private readonly DateTime _asOfDate;

    public TemporalTableInterceptor(DateTime asOfDate)
    {
        _asOfDate = asOfDate;
    }

    public override SqlString OnPrepareStatement(SqlString sql)
    {
        var parser = new TSql130Parser(false);
        var fragment = parser.Parse(new StringReader(sql.ToString()), out var errors);
        if (errors.Count > 0)
            return base.OnPrepareStatement(sql);

        foreach (var selectStmt in fragment.BatchStatements.OfType<SelectStatement>())
        {
            foreach (var tableRef in selectStmt.QueryExpression.FromClause.TableReferences.OfType<NamedTableReference>())
            {
                var entityMetadata = Session.SessionFactory.GetAllClassMetadata().Values
                    .FirstOrDefault(m => m.TableName.Equals(tableRef.MultiPartIdentifier.Identifiers.Last().Value, StringComparison.OrdinalIgnoreCase));
                
                if (entityMetadata != null && typeof(IAuditable).IsAssignableFrom(entityMetadata.EntityType))
                {
                    tableRef.ForSystemTimeClause = new ForSystemTimeClause
                    {
                        SystemTimeType = SystemTimeType.AsOf,
                        AsOfDateTime = new Literal { Value = $"'{_asOfDate:yyyy-MM-dd HH:mm:ss}'" }
                    };
                }
            }
        }

        var generator = new Sql130ScriptGenerator();
        var modifiedSql = generator.GenerateScript(fragment, out _);
        return new SqlString(modifiedSql);
    }
}

Attach the interceptor to your session:

using var session = sessionFactory.OpenSession(new TemporalTableInterceptor(new DateTime(2018, 1, 16)));
var historicalEntities = session.Query<YourAuditableEntity>().ToList();

Which Approach to Choose? #

• Custom Persister: Best for full temporal support across all entity operations (queries, saves, updates) for IAuditable types.
• HQL Visitor: Ideal if you’re primarily using HQL and want temporal logic tied directly to query construction.
• Interceptor: Most flexible—works with both HQL and LINQ queries—though it adds a small overhead from SQL parsing.

All three approaches will generate the correct SQL syntax:
SELECT * FROM Table FOR SYSTEM_TIME AS OF '2018-01-16 00:00:00' t0

内容的提问来源于stack exchange，提问作者veeroo