Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support Temporal Tables #4693

Open
shivajiraot opened this issue Mar 3, 2016 · 63 comments
Open

Support Temporal Tables #4693

shivajiraot opened this issue Mar 3, 2016 · 63 comments
Milestone

Comments

@shivajiraot
Copy link

@shivajiraot shivajiraot commented Mar 3, 2016

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.

@rowanmiller

This comment has been minimized.

Copy link
Member

@rowanmiller rowanmiller commented Mar 4, 2016

For the most part, the fact that a table is temporal is opaque to the data layer - so from that point of view you should be fine to connect to an existing database with temporal tables.

Here are some limitations though, that we could address in EF:

  • If you wanted to query a past state of the table you would need to use a raw SQL query.
  • There is no way to make a table temporal in the model/migrations. So if you are using EF to create the database then you would need to hand edit the migration and use Sql(string) to make the table temporal.
@rowanmiller rowanmiller changed the title Temporal Tables Support Temporal Tables Mar 8, 2016
@rowanmiller rowanmiller added this to the Backlog milestone Mar 8, 2016
@DavidBainbridgeRIT

This comment has been minimized.

Copy link

@DavidBainbridgeRIT DavidBainbridgeRIT commented Jun 7, 2016

I second this as important. Temporal tables are fantastic. They cut out a lot of boiler plate but I need that information in the front end just as easily as in the back end.

@R00iBaard

This comment has been minimized.

Copy link

@R00iBaard R00iBaard commented Sep 16, 2016

Agree, I would love to be able to set this up all via EF code first.

@reberinformatik

This comment has been minimized.

Copy link

@reberinformatik reberinformatik commented Sep 21, 2016

👍 See also #2229

@lucasmaj

This comment has been minimized.

Copy link

@lucasmaj lucasmaj commented Sep 28, 2016

I was absolutely bummed out to find out that currently there is no obvious way to query past state using lambda expressions. Is there any way to sneak in "AS OF <date_time>" into a query? (in EF6)

@divega

This comment has been minimized.

Copy link

@divega divega commented Sep 28, 2016

@lucasmaj have you tried raw SQL queries as a workaround as explained above? E.g. in EF6 use SqlQuery() and FromSql() with EF Core. The latter is composable in LINQ.

@lucasmaj

This comment has been minimized.

Copy link

@lucasmaj lucasmaj commented Sep 28, 2016

I haven't tried raw SQL. If EF6 had SqlQuery composable with LINQ it would do the trick for now. There is no way I would go with pure sql queries and abandon LINQ. Unless there could be a way for LINQ to pure sql back to entities in an automatic fashion.

Is there really no way to augment conversion of Expressions to SQL string so that some custom expression would translate to "AS OF" etc?

Thank you nonetheless.

@sfgadjo

This comment has been minimized.

Copy link

@sfgadjo sfgadjo commented Mar 22, 2017

I look forward to full support of Temporal Tables in an upcoming release. In the meantime, I am trying to make use of the SysStartTime/SysEndTime columns added to the table in the model (using CodeFirst), and I find that EF is trying to write to those columns even when I have the model property annotated with DatabaseGenerated(DatabaseGeneratedOption.Computed). Is there a way to get around this issue so that I can at least use those columns added for versioning without writing SQL?

@R00iBaard

This comment has been minimized.

Copy link

@R00iBaard R00iBaard commented Apr 20, 2017

Any update on this? It would be very nice to have this 👍

@ajcvickers

This comment has been minimized.

Copy link
Member

@ajcvickers ajcvickers commented Apr 20, 2017

@R00iBaard This issue is in the Backlog milestone. This means that it is not going to happen for the 2.0 release. We will re-assess the backlog following the 2.0 release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

@ntimmerman

This comment has been minimized.

Copy link

@ntimmerman ntimmerman commented Jun 14, 2017

+1, I would like to use this in the very near future for a new enterprise doc automation app that requires an audit history on every screen. Does anyone have any more information about the work around's for now?

@pray1997

This comment has been minimized.

Copy link

@pray1997 pray1997 commented Jun 15, 2017

+1

We need it too!

Or is.there any workaround, maybe something like intercepting?

@ephos

This comment has been minimized.

Copy link

@ephos ephos commented Jul 5, 2017

+1 - I hope this gets prioritized after 2.0, this would be great to have without manual setup!

@chassq

This comment has been minimized.

Copy link

@chassq chassq commented Aug 22, 2017

+1

3 similar comments
@kliebenberg

This comment has been minimized.

Copy link

@kliebenberg kliebenberg commented Sep 10, 2017

+1

@angularDevd

This comment has been minimized.

Copy link

@angularDevd angularDevd commented Sep 13, 2017

+1

@cSteusloff

This comment has been minimized.

Copy link

@cSteusloff cSteusloff commented Sep 15, 2017

+1

@squeakyD

This comment has been minimized.

Copy link

@squeakyD squeakyD commented Oct 4, 2017

If you added support for interceptors (like in EF6), then it would be possible to handle inserts and updates to temporal tables by handling the generated fields.

@smitpatel

This comment has been minimized.

Copy link
Member

@smitpatel smitpatel commented Oct 4, 2017

@squeakyD - At present you can configure your model with generated columns to insert/update data in temporal table. Main and most useful functionality would be the query pipeline especially generating SQL with between predicate etc.

@BinaryPatrick

This comment has been minimized.

Copy link

@BinaryPatrick BinaryPatrick commented Oct 16, 2017

+1, would love to see the ability to time slice with a lambda expression.

@sifordtj

This comment has been minimized.

Copy link

@sifordtj sifordtj commented Dec 14, 2017

+1. Would also love to see the ability to query data with lambda ... something that gives me the FOR SYSTEM_TIME without have to use a raw SQL string.

@israellot

This comment has been minimized.

Copy link

@israellot israellot commented Dec 14, 2017

+1

1 similar comment
@forest-devil

This comment has been minimized.

Copy link

@forest-devil forest-devil commented Dec 15, 2017

+1

@AndriySvyryd

This comment has been minimized.

Copy link
Member

@AndriySvyryd AndriySvyryd commented Dec 15, 2017

Please vote using the 👍 reaction on the very first post, otherwise it won't affect the order in the issue list https://github.com/aspnet/EntityFrameworkCore/issues?q=is%3Aopen+is%3Aissue+label%3Atype-enhancement+sort%3Areactions-%2B1-desc

@smitpatel

This comment has been minimized.

Copy link
Member

@smitpatel smitpatel commented Jan 11, 2018

Please vote using the 👍 reaction on the very first post

corrected a bit.

@cpoDesign

This comment has been minimized.

Copy link

@cpoDesign cpoDesign commented Nov 7, 2018

@benhysell

This comment has been minimized.

Copy link

@benhysell benhysell commented Nov 8, 2018

@cpoDesign is the code for that Nuget hosted somewhere? I didn't see the link on the Nuget site to the GitHub repo.

@weitzhandler

This comment has been minimized.

Copy link
Contributor

@weitzhandler weitzhandler commented Dec 31, 2018

@cpoDesign Thanks for sharing!
@ZombieProtectionAgency very nice! would love to see your MigrationSqlGenerator subclass.
@benhysell It's here
@rowanmiller @ajcvickers Anything planned regarding this subject? Is there a way to inject the AS OF <date_time> to an existing query? Can I extend to current SQL provider to allow this feature? If yes, where is the actual place the SQL provider transpiles the SQL query?

Thank you!

@ZombieProtectionAgency

This comment has been minimized.

Copy link

@ZombieProtectionAgency ZombieProtectionAgency commented Jan 14, 2019

@weitzhandler I was a little hesitant to show it off because it is a pretty simple rough draft and doesnt feel "good" but this was what I was using. I also use a custom DbContext that overrides ModelCreating and annotates the models/fields that are required and an annotations provider that persists the entity migrations to the model.

@weitzhandler

This comment has been minimized.

Copy link
Contributor

@weitzhandler weitzhandler commented Jan 14, 2019

@ZombieProtectionAgency Thanks for sharing!

@BloodyMettle

This comment has been minimized.

Copy link

@BloodyMettle BloodyMettle commented Feb 8, 2019

There's another approach to have a package-workaround by @findulov that I just found when I searched for @cpoDesign s package:
https://github.com/findulov/EntityFrameworkCore.TemporalTables
Haven't tested it yet though.

@sm38098

This comment has been minimized.

Copy link

@sm38098 sm38098 commented Jun 4, 2019

FromSql with EF Core won't work if you've included entities that are also system versioned as 'Include' clause would only return the current state of the included entities and not the state 'as of '.
So if you've navigation properties that're also system versioned, this approach won't work. The only way to get around this is by using ado .net which is pretty inapt if you're using EF Core.

@IAmHopp

This comment has been minimized.

Copy link

@IAmHopp IAmHopp commented Jun 19, 2019

Would be extremely useful having this.

@dvanurag1308

This comment has been minimized.

Copy link

@dvanurag1308 dvanurag1308 commented Jul 19, 2019

Would love to see this feature in EF Core.

@cgountanis

This comment has been minimized.

Copy link

@cgountanis cgountanis commented Jul 29, 2019

Is there any solution to db-first, temporal tables and the common error?

Cannot insert an explicit value into a GENERATED ALWAYS column Use INSERT with a column list to exclude the GENERATED ALWAYS column, or insert a DEFAULT into GENERATED ALWAYS column.

I assume it is trying to insert into the [ValidFrom] ,[ValidTo] columns, was working until convert to temporal, those are the only new columns.

@chassq

This comment has been minimized.

Copy link

@chassq chassq commented Jul 29, 2019

You can use the DatabaseGenerated attribute. For example:

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime SysStart { get; set; }
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime SysEnd { get; set; }

Unfortunately the dotnet ef scaffold does not seem to support adding it for you at this time.

@cgountanis

This comment has been minimized.

Copy link

@cgountanis cgountanis commented Jul 29, 2019

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

@cgountanis

This comment has been minimized.

Copy link

@cgountanis cgountanis commented Jul 29, 2019

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

This comment has been minimized.

Copy link

@chassq chassq commented Jul 29, 2019

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

This comment has been minimized.

Copy link

@cgountanis cgountanis commented Jul 29, 2019

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

This comment has been minimized.

Copy link

@vertonghenb vertonghenb commented Oct 20, 2019

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

This comment has been minimized.

Copy link

@glautrou glautrou commented Dec 5, 2019

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

This comment has been minimized.

Copy link
Member

@ajcvickers ajcvickers commented Dec 7, 2019

@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

@ajcvickers ajcvickers removed this from the Backlog milestone Dec 7, 2019
@ErikEJ

This comment has been minimized.

Copy link
Contributor

@ErikEJ ErikEJ commented Dec 8, 2019

@ajcvickers

This comment has been minimized.

Copy link
Member

@ajcvickers ajcvickers commented Dec 9, 2019

@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.

@ajcvickers ajcvickers added this to the Backlog milestone Dec 9, 2019
@glautrou

This comment has been minimized.

Copy link

@glautrou glautrou commented Dec 9, 2019

@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

This comment has been minimized.

Copy link

@bbrandt bbrandt commented Dec 13, 2019

@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

This comment has been minimized.

Copy link

@MoazAlkharfan MoazAlkharfan commented Feb 1, 2020

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()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
You can’t perform that action at this time.