Tracking Data Changes in C# .NET
HTML-код
- Опубликовано: 25 июл 2024
- In this session, I'm going to walk you through a capability that allows you to track data changes in your database with a single line of code. This capability is a powerful feature that comes out of the box with EF Core and offers APIs to enable engineers query historical data in their systems
here's some useful links:
Jeremy's Article about Temporal Tables:
devblogs.microsoft.com/dotnet...
Demo Code:
github.com/hassanhabib/Tempor...
My goodness, how on earth I did not know about this amazing feature until now!
Thank you for the excellent video, Jason Momoa!
It's JSON* :-)
Great video! I liked this feature because it's a clean way to track data changes. It helps you to keep your code less complex.
Exceptional! I used a kind of event sourcing approach to do basically the same. How on earth I did not know this was supported out of the box!
Eye opening! I have been working with Sql Server and dotnet since 2004. I had no idea there is this feature. We were building custom approaches instead. Thank you!
Temporal tables are great. I use them when I can. One important note is it requires SQL 2016. In a lot of our CMS systems database compatability is quite old (pre 2016) and we tend to use triggers and audit tables for this kind of thing, very manual compared to temporal tables. Great video. Thanks.
Lovely!! You are making my life easy, one line of code at a time..
this was what i was searching for a project im working on. i implemented this and most of the extra code we had to write was cleaned immediatly. this is great! thanks for the great insight!
This was a really good tutorial, learned a lot! Nice work Hassan!
Oh my! This is amazing, brother! Let me share my experience with you! We have some old system that we had to code almost the same thing ourselves, by setting a trigger on the main table in DB on update to copy the old data from the main table to another history table i.e h_original_table_name, the history table contains old data, new data, user, hist_dt, and we used this to build a complete an e2e audit and traceability system, plus recovering from failures as you mentioned in the wonderful explanation! 👍 thanks ❤️
Holy macaroni, that is so amazing. I've been doing that manually, with like one ID for db record, another ID to share between historical objects, and a flag IsHistorical, and while updating an object, then manually copying it, and adding a new one with edited data... this will save me lots of time!
This one-liner will take care of that! best of luck!
Discovering your channel was a gift on this last day of 2022. Temporal table is a great feature.
I'm a .NET developer since 2006 and I really would like to share my knowledge with others and I really like your style. Hope to find the way in the new year 😀
Hey, thanks for your kind words. Would love to have you come on my channel so I can introduce you to my followers/subscribers and hopefully it will be a great start for you to share you knowledge with the world.
Saw two videos of yours. Learned two new things. Thanks
Super cool that you have a fundraiser attached to this! What an awesome way to give back to a meaningful cause.
This the best thing that happened since I started this channel. I'm really happy I can maximize the benefits people get from this content.
Thank you for your kind words.
Amazing feature man! Thanks for sharing!
This quick sessions&features are great to follow, thank you ☺
Thank you for watching!
Thanks, and also for your work on odata!
Wonderful session
That's an amazing feature. Learned something new today.
I had no idea something like this existed. Thank you very much for this.
Thank you for this video. Thank you for your knowledge sharing! I appretiate it.
Very cool, It's a good example, thanks for sharing.
Wow! Why we didn't know about this until now. We are gonna apply this concept to our boilerplate SaaS template.
Is it just me or is this feature is so amazingly critical that I don't know how the majority of SQL databases I've seen out there live without it, interestingly Postgres does not (yet) officially support this feature.
I cannot get think of any business no matter large or small that would explicitly opt out of the history and protection this feature gives.
Thanks for the introduction to this 🙏
Very nice presentation
Excellent information and thanks for sharing. I’m looking to potentially change what I have or use this for another application.
Woah, guess I fiound one more channel to become my favourite C# channel amongst a few others 🙂
The PeriodStart and PeriodEnd are Shadow Properties in the model. I think that you can access them in queries using a method in the EF class to represent them.
Like this:
var contacts = context.Contacts.OrderBy(contact => EF.Property(contact, "PeriodStart"));
But true, they are normally are not added to the entity for obvious reasons.
Good point. thank you.
I just started encountering this channel on YT, hadn't heard of it before, but it seems to cover some "big data" and "enterprise" scenarios I'm not familiar with and would seriously enjoy learning. I've been programming for a long, long time but I couldn't possibly (ever) learn _everything_ haha, nobody can. And there's something about listening to another engineer with expertise in other fields talking about how they do things and solve problems that I just _really_ love and admire. Looks like I found another good channel to add to my "backlog" for future YT-watching sprints, haha 😁
I really, _really_ wanna find time to start making serious videos of my own, because I have a _lot_ of things I could share to add value to people's careers, education and learning paths, etc, but my actual work for my real job keeps me so busy that it's hard to set aside time and ever make myself record and edit! And I've been promising friends and colleagues for a while I was gonna do it ... people want me to make videos about advanced interop and writing wrapper libraries, "hacking" .NET assemblies and using IL and native asm for cool stuff, creating DSLs and lexer/parser/compiler tools, real-time 3D stuff with DirectX 12 and game engine architecture, there's like 100 things I kinda "owe" people videos on, lol. This video gave me a bit more inspiration to get to it today ... I've gotta start soon!!! 🤨
Hi Aaron, we could start doing sessions together - I don't edit my videos, it comes as is. I usually tell people there are content creators and there are people who just make content - I'm the latter haha.
Let's find time to get to know each other and show the world some of your magic.
@@HassanHabib that would be cool, man, I'd definitely enjoy that. You on Discord?
@@GameDevNerd Of course, this is my Discord: Hassan Habib#8097
Hello Hassan
Great work man, thanks
BTW, ef.tools package includes the ef.design package :)
I see. Thank you!
Hassan, Thanks very much for this excellent introduction of Temporal Tables in EF. I will be exploring this further in the next couple of days. First, I was wondering if you come across anything about the performance hits from temporal tables to the system. And second, in a system that is of high traffic generating many entries on the temporal table, how are these managed in terms of how much space they occupy, and is it possible to compress the temporal table, to minimize storage usage.
Hi Cesar,
Thank you for your kind words. I appreciate you.
I haven't analyzed the performance for Temporal Tables yet - might be a good session to do however.
Holly shit thank you for sharing.😊
That works only for MS SQL Server, for other databases you have to do this Audit manually.
wonderfull
How would you take advantage of this if you are using the repository pattern on top of EF?
Only thing to add is that you can add + create a temporal table for any table - and that includes when you not using EF and a database first approach as in this video. So, even if you using an existing web site, or even say ms-access against a sql server table, you can add + enable a temporal table, and no code changes are required at the code level, or even if 2 different systems use the same database. so, to be clear, this is a SQL server feature, and one that not really tied to code or even when using a database model - no requirement exists to use this feature with models - it works even if using say ODBC to sql server.
I tried creating a column for LastModifiedBy and OperationDone using Entity Framework but couldn't make it work. Am I missing something or it is impossible to do so with EF only? I understand it is possible doing so with custom methods that write to a history table, but I prefer using native code from EF
🙏
good job! amazing function, and I wonder if i update student by using raw sql command, will this function still can track data changes? thank you for sharing this information.
Yes it should - give it a shot
What about turning on CDC... This way the orm is not burdened with this?
Temporal tables not available in SQL server standard edition.
Wow
Hey, nice video! Is there an equal simple way to create a „bitemporal“ table? That means track not only date of change, but also date of knowledge, so you know which history was known at a given date?
Please elaborate. I don't fully get what you're asking.
Great to know
What is the best monitor for developer from your opinion?
I like this one: SAMSUNG 49” Odyssey G9 Gaming Monitor
Is there a way for it to also track who changed the data?
If you add fields like CreatedBy and UpdatedBy you certainly could.
Well, i think the dates are not coming because the students class has no columns for dates. I think we must inherit the students class into a new class having both the date columns that is used to iterate though!! My hunch is, this may work.
Is this killer of Event Sourcing?
Jesus christ, can I buy you a coffee or something?
Give it to the very first homeless person you run into tomorrow (I used to be homeless)
give it to me
Don’t lock it down to students. Make this a generic solution to data changes so it is easily reusable.
This feature looks like Modified Trigger in SQL Server inserted and deleted table. Excellent job!.
Thank you!