SQL Server temporal tables

[shivajiraot]

Does EF Core support code first approach for Temporal tables? Looking to find a way to use Temporal tables with EF Core code first approach. Please guide me to the article if any existing already. Thank you.

[cgountanis]

Does the EF 3.0 version have this functionality? Also would is hidden equals true solve this problem without the annotations?

[cgountanis]

Using EF 2.4 (stable)...

We set the columns "Is Hidden" via the DbProject to true and it seems to work as it did before the versioned table changes. Not sure I need more than that right now. The application just cares about recent data, the reporting can worry about the historical changes. Just confirming Is Hidden does work if you do db-first and do not want to add annotations after ever scaffold manually.

[chassq]

Not sure. If it seems to work for you then maybe it is fine. Not sure what EF has in store down the road for temporal table support. From our experience if you need the properties in your model then the attributes seem to work.

[cgountanis]

Sure, the goal would be to use .FromSql($"SELECT * FROM dbo.ProductDetails FOR SYSTEM_TIME AS OF {{0}}", date.ToUniversalTime()) but for now IsHidden gets the job done and we can stick with the stable release of EF for the time being.

[vertonghenb]

If we add support for this we should be able to handle historic include as well So you can include historic data, for example Let's say that an Invoice is not seen as a Temportal Entity, however the products and Customer information stated on the invoice are. If we want to query the Invoice on a given date and time:

public class Product {
public int Id {get;set;}
public string Name {get;set;}
// [...] Other fields left out but imagine some other info here.
}

public class Customer{
public int Id {get;set;}
public string Name {get;set;}
// [...] Other fields left out but imagine some address, phone, etc info here
}

DateTime historicDateTime = // some historic datetime, let's say the date the invoice was created
context.Invoices
.TemporalInclude(x => x.Product, historicDateTime ) //this info should be coming from the temporal tables
.TemporalInclude(x => x.Customer,historicDateTime ) //this info should be coming from the temporal tables
.SingleOrDefault(x => x.Id == id)

[glautrou]

The NuGet package EfCoreTemporalTable allows you to easily perform temporal queries with EF Core.

Example:

var result = myDbContext.Employees
    .AsTemporalOf(DateTime.UtcNow.AddDays(-1))
    .Include(i=> i.Company)
    .FirstOrDefault(i => i.Name == "Lautrou");

These are the available extensions:

• AsTemporalAll()
• AsTemporalAsOf(date)
• AsTemporalFrom(startDate, endDate)
• AsTemporalBetween(startDate, endDate)
• AsTemporalContained(startDate, endDate)

It returns an IQueryable<T> so you can easily use it and it generates clean parameterized SQL.

The source code of the package is available on GitHub. (DISCLAIMER: I'm the author.)

If you think my package is useful I would be very happy to suggest my code as a PR in EF Core :) /cc @rowanmiller @ajcvickers

[ajcvickers]

@glautrou Thanks--I will discuss with the team. In the meantime, consider sending a PR to add the extension here: https://github.com/aspnet/EntityFramework.Docs/blob/master/entity-framework/core/extensions/index.md

[ErikEJ]

@glautrou Added to my PR here: https://github.com/aspnet/EntityFramework.Docs/pull/1864

[ajcvickers]

@glautrou A couple of people on the team took a look and while this seems like a good workaround it is likely we would implement this differently in the main code base. Specifically, not using raw SQL and with appropriate integration with the model metadata.

[glautrou]

@ajcvickers Thanks for your reply. Yes we can avoid raw SQL and integrate with model metadata for columns like I did for table names. However for the notation I didn't find any other intuitive way using something different than a DbSet extension (because of Include, especially for different temporal types). Do you think there is a better alternative for that?

[bbrandt]

Updated: See Adam-Langley post below for a solution more in line with the design of EF Core.

@glautrou I have a library, EFCore.TimeTraveler, that I just received approval from my company to publish as open source that may be of interest to you. It's still in very early stages as I've only used it for a prototype and not production, but it allows EF Core to load related data, from navigation properties, Include(), etc, from temporal table history .

The problem I am trying to solve is that I have a good amount of LINQ queries and other code written and tested, but now a requirement has come in requiring loading the same set of data at a prior System Time. The Point-in-time analysis (time travel) usage scenario for SQL Server Temporal Tables pretty well described what I am wanting to do.

To time travel enable EF Core, I am using the new DbCommandInterceptor functionality to inject the required FOR SYSTEM_TIME AS OF @asOf for each temporal table. A using(TemporalQuery.AsOf(asOfDateTime)) statement is used to define an ambient context to that communicates with the TimeTravelInterceptor. The TimeTravelInterceptor will only affect tables related to entity types that have been configured with entityTypeBuilder.EnableTemporalQuery().

EFCore.TimeTraveler

• Allow full-featured Entity Framework Core queries against SQL Server Temporal Tables.

Here's a usage example I came up with. Pardon the silliness of the domain.

    var appleCurrentState = await context.Apples
        .Include(apple => apple.Worms)
        .Where(a => a.Id == appleId)
        .AsNoTracking()
        .SingleAsync();   

    appleCurrentState.Worms.Count().Should().Be(3);

    using (TemporalQuery.AsOf(ripeAppleTime))
    {
        var applePriorState = await context.Apples
            .Include(apple => apple.Worms)
            .Where(a => a.Id == appleId)
            .AsNoTracking()
            .SingleAsync();                     

        applePriorState.Worms.Count().Should().Be(0);
    }

Update: EFCore.TimeTraveler is now listed on nuget.org: https://www.nuget.org/packages/EFCore.TimeTraveler/

Restrictions: Since history is immutable, all EF queries within a TemporalQuery.AsOf(targetTime) block must use .AsNoTracking(). This avoids the DBContext getting confused and caching prior state data as the current state of the data. In a future release, the disabling of change tracking for temporal queries may be automatic if I ever figure out the best way to do this. PR's are welcome.

@ajcvickers Sorry, there's likely not much in the EFCore.TimeTraveler that would be of use to you and your team. EFCore.TimeTraveler is intended as a stop gap for working with SQL Server Temporal tables with the current version of EFCore. I consider any type of SQL interception/rewrite to be a bit of a hack, so I very much look forward to what the EF Core team comes up with as an official feature at which point I will gladly deprecate EFCore.TimeTraveler if it is no longer of any use.

[MoazAlkharfan]

Just a caveat i found so people would take notice.

From my initial tests to query an entity which have several changes produces a list with multiple results with equal reference to each other.

example query:

db.Table1
    .FromSql("SELECT * FROM [Table1] FOR SYSTEM_TIME ALL)
    .Where(x => x.Id == someid)
    .ToList()

All of the entities materialized with the above query will be a single entity added to the list x times.

now if we change the query to include a projection it will produce the proper expected results:

db.Table1
    .FromSql("SELECT * FROM [Table1] FOR SYSTEM_TIME ALL")
    .Where(x => x.Id == someid)
    .Select(x => new Table1Model {
        Id = x.Id,
        ChangedProperty = x.ChangedProperty,
        ...(other properties)
    })
    .ToList()

[Adam-Langley]

Thanks to everyone who has contributed to this thread with their ideas and suggestions. I've just published an extension to EF to add temporal table query support. The implementation follows the same AST visit and generate model used for all other language support internally in EF Core 3.1. Feel free to submit suggestions/PRs (and/or tests if you feel really generous).

https://github.com/Adam-Langley/efcore-temporal-query

[bbrandt]

@ajcvickers What do you think about pulling @Adam-Langley 's code into dotnet/efcore? Would you be open to a PR with the code from https://github.com/Adam-Langley/efcore-temporal-query along with some tests to cover it?

The thing that I could think of that would be missing would be first class support for EF Migrations, but currently there are a few 3rd party libs to cover this aspect.

[ajcvickers]

@bbrandt It's not clear that the implementation there is the way we would want to it in the EF code. In particular, the integration with the query pipeline is not ideal.

It may be better to come up with a proposal for the behaviors and API surface that should be introduced, which we can then discuss. After that, we can figure out what the implementation should look like.

[Adam-Langley]

@bbrandt, @ajcvickers agreed - there were some nasty hacks I had to introduce to get it working.

Some starting points for an API discussion:

• how to carry annotations on expression nodes through the query pipeline? I had to derive TableExpression, which seems like a simple annotation bag could preclude the need for this.
• I had some trouble dealing with how to both replace services, and add new services at the same time. If using the internal service provider, then the lifecycles of the various internal services are pre-defined - however one cannot add a new service without switching to external service provisioning - in which case to make all the requisite services available for SQL Server, we have to copy a big chunk of internal code.

[ajcvickers]

@smitpatel to answer first question.

[smitpatel]

TableExpressionBase can have infra similar to metadata to carry annotation. Some form of IAnnotatable.

[ajcvickers]

I had some trouble dealing with how to both replace services, and add new services at the same time. If using the internal service provider, then the lifecycles of the various internal services are pre-defined - however one cannot add a new service without switching to external service provisioning - in which case to make all the requisite services available for SQL Server, we have to copy a big chunk of internal code.

This should be less of an issue if adding this first-class in the EF Core code. I started some docs on the internal service provider that may be useful: https://github.com/dotnet/EntityFramework.Docs/pull/2066

[smitpatel]

Scoping issue:

• When you apply AsOf or any other temporal table clause, does it apply to all the tables in the query or just the table you called it for. It has same characteristic as #6717 . It is not invalid to say that give me all customers for Jan 1, 2020 but all their addresses till that date. In that case, how would you apply to other tables brought in explicitly via db set or implicitly via navigations.
• AsOf and siblings must be SqlServer specific methods.
• Mechanism to transport should be annotations over TableExpressionBase.
• It should not cause query cache pollution.

Temporal table uses history table when additional syntax used after table name. T-Sql allows to do update/delete/select over history table. Another API question would be how to track past data and generate update/delete. How to access history table from DbSet API. For query the existence of method could imply use of history table. (Just like how T-Sql interpret). We can scope it to be read-only for now. So no update/delete supported over history table. Due to this, any query which uses history API must be non-tracking.

[Adam-Langley]

Hi @smitpatel, current implementation: AsOf applies to the entire query, joins included.

My use case was for data synchronisation, so the queries would have been more complex if I had to opt-in all navigations.

I see, hinting could be used to implement the ultimate "scoped" AsOf.

I was considering something like this for the opt-in scenario:

// grab all last week's orders for customer 1, but latest customer record.
// Goal is to keep the fluent API as similar to current, but
// when using SQLServer, additional arguments (asOf) are available.
_db.Customers.Where(x => x.Id == 1).Include(x => x.Orders, asOf: _lastWeek);

Agreed, read-only for now. I hadn't even considered the need to mutate the history.

[weitzhandler]

When you implement this, please do so in an abstract manner, so it's easier to be brought over to other provides, such as Npgsql (@roji).

[roji]

_db.Customers.Where(x => x.Id == 1).Include(x => x.Orders, asOf: _lastWeek);

Note the recently-introduced filtered includes, which seems like a more natural way to express temporal filtering when including.

PostgreSQL seems behind on this... there does seem to be the temporal_tables extension, but I can't see any update since 2017 nor does it seem to support any special query syntax). Here are some additional resources.

BTW here's an article describing the SQL 2011 standardization of temporal tables. Not sure if SQL Server is conformant (or whether it matters).

[ErikEJ]

Also note that there are currently no less than four extensions that support SQL Server temporal in one way or other - what if all those good people created a good PR?

[ajcvickers]

@maumar is going to do some investigation here so that we can start to get a clearer idea of what support EF should add.

[bbrandt]

In addition to being able to time shift using AsOf() in LINQ, it would be useful to be able to apply the time shift in a cross-cutting way, similar to the way Global Query Filters are able to work using instance level fields on the DbContext.

// The current DbContent HasQueryFilter() instance level field example
modelBuilder.Entity<Blog>().HasQueryFilter(b => EF.Property<string>(b, "_tenantId") == _tenantId);

// Proposed Global Temporal filtering: 
// _asOfTime is an instance level fields on the DbContext 
// representing the time travel date for all temporal
// tables in the DbContext that have been configured as below.
// When _asOfTime is null, no temporal shift occurs.
modelBuilder.Entity<Blog>().HasAsOfFilter(_asOfTime);

As @smitpatel stated previously, any results you get back from the history table (when _asOfTime is not null in the case above) the returned results would need to be .AsNoTracking() and are not updatable in the DB.

Note: Using the term "filter" in this case could be confusing, so terminology may need to change. The proposed HasAsOfFilter(_asOfTime); may need to take a lambda to correctly reference the instance level fields on the DbContext?

[smitpatel]

@bbrandt - I am not sure if such a model level global filter is warranted for AsOf. Model level filters are for query parts which are universal for all queries with an opt-out in very few queries where you want an exception. If there is no separate entity in model for history table data then the main entity is going to be used mainly for purpose of querying the base table rather than history table.

[jzabroski]

@glautrou

The source code of the package is available on GitHub. (DISCLAIMER: I'm the author.)

Any interest in backporting this to EF6 (may be able to sponsor such efforts)? Or any thoughts on whether it is even feasible to do so? If you are interested in discussing, please see my GitHub profile for contact information. You can also create a "Let's Connect" issue in my Home repository.

[jzabroski]

@roji

PostgreSQL seems behind on this... there does seem to be the temporal_tables extension, but I can't see any update since 2017 nor does it seem to support any special query syntax). Here are some additional resources.

Note that the special query syntax is just sugar. You can easily replicate the index traversal behavior using CTE syntax in SQL Server. See here: https://github.com/jzabroski/Home/blob/master/Tsql/TemporalTables/README.md

[roji]

One additional note on PostgreSQL... PostgreSQL supports first-class range types over any other type, which have a lower and upper bound. For example, you can have a range of ints [3..8), and there are operators for (efficiently) checking if something is contained in a range. It seems that ranges of timestamps are sometimes used to implement at least some of the temporal functionality, although this isn't the SQL Standard syntax/behavior.

[jzabroski]

Yeah, Postgres is pretty awesome. These days I keep asking why I bother using SQL Server. The short answer is my customers want it. The hidden answer is SQL Server query plans are very helpful in debugging performance problems, and if you know how to debug performance problems, you make more money (and we like that, so why search for different customers who pay less). I have not yet learned similar tricks for Postgres, but I find Postgres has more tools to effectively avoid problems (e.g., jsonb data type) than troubleshoot them.

[jzabroski]

BTW here's an article describing the SQL 2011 standardization of temporal tables. Not sure if SQL Server is conformant (or whether it matters).

BTW, this is nice - I am curious if you know of a good way to get a copy of all the ANSI SQL Standards? You would think these standards are as easy to get as, for example, the Unicode Regular Expressions standard that's just a web page with full revision history tree. But it seems like SQL is a feifdom that makes it very hard to know what the ANSI standard says. Even in school I would hear my teachers say, "ANSI SQL 92 compliant" and wondering where I could go read it to fall asleep faster. :)

[jzabroski]

@maumar is going to do some investigation here so that we can start to get a clearer idea of what support EF should add.

@ajcvickers @smitpatel @maumar One additional consideration for temporal tables, which may vary wildly depending on the database, is that SQL Server temporal tables are agonizingly verbose to solve "Net Changes" query - SQL Server MVP Adam Machanic provides sample code that is 60 lines long, and needs to be customized PER TABLE. I think this would be a huge win for an ORM framework to automatically generate a correct Net Changes query. See: http://dataeducation.com/solving-the-net-changes-problem-with-temporal-tables-t-sql-tuesday-087/ for outline of some of the gotchas. Currently, you pretty much have to auto-generate stored procedures to solve Net Changes in a bug-free manner. With C# Source Generators coming in .NET 5.0, this might be easier task, but still not as nice as saying something like DbSet<Person>().NetChanges()

Also, note that if you do multiple updates within a single transaction, all ValidFrom periods committed to the temporal table will be based on the start of the transaction, which will make it appear as though these changes all happened instantly. Mostly useful to think about how developers can write Repository integration tests around temporal table queries, as most people have an Xunit test with a transaction around the whole test method, and rollback the transaction when it is done.

[maumar]

EF Team had a design meeting discussing scope and approach to temporal tables feature. Notes below:

General

1. Feature should be implemented as relational, rather than SqlServer specific. Several databases support it in one form or another (SqlServer, MariaDb, Oracle, DB2). So far no support in Postgres but there are some building blocks and once the support lights up EF should have proper infrastructure to add the feature.

2. Pri 0 is AS OF operation (i.e. time travel) we can fully support it and still create consistent graph. Other operations can be supported in limited form to facilitate data audit scenarios - querying a single table but without relationships with other entities (to avoid inconsistent/ambiguous graph).

3. When configuring entity to map to temporal table allow for specifying names for StartTime/EndTime columns as well as name for History Table.

4. When mapping StartTime/EndTime columns to entity we can use convention - if the entity has properties corresponding to StartTime/EndTime column names, they should be mapped, otherwise create StartTime/EndTime properties in shadow state. We can allow option to specify those columns as HIDDEN. In that case always map them in shadow state or not map at all (TBD)

5. We should allow to map entity to a History Table. Either as keyless entity or create a composite key using primary key of the original entity as well as StartTime/EndTime columns (TBD).

6. Migration from non temporal table to temporal - adding StartTime/EndTime columns with defaults current datetime and DateTime.Max.

7. Reverse engineer model from temporal is lower priority for now.

Query design details

1. Temporal operations should be allowed directly on DbSet, rather than on arbitrary IQueryable (similar to FromSql) Caveat is that temporal operation won't be usable together with FromSql. Solution is to use the temporal operation directly in FromSql string, and if necessary propagate the temporal information to related entities manually using joins. Alternatively change FromSql to return a custom type that inherits from DbSet, rather than IQueryable and make temporal operations be defined on that type instead of DbSet (TBD)

2. Temporal operation should be propagated from root DbSet to all navigations (either accessed via property or included). This should be done during navigation expansion (somewhat tricky since nav expansion is part of core, but temporal tables are relational concept). This way we always have consistent graph and don't need to add custom Include methods or ways to apply temporal operations to navigations individually. Queries that require different points in time for can be constructed using Join:

   from c in customers.AsOf(2010/1/1)
   join o in orders.AsOf(2020/2/2) on c.Id equals o.CustomerId into grouping
   from o in grouping.DefaultIfEmpty()
   select new {c, o}

Consider throwing for set operations due to ambiguity , e.g.

customers.AsOf(2010/1/1).Concat(customers.AsOf(2020/2/2)).Select(c => c.Orders)

3. Queries using temporal operations should always be non-tracking. We can end up with multiple entities having the same key. Queries against temporal tables that don't use temporal operations can be tracked just like normal entities.

[maumar]

Question to the community:

Can you describe some scenarios where you would like to use EF temporal tables feature, other than time travel (AS OF operation)?

We want to assess how compelling are those other operations and to what degree we should support them.

[esbenbach]

Question to the community:

Can you describe some scenarios where you would like to use EF temporal tables feature, other than time travel (AS OF operation)?

We want to assess how compelling are those other operations and to what degree we should support them.

Not entirely sure how this would affect the design decision but what we would like to do is provide "audit/change" history for entities. For instance, a list of address changes for a given person, and when they occurred. Not within a specific time range (i.e. not AsOf), but the entire history.

But I guess this is just a form of the "time travel" scenario.

[IAmHopp]

Not entirely sure how this would affect the design decision but what we would like to do is provide "audit/change" history for entities.

Same here! I'm assuming that, for a particular entry on a particular table, I'd be able to query all different states of that entry through time. I might have to do additional work to keep track of the user responsible for that change but, just to help with the context, the reason I ended up here is I was researching for an audit solution on a .Net project and was considering temporal tables for a possible implementation.

[augustoproiete]

Can you describe some scenarios where you would like to use EF temporal tables feature, other than time travel (AS OF operation)?

@maumar, Other than time travel (AS OF operation) for me would be:

• Display Audit Log to user, to help them understand who made changes to entities, when, and what changed (effectively a diff between entities over time)
• Analyze history to understand user behavior, identify business events and potentially missing features

[douglasg14b]

Can you describe some scenarios where you would like to use EF temporal tables feature, other than time travel (AS OF operation)?

We want to assess how compelling are those other operations and to what degree we should support them.

@maumar

In my case, this would be to support "snapshots" in time for historical records and calculations without having to have an overly complex schema. Such as in an e-commerce application.

For instance. Say you have Orders and Products. Each product has a price column. The price a Product may change over time, however orders made at certain periods of time need to be associated with the price of the Product at that time.

This could be accomplished by having another entity ProductPrice that is a one-to-many to Product. And the an Order references the ProductPrice. This is fine for a single instance, but what if you have a dozen such fields that need that kind of tracking? The schema starts become full of these kinds of tables.

If I could query for a date range based on an order date, then I can get the price at that time without having to have extra relationships.

[augustoproiete]

@douglasg14b You've just described what @maumar meant by time travel (AS OF operation)

He's asking for additional scenarios that are not time travel (AS OF operation)...

[douglasg14b]

oh.... my bad

[rezdm]

Question to the community:

Can you describe some scenarios where you would like to use EF temporal tables feature, other than time travel (AS OF operation)?

I am working on a portfolio management system. It is of a niche product, but for the sake of clarity, imagine a portfolio management of any financial product (equities, bonds, does not matter). In this project some properties of some entities varies over time.

Usual "as of" scenario An example could be "name of bond issuer" (exactly just the name, not the issuer company entity). In some user scenarios, I need to view a portfolio with one single value for such a name, basically "open a portfolio as of mm.dd.yyyy" and there should be just a single value of a name.

Not specifically "as of" single date scenario: There are scenarios, where I need to be able to have access to historical data as well, an example would be portfolio performance calculation -- that's when entities that are used in pricing have properties valid from/to and I need to load data not at a given moment in time, but for some time period.

"Show all" scenario: Third scenario is just giving a user an opportunity to view all changes for a given entity over time. From example above -- name of a company, rating of a company, etc

"Future" scenario: It is not a scenario on its own, but consider any scenario from above and the following. Sometimes users know that certain properties have new values "as of" in the future. From examlpes above, consider a company name change. It is announced that a from 1/1/2021 a company "XYZ corp" becomes "XYZ+partner Corp", but today is 6/6/2020. The trick here is how to live for upcoming 6 months and correctly handle changes in such a company with a record already in the future.

All this is to support either to address handling of data entities that have properties naturally varying over time (pure "as of" scenario) and for audit trail

So far I implemented my own handling valid from/to logic based on Z entity framework extensions with .IncludeOptimized and some generified code to pass a predicate for entities that are temporalized.

upd on 08-Jun-2020: I give it another thought and in just my scenarios, what I would appreciate is:

1. When initialize a DbContext, just provide a timestamp of interest and let EF to detect temporalized entities and load a single record for that timestamp. This is for "as of date" scenario
2. For other scenarios, it is up to me to detect what to load, so DbContext should allow not to have filter on timestamp.

[jzabroski]

@maumar I wasn't clear from your May 7 meeting summary if the team had considered supporting "Net Changes" or not. I don't consider this explicitly "time travel" because its stitching together a set of historical facts and rolling them up. I also believe this is the most common question people actually want to answer yet most difficult query to write (even SQL Server MVP Adam Machanic needed three tries).

Can you please comment on Net Changes?

Thank you.

[jzabroski]

Not entirely sure how this would affect the design decision but what we would like to do is provide "audit/change" history for entities. -- @esbenbach

I believe, by audit report, there are two different types:

• Did anyone attempt to change something?
• What were the net changes between two time periods?

Same here! I'm assuming that, for a particular entry on a particular table, I'd be able to query all different states of that entry through time. -- @IAmHopp

I believe you're asking for "Net Changes" I mentioned above on May 5th.

One observation is that it isn't clear from @Muamar meeting notes:

• Does EFCore Temporal Table support require the underlying database model to explicitly use "system periods" and "temporal table query syntax"?
• Can EFCore Temporal Table support use de-sugared syntax like the one I showed produces the same query plan as the fancy SQL Server syntax?
• When @maumar says that "Several databases support it in one form or another (SqlServer, MariaDb, Oracle, DB2.)", are there proof-of-concept SQL queries and tests in C# proving out their raw SQL are congruent?
• MariaDB has a cool concept called DELETION FOR PORTION that would be nice for DBAs who want a process to truncate data past a reg-hold requirement, like the SEC requirements to reg-hold transactions data 7 years. It's really annoying that SQL Server is "car without seat belts".
• In general, there are application periods, which is what @rezdm seems to want. For example, the SEC requires a transaction fee for certain kinds of trades, and the value of that fee changes over time. It would be nice to use application periods for this. Ideally, it would do the de-sugared query I gave in my link above for SQL Server. @maumar would need to research other DBs, but Maria seems straight forward.
• I would love it if EFCore had API to efficiently convert existing tables to temporal tables. I wrote some nasty logic in FluentMigrator (FM) to do this for one client but it's not in public sources of FM.
• It goes without saying that EFCore fluent model builder should stop foot guns. It should ideally warn user when database isn't using UTC.
• Open question on whether system periods should be convertible to local db time if datetime2 is used instead of datetimeoffset?

[jzabroski]

@douglasg14b Don't do it that way! What you're referring to is application periods. But from a domain modeling perspective there are so many potholes in your approach. Funny enough, this is an interview question I give.

For instance. Say you have Orders and Products . Each product has a price column. The price a Product may change over time, however orders made at certain periods of time need to be associated with the price of the Product at that time.

You need to memoize the price at time of Order, and revisit it if the price changes at time of fulfillment.

[maumar]

@jzabroski we didn't talk about net changes specifically. I have filed an issue to track it: #21170 It would not be part of the initial temporal tables implementation. Looks like this feature needs "data audit" scenario support. From the perspective of Temporal Tables feature we will consider this as a feedback/desire for having the audit scenarios implemented.

[jzabroski]

Thanks, @maumar . Can you also respond to:

Open question on whether system periods should be convertible to local db time if datetime2 is used instead of datetimeoffset?

What I mean is this note in the SQL Server documentation (unfortunately, I think you need to subsequently dive into each db provider to cross-verify consistency, otherwise you will end up with more bugs):

System-versioned temporal tables store values for period columns in UTC time zone, while it is always more convenient to work with local time zone both for filtering data and displaying results. The following code example shows how to apply filtering condition that is originally specified in the local time zone ...

-- From Temporal Table Usage Scenarios on MSDN

Ordinarily, if this is stored as datetimeoffset, this is no big deal. However, some clients of mine preferred using datetime2 for everything. It would be nice if we could annotate these columns' projections with time zone conversion functions, such that the reified .NET DateTime object is in local time.

I realize this is an "edge case", but just imagine all the time Diego et al spent in EF6 troubleshooting DateTime related bugs. It's better EFCore be explicit about these behaviors and at least provide explicit guidance up front. I don't think its sufficient or necessary to frowny face my feedback - I spent a lot of time thoughtfully composing it and providing my experiences with temporal tables:

[maumar]

@jzabroski we have not discussed such details yet, but from a brief conversation within the team today some folks were sympathetic towards this idea. This of course is subject to scheduling, difficulty of implementation etc, so no promises but seems like a good idea at face value

[fernandoseguim]

Question to the community:

Can you describe some scenarios where you would like to use EF temporal tables feature, other than time travel (AS OF operation)?

We want to assess how compelling are those other operations and to what degree we should support them.

Currently, we use the event source to store and learn about the entity's behaviors, but there are cases when this solution turn application more complex. The temp tables are a very simple solution in scenarios when we don't need to know how I got there, but need to know when it happened. Temporary tables will be used by us in these scenarios.

[jzabroski]

@fernandoseguim What sort of query would you write in that situation? On the surface, it seems like its still time travel.

@maumar I guess this is "obvious" to me, but... in addition to AsOf, do you plan to include support for all of the temporal table query operators? Re-reading your summary, it isn't clear.

• AS OF
• FROM TO
• BETWEEN AND
• CONTAINED IN ( , )
• ALL -- Microsoft Docs: Querying data in a system-versioned temporal table