Entity Framework SQL query string to get history data from temporal tables throwing a data-table incompatible error

Question

I had same problem explained in this question.

Found a great answer by Matt in the same post which helped me a lot. It works for insert and update queries. I'm using IDbCommandTreeInterceptor and Ignored ValidFrom and ValidTo columns.

Now I'm trying to query some history data. As Entity Framework Linq queries cant get data from temporal tables, I'm forced to used SQL query string.

var result = this.Context.VehicleSets.SqlQuery("SELECT Id, Name FROM T.VehicleSet FOR SYSTEM_TIME ALL WHERE Id = 1").ToList();

This line throws an error in run-time.

The data reader is incompatible with the specified 'Model.VehicleSet'. A member of the type, 'ValidFrom', does not have a corresponding column in the data reader with the same name.

Matt in his answer has set clause for insert and update commands. I tried using DbQueryCommandTree to set same clause for querying, but DbQueryCommandTree doesn't have SetClauses command.

Any help will be very much appreciated.

Answer 1

In the SQL "Select" query, you need to include all the fields that you have in the VehicleSets class... appart from "Id" and "Name" fields, you need to have at least a Start and End dates (that hold the temporal data...).

* (I know it's a late reply, but it may help others) *