Add support for OData Temporal Data Exension

[ScottLeWarne]

Would like to request adding support for the four temporal query options defined in the OData Extension for Temporal Data ($at, $from, $to, and $toInclusive). Ideally support for these could be implemented in the same manner as the ISearchBinder where the library does the heavy lifting and allows for providing a binder that returns an expression used to augment the eventual EF query.

[julealgon]

@ScottLeWarne can you provide some docs about these extensions for folks who are not aware of them?

[ScottLeWarne]

Absolutely, sorry for the ommission.

The extension spec is documented here: https://docs.oasis-open.org/odata/odata-temporal-ext/v4.0/cs01/odata-temporal-ext-v4.0-cs01.html

thx.

[xuzhg]

@mikepizzo @chrisspre What's the 'support' policy for this temporal extension?

[woojunfeng]

Any progress？

[xuzhg]

@woojunfeng @ScottLeWarne So far, we haven't had a customer requirement for this feature. Would you please share your usage/requirement that can help us to prioritize it? Besides, The feature is open for contribution.

[julealgon]

@woojunfeng @ScottLeWarne So far, we haven't had a customer requirement for this feature. Would you please share your usage/requirement that can help us to prioritize it?

We could probably leverage this right away for auditing purposes. Temporal tables are great to track and query changes over time which is almost always good enough for basic auditing capabilities.

The fact that EFCore now supports temporal queries directly and OData does not would be a potential blocker for us in a few scenarios like that where simple auditing is required. We'd have to be forced to use a custom bound function backed by a manual EFCore temporal query to provide the results which is obviously a lot less flexible.

[xuzhg]

@woojunfeng @ScottLeWarne So far, we haven't had a customer requirement for this feature. Would you please share your usage/requirement that can help us to prioritize it?

We could probably leverage this right away for auditing purposes. Temporal tables are great to track and query changes over time which is almost always good enough for basic auditing capabilities.

The fact that EFCore now supports temporal queries directly and OData does not would be a potential blocker for us in a few scenarios like that where simple auditing is required. We'd have to be forced to use a custom bound function backed by a manual EFCore temporal query to provide the results which is obviously a lot less flexible.

@mikepizzo looking for inputs.

[danroot]

Landed on this looking for similar. Apparently temporal support is in OData4 (not sure of adoption status.) and it is in EFCore. It seems a natural fit.

In our case, we have a /data/AccountingPeriods endpoint mapped to EFCore AccountingPeriod entity mapped to SQL Server. We've enabled temporal tables for this entity in EFCore, but now need to wire up in OData so that users can see the history of when a period was opened and closed and by whom. We would like to be able to do queries like /data/AccountingPeriods(123)?$from=2024-09-01&$to=2024-09-31 or /data/AccountingPeriods(123)?$at=2024-09-10 Currently, since there is no native temporal functionality in ASP.NET Core Odata, we will just expose a non-OData endpoint for the historical data. Not a big deal, but if there is a standard we would prefer that approach.

Some other example use cases, admittedly not real world for us:

• We have enabled temporal tables for the Order entity. Users would like to be able to see the history of changes to a given Order entity over time in the user interface. The UI currently uses OData to query /data/Orders(123) and get the current order. We would like to also query /data/Orders(123)?$from=2024-09-01&$to=2024-09-31 to show the change history.
• We have a temporal table of Employees mapped to the Employee entity. Their ID stays the same, but their names and job titles change. Managers want to see a list of employee changes based on the Employees history table. Our UI needs to query /data/Employees?$expand=history($select=Name,Jobtitle)&$from=2012-03-01&$to=2025-01-01

[wertzui]

I would also like to see this.

Our use case is that we want to audit configurations, so we know who changed what and when.