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 · 32 comments

Comments

Projects
None yet
@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.

Show comment
Hide comment
@rowanmiller

rowanmiller Mar 4, 2016

Member

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

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 from Temporal Tables to Support Temporal Tables Mar 8, 2016

@rowanmiller rowanmiller added this to the Backlog milestone Mar 8, 2016

@DavidBainbridgeRIT

This comment has been minimized.

Show comment
Hide comment
@DavidBainbridgeRIT

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

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.

Show comment
Hide comment
@R00iBaard

R00iBaard Sep 16, 2016

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

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.

Show comment
Hide comment
@reberinformatik

reberinformatik commented Sep 21, 2016

👍 See also #2229

@lucasmaj

This comment has been minimized.

Show comment
Hide comment
@lucasmaj

lucasmaj 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)

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.

Show comment
Hide comment
@divega

divega Sep 28, 2016

Member

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

Member

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.

Show comment
Hide comment
@lucasmaj

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

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.

Show comment
Hide comment
@sfgadjo

sfgadjo 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?

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.

Show comment
Hide comment
@R00iBaard

R00iBaard Apr 20, 2017

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

R00iBaard commented Apr 20, 2017

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

@ajcvickers

This comment has been minimized.

Show comment
Hide comment
@ajcvickers

ajcvickers Apr 20, 2017

Member

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

Member

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.

Show comment
Hide comment
@ntimmerman

ntimmerman 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?

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.

Show comment
Hide comment
@pray1997

pray1997 Jun 15, 2017

+1

We need it too!

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

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.

Show comment
Hide comment
@ephos

ephos Jul 5, 2017

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

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.

Show comment
Hide comment
@chassq

chassq commented Aug 22, 2017

+1

@kliebenberg

This comment has been minimized.

Show comment
Hide comment
@kliebenberg

kliebenberg commented Sep 10, 2017

+1

@angularDevd

This comment has been minimized.

Show comment
Hide comment
@angularDevd

angularDevd commented Sep 13, 2017

+1

@cSteusloff

This comment has been minimized.

Show comment
Hide comment
@cSteusloff

cSteusloff commented Sep 15, 2017

+1

@squeakyD

This comment has been minimized.

Show comment
Hide comment
@squeakyD

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

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.

Show comment
Hide comment
@smitpatel

smitpatel Oct 4, 2017

Contributor

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

Contributor

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.

Show comment
Hide comment
@BinaryPatrick

BinaryPatrick Oct 16, 2017

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

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.

Show comment
Hide comment
@sifordtj

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

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.

Show comment
Hide comment
@israellot

israellot commented Dec 14, 2017

+1

@forest-devil

This comment has been minimized.

Show comment
Hide comment
@forest-devil

forest-devil commented Dec 15, 2017

+1

@AndriySvyryd

This comment has been minimized.

Show comment
Hide comment
@AndriySvyryd

AndriySvyryd Dec 15, 2017

Member

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

Member

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.

Show comment
Hide comment
@smitpatel

smitpatel Jan 11, 2018

Contributor

Please vote using the 👍 reaction on the very first post

corrected a bit.

Contributor

smitpatel commented Jan 11, 2018

Please vote using the 👍 reaction on the very first post

corrected a bit.

@chassq

This comment has been minimized.

Show comment
Hide comment
@chassq

chassq commented Jan 12, 2018

👍

@angularDevd

This comment has been minimized.

Show comment
Hide comment
@angularDevd

angularDevd commented Jan 12, 2018

👍

@AndriySvyryd

This comment has been minimized.

Show comment
Hide comment
@AndriySvyryd

AndriySvyryd Jan 12, 2018

Member

@chassq @angularDevd Only the reactions on the first post are counted. All 👍 and +1 comments will be ignored.

Member

AndriySvyryd commented Jan 12, 2018

@chassq @angularDevd Only the reactions on the first post are counted. All 👍 and +1 comments will be ignored.

@marc-at-nowhow

This comment has been minimized.

Show comment
Hide comment
@marc-at-nowhow

marc-at-nowhow Apr 18, 2018

Definitely +100 from me! 👍 👍 👍 👍 👍

marc-at-nowhow commented Apr 18, 2018

Definitely +100 from me! 👍 👍 👍 👍 👍

@cpoDesign

This comment has been minimized.

Show comment
Hide comment
@cpoDesign

cpoDesign May 24, 2018

+1 here too. How about to add a base class "Temporal" entity that will add the behaviour and has the properties out of the box?

cpoDesign commented May 24, 2018

+1 here too. How about to add a base class "Temporal" entity that will add the behaviour and has the properties out of the box?

@ajcvickers

This comment has been minimized.

Show comment
Hide comment
@ajcvickers

ajcvickers Jul 3, 2018

Member

See also aspnet/EntityFramework.Docs#797 make sure to support temporal in-memory tables.

Member

ajcvickers commented Jul 3, 2018

See also aspnet/EntityFramework.Docs#797 make sure to support temporal in-memory tables.

@ConX-Ryan

This comment has been minimized.

Show comment
Hide comment
@ConX-Ryan

ConX-Ryan Aug 24, 2018

+1 need this in my life

ConX-Ryan commented Aug 24, 2018

+1 need this in my life

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment