Get SYSTEM_TIME information from a temporal table on Entity Framework code first method

Question

I created my temporal table following instructions on this link "Cutting Edge - Soft Updates with Temporal Tables"

The procedure generally takes two steps:

1. create the regular table; this is just the kind of work that Code First usually does
2. alter the table by adding SysStartTime, SysEndTime columns and turning on the SYSTEM_VERSIONING setting

Everything looks good so far.

However, please note that the entity class doesn't have SysStartTime property and SysEndTime property as they are added later. This gives me trouble as I need to get information of SysStartTime from table.

My question is: How can I get it with Entity Framework?

The link also says:

In EF 6, you can only leverage the SqlQuery method of the DbSet class:

using (var db = new EF6Context())
{
  var current = db.Bookings.Single(b => b.Id == 1);
  var time = DateTime.Now.AddMinutes(-5);
  var old = db.Bookings
    .SqlQuery("SELECT * FROM dbo.Bookings
          FOR SYSTEM_TIME AS OF {0} WHERE Id = 1", time)
    .SingleOrDefault();
}

Note that for EF 6, the column names returned in the query need to match the property names on the class. This is because SqlQuery doesn’t use mappings. If column and property names don’t match, then you’d need to alias the columns in the SELECT list, rather than just SELECT *.

I have no clue how to do this or if it solves my problem. Does anyone have any experience on this?

I can think of one solution is to added an extra column AppliedTime on my table by adding AppliedTime to my entity class. It has (almost, but good enough for me) same value as SysStartTime.

Another solution could be use plain SQL to query the table directly.

Thanks.

Answer 1

I have been worked on this one and found a solution. It is actually quite simple. Just use Database.SqlQuery Method

DateTime time = context.Database.SqlQuery<DateTime>("SELECT SysStartTime 
                                                     FROM dbo.Calibration 
                                                     WHERE CalibrationID = 1") 
                                                     .SingleOrDefault();

See Also: Raw SQL Queries