Using EF Core’s Coolest Feature to Audit in .NET
HTML-код
- Опубликовано: 10 сен 2024
- The first 200 get 30% off our new Git and GitHub Actions courses on Dometrain with code GIT30: dometrain.com/...
Subscribe to my weekly newsletter: nickchapsas.com
Become a Patreon and get special perks: / nickchapsas
This video is sponsored by AWS. To get $25 free AWS credit, check out this link: aws.amazon.com...
Hello, everybody. I'm Nick, and in this video, I will show you how you can add database auditing in your .NET applications using Entity Framework Core's interceptors feature.
Workshops: bit.ly/nickwor...
Don't forget to comment, like and subscribe :)
Social Media:
Follow me on GitHub: github.com/Elf...
Follow me on Twitter: / nickchapsas
Connect on LinkedIn: / nick-chapsas
Keep coding merch: keepcoding.shop
#csharp #dotnet
Why do we need this KeyedScoped? AuditInterceptor is already scoped as it was created inside scoped DbContext. It can just store this list as part of its internal state.
I have the same question
Would love a follow up about what to use to store the audit.
In my organization we use a cosmos/mongo db with a retention period of 6 month.
I don't know if it was mentioned in this video, but this won't work with the new ExecuteUpdate method from EF Core, you have to use different kind of interceptor and do quite a bit of expressions manipulation.
such things are the reason why I would prefer to audit explicitly
Would temporal tables fix this? They are natively supported by EF.
@@diegoronkkomaki6858 Temporal tables are part of your database provider auditing logic, so they aren't affected at all by EF Core interceptors. The problem with ExecuteUpdate/Delete methods is that they don't hydrate ChangeTracker, which makes sense since they generate raw sql from just an expression tree. You can solve this by using a QueryExpressionInterceptor, if you'd google for `How to update shadow properties with Entity Framework Core ExecuteUpdate method?` you'll find a stackoverflow post with answer that has a code sample, how one could achieve it.
Can you tell how can we do this using some expression manipulation? Cuz, as far as i know if save changes is not called in the first place how can we manipulate what going into database?
@@DubzerIn my opinion, this would be risky, as some other dev might miss some important bits. What do you think? It may result in, inconsistencies between entity to entity
It's a good video for starters, but don't forget that proper auditing is often quite complex as each business' requirements are typically different. Using a messaging service to store your audits may be a good route, but the argument shouldn't be to preserve database resources; transactional messaging would require use of the outbox pattern (or similar), which takes up resources as well. Aside from that, an interesting take on a common requirement, and I highly recommend EF developers to learn more about the change tracking APIs.
Great video, would love a separate topic on sanitizing/anonymizing data in EF, that would be sweat! Maybe how to create test data based on production data using EF core, in a safe and compliant way?
Why not generate a new set of data using Bogus and seed the DB with that?
@@JollyGiant19 If a customer is experiencing issues in production, I would like to pull his data, protect any pii and/or other sensitive data, and see if the issue is reproducible in the new database
Edit:
Also, would like to know how to anonymize data in auditing as well.
@@ericvruderI've used the data masking feature in SQL Azure for this before
@@rattrick1 Looks interesting, have a user that doesn't have 'unmasking' permissions and get that to read data into a test database. Thanks for the tip!
This is on point. I have learned something new today. Thanks, Nick
That Database window and Sessions (when you connect to a db) in Rider is amazeballs. From my experience so far, its Intellisense blows SSMS and PGAdmin (for Postgres) out of the water. If they could get multiple result sets showing on the same tab/screen (or get a Dump like LINQPad's), it would be legendary.
I had worked with something like this entirely setup using DB triggers (it was an Oracle DB, and the triggers written in its PL/SQL). For affected tables, on any operation changing a row, a new version of the row would be copied to another table (plus some metadata, timestamp etc).
This creates a timeline of changes for those tables, and you can easily query their state at it was at any point in time (changing table schema becomes a bit tricky though).
I found this ability to look back in time *across multiple tables* extremely useful.
It usually works out to perform better to use Update/Insert Triggers in the database for this sort of tracking when writing your audit to SQL as well. Where it is useful, is when your audit entries are going elsewhere, like log files or a NoSQL database etc. Sending an entirely new transaction to SQL with the same data it already has access to in the trigger is silly.
7:14 Couldn't you inject the interceptor in the DbContext constructor and pass that in the OnConfiguring method? That way you avoid injecting dependencies to the DbContext that it won't actually use
I’ve been waiting for this topic in your channel.
Great video, Nick! Would love to see a follow up to this showing your recommendation on how/where to store the data.
What about history tables, (temporal) whose support was added in Ef6
This is the way.
Doesn't it only support temporal tables with MS SQL server?
@@99MrX99 I think you are right.
Temporal tables support is way better than manual things to manage. It's nice that it is also supported in EF Core.
Absolutely helpful!
Many thanks @Nick.
It's okay based on the options and different ways to do things. However, use your database audit and CDC features to keep your application agnostic of data and not compromise the performance with extra db transactions due to manual audits.
I've actually implemented something like this for myself. I even added blockchain hashing so I can always validate the audit trail per tenant basis. But my implementation does not work as interceptor, which is really cool idea. I'll have to try if I can bake my process inside SavingChanges method.
I implemented an interceptor a while back to set some strange db credentials to insert or update certain tables (it was in a PCI environment and even then this was strange and unnecessary). For that to work properly, I had to set and unset them every time EF inserted or updated anything in the database. I had to learn what SavingChanges and SavedChanges did the hard way but once I got it working it worked exceptionally well.
Thanks for breaking down EF Core interceptors for auditing! Curious if you've considered using Kafka instead of SNS/SQS for even more scalable event-driven auditing, especially in high-throughput systems.
Using a compiler feature which I personally say has a library level or source generator use sounds like a workaround hacking. Also how it is used is sooooo much more complicated than should be done for the developer.
EF Core is a bigger package should make it by themselves
Great topic! Thanks.
DB audit is better be done on server side instead of client as data may be changed not only by this particular application
Hello,
I assumed I could find the source code link as you mentioned in the comment, but it’s not there. I also subscribed to your Patreon to access the code, but I couldn't find it there either.
Is there a way to use change tracker to figure out what was changed on each entity? E.g. you change customer name, but keep other properties the same - is there a way to figure that fact out using change tracker? Or can I only see the fact customer was updated and that's that?
Yeah, all of that information is available from the ChangeTracker and you inspect the metadata of each property to determine it's state (and access any previous value, if applicable).
@@andersborum9267 I looked previously, but I couldn't figure it out. Any links to relevant properties or classes would be much appreciated.
Iterate through `entry.Properties` and then, you can use `property.CurrentValue` for new records and `property.OriginalValue` for old records.
It is possible to add a user and do not have an audit record, because the application may fail in/before savedChangesAsync, am I right?
where is the github link you said its free please?
Would love to see your thoughts on capturing audit logs in something that is highly performant and can be reported on well.
I don't get the point for why using Keyedscopedscervice for the audit list, could you please explain it ? :)
Oh i found the answer, totally forgot that previous Scoped method doesnt allow types like List where keyedscopedscervice do :D
@@julienraillard3567 where is the key "Audit" then used?
Let’s say the application is living on premise without the access to any cloud. What would be the best target (storage) to save audits? Could it be a separate DB just for that?
ExecuteUpdate/ExecuteDelete/raw sql make this approach questionable. You are locking yourself in using only EF DbContext for modifying data. If you really need audit, just do it explicitly.
The better use-case for interceptors is adding hints to queries. For example you can use built-in EF TagWith for something like TagWith("FOR UPDATE") and add the interceptor to handle it. The interceptor can look for "-- FOR UPDATE" comment in the sql and modify the sql accordingly.
I think you don't need universal approaches for absolutely everything, so this should be relatively fine if you constrain your codebase.
@@diadetediotedio6918 I agree. On the other hand, If I'm not mistaken, you won't audit any migrations you might do with EF.
Good and simple approach. But there should be some advanced way to handle audit logs in transactions or get DB generated values.
Can these interceptors work per each set? I mean, for what I'm seeing I assume this interceptor will work globally, but I'm wondering if I can configure interceptors to have customized behavior for each set that I have, it maybe just for few that I care to intercept
You saw him doing the logic based on type. From there you can build anything.
Is there a better way to do handle it and consume it from the PG level? 😊 because I’m using ExecuteDelete/Update a lot.
Probably you can try to build audit messages from WAL (write ahead logs). Still there is no common library and it would be custom code.
@@AlexanderShumakov I believe WAL is disabled by default on CosmosDb for PG (Citus). I might try some other approaches.
@@3dnoob That's true. Usually you don't need it and you should know why do you need Wal. That was just one of the hints beside of DB triggers 😁
I work in healthcare and my organization needs to get a certification for our product. One key aspect is the auditing of read access for some sensible information. Would it be possible to use a different kind of interceptor to do the job?
how would this work with the bulk update? idk but i would imagine that the change tracker doesnt do much when that's executed kind of like executing raw sql. but it would be cool. i need to test that!
24:10 this PK looks more like a Primary Key than a Partition Key, since it's the table's PK
if dbcontext is scoped, wouldn't be enough to make interceptor scoped too and just add field with the list of changes?
Edit: oh, now i see that you registered interceptor with AddInterceptor(new AuditInterceptor...), would it be possible to register it with factory from ID?
this was very interesting
Great video!
Great video. Would like to see a follow up on the error handling tho. I would say if you have audit logging in your APP it is mandatory. What happens if you cannot publish the audit log? Rollback? Retry till success (not a guarante)? Etc
all depends :) If you have on-site app that can lose access to the Internet and you publish logs to some cloud-service then I would probably save them in a file and have a service that picks them up and send them for processing. Stopping the whole system because broadband is down would be silly.
Where would be the right place to save those audits? S3 files?
A Microsoft MVP sponsored by AWS alright. You'd have expected this to go the Azure route otherwise.
Still, nothing wrong with showing a bit of the competition.
As for the dumping ground for this, I guess considering the amount of data to be expected logging this granular you'd likely choose data lake for those logs rather than the RDBMS.
Maybe a data warehouse if you want the queryability. Though if you're using Azure anyway, I think Log Analytics would fit the bill just fine for this sort of logging (though since that's got limited data retention, you'd likely need archiving still).
But should not AuditEntries.Clear() also delete other users Audits stored savestates?
it is clearing per user request scope which means each request scope will have its own AuditEntries list.
Hi Nick,
You mentioned that I could access the code for this video for free, but I can't seem to find the link.
Is it necessary to be a Patreon member to access the code, or perhaps the link was accidentally omitted from the description?
Thanks for clarifying!
Hello. In my infinite wisdom I forgot to set up the code link. I will be doing that in the next couple of days. Sorry for that
Ok it is sponsored by AWS. But sending a message for every save to the cloud and the receive it and write it back to the DB is not the cheapest solution. Why not simply have a background worker that you trigger and that does the saving in an async way (certainly some audit changes could get lost if this crashes).
I think this was mostly done for demonstration purposes (to justify the sponsored video). I doubt Nick would use such a setup for logging a high volume of entity changes!
Actually this not best solution. Best is create CRUD SP and do all work in SP. In such case you not need EF completely. This might sounds "oldy", but this really a way to go for big databases, when dba and performance and logic is really needed. Same things even better can be done via db triggers: don't understimate database changes audit which caused by maintenance/supporting divisions. The difference what data+audit is integrated in DB, and is atomic.
Every solution have own applications, but power of linq is making dynamic queries. Outside of this power - it strictly worse than good old ways.
Because with a background worker you can lose audit messages
@nickchapsas Genuine question, how can you not lose messages there? You said the audit is not saved sync with the other data (which I guess it means, it's not saved in the same transaction to the database)?
@@NotACat20 in sql Server this is build feature called change data capture. But that might not log all details IMHO. The best approach is too first research if the rdbms supports the required auditing out of the box and what the performance implications might be. But this video was about demoing the ef core event handling
no link?
Just use temporal table with author instead. You get all the history / snapshot features for free!
Exactly what I went with. Very very less work.
Or should I say 'no work' at all. Just added migrations.
outbox pattern yes?
Began with a simple task and ended up tackling SQS Queues.
Auditing isn’t a simple task buddy
And that's it!... X 20.. lol
What IDE do you use?
JetBrains Rider
coolest feature vs 26 minutes long neverending video :)
in our company we use nhibernate, so we are stuck with envers wich is not bad but the docs for it are so old and not updated at all :(
We usually solve this by adding triggers on the DB level. Application shouldn't be aware that there is any auditing going on other than to return audit entries when needed.
What if the database doesn’t support triggers? What if you’re working with multiple providers. The database shouldn’t be aware of auditing either
Applications need to be aware of triggers when using EF Core on Sql Server because certain T-SQL statements don't work the same when triggers are present (unfortunately).
Roses are red, violets are blue, C# is easy, you can learn too
Typical .NET!!! Convoluted AF!!!