如何通过Fluent NHibernate查询SQL Server时态表的历史数据？

因为NHibernate没有内置支持SQL Server时态表的FOR SYSTEM_TIME AS OF语法，下面提供几种可行的实现方式：

1. 直接使用原生SQL查询 #

这是最直接的方案，完全贴合你需要的SQL语法，适合临时或简单查询场景：

var targetTime = new DateTime(2022, 12, 28, 21, 0, 0, DateTimeKind.Utc);
var results = session.CreateSQLQuery(@"
    SELECT * FROM [MyDB].[dbo].[MyTable] 
    FOR SYSTEM_TIME AS OF :targetTime
    WHERE YourColumn = :filterValue
")
.SetParameter("targetTime", targetTime, NHibernateUtil.DateTimeOffset)
.SetParameter("filterValue", "yourFilter")
.SetResultTransformer(Transformers.AliasToBean<MyDTO>())
.List<MyDTO>();

注意用参数化查询避免SQL注入，同时通过AliasToBean将查询结果映射到MyDTO。

2. 自定义拦截器实现QueryOver/LINQ支持 #

如果需要复用时态表查询逻辑，且想保持NHibernate的查询语法风格，可以通过自定义拦截器在SQL生成阶段插入时态表子句：

第一步：实现查询拦截器 #

public class TemporalTableInterceptor : EmptyInterceptor
{
    private DateTimeOffset? _asOfTime;

    public void SetAsOfTime(DateTimeOffset asOfTime)
    {
        _asOfTime = asOfTime;
    }

    public override SqlString OnPrepareStatement(SqlString sql)
    {
        if (_asOfTime.HasValue && sql.ToString().StartsWith("SELECT", StringComparison.OrdinalIgnoreCase))
        {
            // 提取SQL中的目标表名（简单实现，复杂SQL需调整逻辑）
            var tableMarker = "FROM [MyDB].[dbo].[MyTable]";
            if (sql.ToString().Contains(tableMarker))
            {
                var temporalClause = $" FOR SYSTEM_TIME AS OF '{_asOfTime.Value:yyyy-MM-ddTHH:mm:ss.fffffff}'";
                sql = sql.Replace(tableMarker, tableMarker + temporalClause);
            }
        }
        return base.OnPrepareStatement(sql);
    }
}

第二步：使用拦截器执行QueryOver查询 #

var asOfTime = new DateTimeOffset(2022, 12, 28, 21, 0, 0, TimeSpan.Zero);
var interceptor = new TemporalTableInterceptor();
interceptor.SetAsOfTime(asOfTime);

using (var session = sessionFactory.WithOptions().Interceptor(interceptor).OpenSession())
{
    var results = session.QueryOver<MyDTO>()
        .Where(x => x.YourColumn == "yourFilter")
        .List();
}

这种方式可以让你继续使用熟悉的QueryOver语法，拦截器会自动为目标表添加时态表子句。

3. 使用NamedQuery封装固定查询逻辑 #

如果查询逻辑固定，可以将时态表查询定义为命名查询，在Fluent NHibernate映射中配置：

public class MyDTOMap : ClassMap<MyDTO>
{
    public MyDTOMap()
    {
        Table("MyTable");
        // 配置实体属性映射...
        
        NamedQuery("GetMyTableAsOfTime")
            .SetQuery(@"
                SELECT * FROM [MyDB].[dbo].[MyTable] 
                FOR SYSTEM_TIME AS OF :targetTime
                WHERE YourColumn = :filterValue
            ")
            .SetResultTransformer(Transformers.AliasToBean<MyDTO>());
    }
}

调用命名查询：

var targetTime = new DateTimeOffset(2022, 12, 28, 21, 0, 0, TimeSpan.Zero);
var results = session.GetNamedQuery("GetMyTableAsOfTime")
    .SetParameter("targetTime", targetTime)
    .SetParameter("filterValue", "yourFilter")
    .List<MyDTO>();

这种方式适合将常用的时态表查询逻辑封装起来，便于统一维护。

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