0

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));
Michael
  • 1,351
  • 1
  • 11
  • 25

1 Answers1

0

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

veeroo
  • 752
  • 6
  • 25
  • Thank you very much. My current thinking is that I'll simply map the history tables separately and use their timestamp fields in regular queries. This does *not* help if I want a query that will retrieve data from *either* the history table *or* the primary table, depending on the timestamp -- for that I would still need an AsOf. – Michael Nov 10 '18 at 16:03
  • If you always query for exact one year backwards, you could consider writing database views using `FOR SYSYEM_TIME AS OF... ` and map them with schema binding. This would simplify whole query if you have multiple joins as `FOR SYSYEM_TIME AS OF... `statement is applied properly only for temporal tables so you don't have to worry about it – veeroo Nov 10 '18 at 20:06