IQueryable Extension to Generate Temporal Table "AS OF" Queries

Question

Having failed to find a satisfying solution, let me post this here:

We're using NHibernate as our ORM and are just beginning to use Sql Server temporal tables. We therefore need some kind of extension to IQueryable (or the HQL Builder or an InterceptingProvider or something) that will allow us to add the "AS OF" clause to our queries, something like

var results = session.Query<Company>
.Where(c => c.Name == "FogCreek")
.AsOf(DateTime.Today.AddYears(-1));

Answer 1

I've been struggling with it too and gave up as I lost too much time trying to find better approach...

So far I've injected FOR SYSTEM_TIME AS OF into SQL using custom HqlGeneratorForMethod but this gave me invalid SQL expression. So I had to fix it in OnPrepareStatement. This is hacky and not elegant solution but works for most simple cases.

Please look at my solution here and feel free to reply if you find better solution