Error when querying temporal table through a mapped Sql Function

[wdhenrik]

I have a temporal table in my database that I'm using in a Sql TVF. I've also associated the sql function to a C# method. When I call that method through EF, the PeriodStart & PeriodEnd date are always looked for by EF. Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'PeriodEnd'. Invalid column name 'PeriodStart'

I've stripped back the TVF to be a simple statement, with explicit column references instead of Select * and EF still looks for the temporal columns. As a test, I removed the temporal status and the function mapping performs correctly.

Steps to recreate:

1. Add a simple sql tvf to select data from a table, mapped to a C# method.
2. Call the c# method from the context, it should return results
3. Change the table to temporal
4. Repeat 2, the method call will fail with the Invalid Column Name error.

[ajcvickers]

This issue is lacking enough information for us to be able to fully understand what is happening. Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

[wdhenrik]

SqlFunctionsOnTemporalTable.zip Steps to recreate using the sample:

1. Update-Database to create database and objects. ConnectionString is set in Model.cs. Blogs is created as a temporal table.
2. Execute project. Index page fails with SqlException: Invalid column name 'PeriodEnd'. Invalid column name 'PeriodStart'.
3. In Model.cs, line 30, change the IsTemporal setting to false. (migration not needed at this point)
4. Execute the project. Index should show 0 blogs and posts.

When IsTemporal is true, the model is adding PeriodStart & PeriodEnd to the expected columns list and failing when it can't find them in the result set. The TVFs created in the initial migration do not use Select * (line 78-79). Changing the Blog to not be a temporal table in the model, even if still temporal in the database, makes the TVF work as expected as it is no longer expecting the Period columns.

[wdhenrik]

I think I've figured this out and it likely just needs to be a small documentation update. (or maybe another reason for #26463)

With a non-temporal table deployed through EF, all properties mapped to the table are also included in Select *, meaning the TVF doesn't need an updated definition for new properties, just a refresh.

However, Select * doesn't include the Period columns from a temporal table because they are hidden. This means a TVF must always use an explicit column list, including the Period columns. The unfortunate side effect is that the TVF select columns must always be updated whenever a new property is added/removed from the model. And missing that step won't raise the exception until run-time.

[RascoApps]

I just came across this issue as well.