EF Core Performance: How Do You Optimize Database Updates?
Vložit
- čas přidán 19. 06. 2024
- ☄️ Master the Modular Monolith Architecture: bit.ly/3SXlzSt
📌 Accelerate your Clean Architecture skills: bit.ly/3PupkOJ
🚀 Support me on Patreon to access the source code: / milanjovanovic
EF Core is the ORM of choice for most .NET developers. Although it offers excellent performance out of the box. We will see how to use EF to optimize database updates - and why SQL could be a better tool for the job in certain situations.
Join my weekly .NET newsletter:
www.milanjovanovic.tech
Subscribe for more:
czcams.com/users/MilanJovano...
Chapters
0:00 Introduction
0:15 Initial project setup
2:04 Setting up Entity Framework
5:20 Adding database migrations
6:15 EF update naive approach
10:24 A better approach - using SQL
15:03 Bonus: using Dapper
18:13 Performance comparison
19:31 Outro - Věda a technologie
An excellent demonstration of why it’s essential to know or at least investigate the queries generated by the ORM. Thanks, Milan!
Thank you sir, it is our mission to spread the knowledge.
Here I am watcing and learning. Thank you for your time. 👩🏼💻
Thank you for watching and learning Milica 😁
Awesome video! Very insightful, not to mention helpful and to the point. Keep up the good work Milan, looking forward to seeing more of your content!
Thanks a lot Nikola, I'm glad you liked it!
Thank you so much Milan. Your contents have improved my understanding exponentially.
Glad to hear that. I hope I can create more content that helps you, like this one
Awesome video, MIlan! It is very helpful for a project I am currently working on. Can't wait for more videos :)
Thank you, Nikola. I'm glad you could actually use what I talked about in this video.
Thaks Milan, I learnt something new today. I had never used dapper and efcore together before. It's really amazing.
Awesome Manzur, I'm glad you learned something new! This is the reason why I'm on CZcams :)
Great explanation, Talented Instructor , excellent examples. Man you are a legend
Thanks a lot, Ramy :)
Excellent content with a very great advice to enhance EF update performance
Thanks!
Great work, Milan 😃. The video is a source of inspiration for programmers who want to optimize their code regarding the database.
If I inspire just one engineer to make a change, I'm happy with that 😁
Awesome video. Never knew you could combine Dapper with EF this way.
Glad you enjoyed it 😁
Great video! Thank you Milan!
Thank you Stefan, I'm glad you like it!
Niiice! Very simple and clear demonstration. Thanks!
Thanks a lot, John! What would you like to see me cover?
Short and sharp. Great video 💪
I try to be "to the point" as much as I can.
Well done! Recently, I have been working with stored procedures to write and APIs and I can tell you is pretty fast in response. Hence I have come to conclusion that to really optimized and API, writing raw SQL query skill is necessary even though am not a SQL developer
Great to hear! I wouldn't say raw SQL is the way to go in 100% of the cases, but where you really want to boost the performance it can be a useful tool.
Solid demonstration! I love how you explained why using Dapper for the slight performance improvement might be outweighed by the technical cost of maintaining both frameworks and the transaction objects.
Thank you!
That was awesomely paced 👍
Thank you, I'm glad you liked it
Great topic to get started with your CZcams journey Milan. Pacing was excellent.
I'm glad you enjoyed it Fahim. Any topics you would like me to cover in the future?
@@MilanJovanovicTech would love to see your insights on Software Architectures and concepts like DDD if you plan on doing those.
Great job, very nice video!
Thank you very much, Vladice!
Thank you Milan!
You're very welcome :)
Great video!
Thank you very much!
Thanks for the video
You're welcome, more are coming!
An example of a simple but powerful explanation.
Thank you!
Clear explanation :)
Thanks!
Great video Milan and very simple and powerful tips all should follow. Here I learned the interpolated variant of EF, was fun to see it really creates @p0 parameter, which I was not aware. I am now looking into RepoDB as it seems it overpasses all the ORMs in perf currently, and looks and feels the same as Dapper but has more features. Also, you might add a tiny bit more attention to audio work ie. cutting, seems some times you cut too hard (ie. too early or late) and maybe just not hide content with your camera, maybe crop it to 1x1 format. Was funny at end to hear that Dapper is faster than SQL Server hh. Well done all in all, following.
I heard about RepoDB before, but I remember something made me not try it out. Maybe I will give it a second try now.
I really appreciate the feedback for the editing, I will try to work on that in the future videos.
Great !!! more content like this, pls ! :)
I'm glad you liked it! Surely more stuff like this in the future 😁
thanks, great content.
Have you used this approach before?
@@MilanJovanovicTech I saw EF and dapper used in one project, everyone used for specific cases.
Great video for the start on your CZcams journey! Topic, pace and pronunciation are perfect. My only suggestion is to use Postman dark theme :)
Thanks Milan!
Thanks a lot Branislav. That's a great suggestion, I switched to dark mode in Postman just now 😁
Thanks for the video :)
Just to know, EF7 supports `Bulk updates` and `Bulk Delete` maybe it's cleaner for the future.
I'm aware of that feature existing, but so far my understanding was that it would not make the cut and be released on time.
Thanks 🙏
You're welcome!
Cool video. It would be greate if you can setup 'Track Active Item in Solution Explorer'. It would be helped to better understand structures of the project folder and files.
Ah, you're right that would actually be helpful
Thank you for sharing this, Milan! That is very helpful and I learned something new about the use of both EFCore and Dapper :)
I haven't tried before about how you handled the transaction in this video and just curious, is it possible to implement some roll-back features with the use of EFCore or Dapper?
Of course Cristian, although I didn't mention it in the video. The IDbTransaction instance has a Rollback/RollbackAsync method available. So we would just wrap everything in a try-catch block, and rollback in case of an exception.
Really good start bro, To the point and crystal clear, but for EF beginners may not get it
Thank you. I'm aware it's not really beginner friendly, it wasn't intended to be. I can't cater to every audience in one video. 😅
@@MilanJovanovicTech That's true, I missed the suggestion that it would be great if you give a small note on to whom it would be suitable, cos I remember I was really frustrated when I couldn't understand tutorial videos when was a beginner 12 years ago, that's the reason I am bit concern on that, anyway nice video and keep doing your great work, God bless you
Very nice video, especially the Dapper bonus
I need to cover EF 7 and the new Update method
Thanks for this awesome comparison! I was wondering what is the point of calling SaveChanges in the raw sql and dapper versions if it updates the database without it?
The SQL + Dapper is for updating salaries. EF + Save Changes is for updating the company. And a transaction around both queris.
@@MilanJovanovicTech Thanks for the quick response! I totally forgot that you were setting the LastSalaryUpdateUtc still using EF.
Great
Glad you liked it!
You sir earned a subscriber! Will you compare this to bulk operations available in EF Core 7? They added bulk updates, inserts and deletes :)
Thank you for the sub 🔥
That's a great suggestion. I think I'll make a benchmark video comparing the new approach 😁
Great video. Do you generally encapsulate the transaction logic in your UoW implementation?
Yes, I like using UoW in most of my applications.
Thanks for the tutorial. I noticed that you missed 'using' keyword before transaction declaration (it's IDisposable) so if code crashes after transaction was created but before it was committed, it will not be rolled back automatically, so in case of "serializable" isolation level, rows will remain locked not allowing other transactions to update them
I said I didn't tackle those cases in the video, and that you should most certainly do it 😁
Nice job Milan,,, which theme you are using in VS by the way? or may be it's the lastest version of VS?
Hey Malik! I'm using ReSharper's dark them for the code coloring.
Excellent demo. The update statement seemed a bit simple. and straightforward, what if you had to update other properties from a list? you would still have to loop it all and still have the same number of updates no?
Why not try to fit everything into one UPDATE statement (assuming it's possible)?
Awesome video... And you are code writing skills are very fast compared to VS compiler.
Thank you, I'm really glad you liked it!
Do you any advice on how to work with locking reads with EF? I used FromSqlRaw("select ... for update") but I'm wondering if there're better approaches.
Many thanks!
You can always just open a transaction? Other than that, and the approach you mentioned with "SELECT ... FOR UPDATE" I'm not aware of anything else.
Milan, nice example. But what if you have "dynamic" database connection? I mean the case when you use MsSql and PostgreSql for different clients. There you need to wrap tableName with " and raw sql-syntax may be different.. Or dapper can translate it in correct way?
What the best approach in this case?
You would need to have diffetent SQL for each database, in case of Dapper.
With EF you have separate DB providers, which take care of that for you
Hi Milan Jovanovic, this is a very good demonstration, congratulations.
I have a question, I have been working with the SAP HANA database as LINKED SERVER on SQL SERVER (Dapper ) and I pretend to change to EF CORE and get data directly from SAP HANA, in your point of view is it a good approach? do you have any examples using the SAP HANA database and EF CORE?
Hi Edson - I have never even heard of SAP HANA before you told me about it 😁
I would be cautious about using it with EF, since it's an "in-memory column-oriented RDBMS". It would probably be a good idea to write your own queries (with Dapper, as you pointed out).
Hi Milan, in real-world apps and projects you worked on, what has precedence, Fluent API, or data annotations in order to configure a model? Or it's some kind of mixture?
Definitely FluentAPI, I don't like using data annotations at all. I also advise against the approach in this video, I just used it for simplicity. Using IEntityTypeConfiguration is the best option in my opinion.
Hi Milan,
Thanks for your video tutorial.
How to Bulk insert with checking if records exist in .Net without using Dapper?
Without Dapper? I'm not sure. I think you can use SQL MERGE for that
cool :D
Thanks
but did the company.LastSalaryUpdateUtc change in the db when you called SaveChangesAsync or it is also included in the transaction?
It's also persisted in DB
What if I wanted to add the three implementations in my project to gain performance benefit from each in reading and writing, What could be the best implementation for that?
Does the Repository Pattern help with this? What do you think?
Love your content and your style of presentation ♥ Keep it up, Milan
You can always create an interface, and call it where you need to. And in the implementation of that interface, use whatever is most performant.
@@MilanJovanovicTech So, I should create 3 interfaces for each implementation & Register them in DI, right?
How would you tackle that problem when using DDD? That salary raise would have to go through the Employees Aggregate Root RaiseSalary method right?
DDD can't solve this problem easily, to be honest. You would have to take a different approach - being pragmatic over dogmatic.
Awsome video.
Is the SQL output a built-in feature of visual studio, because I have never seen ef instructions being displayed as queries there.
This is the Visual Studo output - which is just the Console output.
EF will log queries by default - but you have to turn on the proper Log Level to see these (Information and below).
@@MilanJovanovicTech Thank you , but can you please tell the log level for this output.
@@MrAymenmatador Information, Debug, Trace
Good luck
Thank you!
cool!
🧊
Please upload on ef core stored procedure complex scored procedure
I will see what I can come up with!
The EF Core team has actually introduced a feature in EF 7, that allows to do the same single update statement, but with 0 sql using pure EF. TBO, I don't know, how we managed to live without such feature for so long)
Me neither 😱
what if we have no standart updating in group of data? for example i am going to make update on a table; I got n lines of updates. I am updateding in foreach loop some values are a and values are b regarding to if or swich statements. Currently i am using db.tables.updaterange(mylist); db.savechanges(); what do u offer instead of this? also in my method if i write after savechanges "db.database.commitransection", will it work?
Yes, your approach will work.
If you can run EF Core 7, take a look at ExecuteUpdate, I made a video about it recently:
czcams.com/video/VYitXAc_htI/video.html
@@MilanJovanovicTech I would like to watch it too and i will watch all of your videos soon. But at present i have newly entered .net 5 to .net 6 :) .net6 made a conford zone for me but i need to break this zone soon in my new projects. So I started to inspect 7 at the moment.
Milan, the payload of include and includeafter is too big, selecting all columns, projection is not tracked specially with 2-3 includes, do we really need to refactor to executefromsql, thanks!
You can try with AsSplitQuery if you issues with joins
Hi Milan,
I suggest you zoom and focus more on the code tab. I think most people watch from their phone now...
Thanks Anthony, that's a great suggestion. You're not the first one to say that either. Fixing it for the next video 😁
shouldn't we also wrap updates in try catch block and rollback transaction and send a 500 error response in case something went wrong?
I didn't tackle error handling here, that was on purpose
Great first video Milan. Good luck with the channel (and those pesky porn comments as well :).
🤣
Thanks Code Maze crew! The pesky comments are gone 😁
Hi milan, please make a video on best way to do db migraration on production. like scipt way or without script
Means in microsoft doc they have mention that we can do that in may ways
like migrations script and migrations bundle
I want to achieve it should support CI-CD plipline and on-demand migration,
CICD is ok with the DEV env. but how i do that on production?
Please give me some suggestions?
@@amitsinghrawat4760 For Production I personally create SQL scripts and execute them manually. I like to have more control over this process.
in some where you said lambda functions for passing value and also in other where you said delegate for the same parameter. are they a same concept? (forgive me about this simple question... i'm new in c#)
learn.microsoft.com/en-us/dotnet/standard/delegates-lambdas
Would be nice to use dark theme in postman as well ;-)
Definitely will in the future videos!
Question is what the best way to update a field on a collection of lets say 20 items max
In-memory?
@@MilanJovanovicTech, so this is the context I’m trying to create some sort of a scrum board, with tasks organized by columns, and every time I move a task I want to update the info in database of all the tasks on that column with new positions
what if i want to update different salary for each employee?
Then gotta do one by on
@@MilanJovanovicTech is there anyway to enhance the performance in this case?
A great approach in some cases, but there are a few drawbacks.
Do you want to encourage your developers to write raw SQL? Chances for mistakes are much higher.
If you have raw SQL and ORM, you have to track changes in the database in multiple places. What happens if you change the table (in an incompatible way)? You will not get a build time error.
Of course, this depends on the situation, but I would be hesitant to introduce raw SQL/Dapper with EF.
Yes, I do want to encourage people to write raw SQL. I think SQL is great.
In general, for performing these kinds of updates on large number of rows, it really doesn't make sense to use EF - it won't scale.
If you change the table - hopefully you have a good set of tests to catch something like that?
Also, changes to the database should be communicated inside of a team so problems like that can easily be mitigated.
@@MilanJovanovicTech
If you have to update many rows per command frequently, maybe you have a problem with data design.
>>If you change the table - hopefully you have a good set of tests to catch something like that?
Also, changes to the database should be communicated inside of a team so problems like that can easily be mitigated.
You are assuming there are tests for all parts of the code. And you are assuming that developer that wrote that SQL is still in the company.
I am not saying you are wrong. Just too optimistic.
@@MilanJovanovicTech I agree 100%. It is fascinating to see today how developers have almost no clue about SQL. They relay to ORM's to do job for them. Just side note, IMO such update belongs to DB (stored procedure).
@@michaelkopljan578 you can say same thing about assembly language.
How do you achieve this from a DDD perspective since you can't introduce external dependencies?
You wrap it in a service, essentially. We can define an interface in the Domain that would increase the salaries within a company. And we can implement this interface in the Persistence layer.
@@MilanJovanovicTech OK, I thought as much
This isn't very DDDish, right? How would you fix it? Introducing a CanRaiseSalaryByX method in the Employee model?
DDD wasn't the focus here at all
For serious application development you have to write sql anyway. And SQL is not hard to write. These two reasons had me develop a big disdain for ORMs in general and EF in particular.
I find that you can use both quite effectively in most applications
When you defined "Employees" property inside your "Company" object why you used "List" as property and not "IEnumerable" since you'll not use methods such "Add, Remove, ..."?
Because that wasn't the point of the video, and I just used the first thing that came to mind
Sub'd
Awesome, glad to have you join us!
Did you try updatemany?
Is there such a thing?
The first step to prepare EF Core is to spent six hours to read entityframeworktutorial.corg. 2 Write actual sql generated requests into console. Final, drop EF Core and use dapper with raw sql queries. Still there are too many questions without database.
No, EF is awesome. Too good to drop it for Dapper. But I like Dapper, I use it together with EF often.
Who do not understand. It makes one select request and then n requests to update each employee salary in a separate trip. Avoid using EF for such scenarios at all. And It is all even worse, If it is not changed, because EF was not able to deliver it in on server trip, it made actual n trips.
@@IldarIsm Sorry, but you are wrong on that one. It *generates* N update statements. But It only executes them in batches. EF batches requests to the database in the background, so that i t does make multiple round trips. But I agree that the first approach is not the best one, which is precisely why I show 2 alternative approaches which are much better.
@@MilanJovanovicTech Ok, I see you as me miss "not" when want to say does not. If there is batches it means it does not make n trips. I am glad they have implemented batches. Great you have figure out traps on the blink eye and provide alternative solutions.
Thanks for the video, but put sql code in c# is not recommendable for security, code injection.
Not true if you parameterize the query
Guess this is the difference between a senior and a junior engineer.
Already starting a new project applying the right statements using EF. Don't really see the need using Dapper .4ms isn't that much of a reason.
It's not .4ms it's 4ms. Which is still a 16% improvement. Multiply that over a large number of API calls and it quickly adds up. 😁
@@MilanJovanovicTech Guess that shows why I am still a junior😊
Thanks for putting me right.
could you increase the font a bit Thanx
Sure thing, thank you for the suggestion!
Of course you used to play WOW 😄
Still do 🔥
You could change your EF approach and get the same behavior.
How so?
Type prop tab to create a property faster
I really dislike that approach, I find I am faster when I type it out myself
@@MilanJovanovicTech Oh, I see I type really slow :)
Sorry but are you just showing that the way of „making ef core faster“ ist basically just not using it. Even by writing the „raw query“ you skip basically ef core functionality. The title of your video is misleading.
There's also ExecuteUpdate now 😁
I think it's better to use
using var transaction = dbContext.Database.BeginTransaction()
Yo ensure that the transaction is disposed if something went wrong
And also I'd suggest EF Bulk updates, I can remember that it's adopted into EF core without external libraries
EF Bulk updates is from .NET 7
👋
Is This the faster and best way to pull all data from a table?
app.MapGet("/Students", async (DatabaseContext dbCOntext) =>
{
var forecast = dbCOntext.Set();
return forecast;
})
.WithName("GetStudents");
And how is the best and fastest way to pull all by ID?
The fastest way would be:
var forecast = await dbContext.Set().AsNoTracking().ToListAsync();
Additionally, you can return something other than Student (with less data perhaps) by selecting only what you need.
@@MilanJovanovicTech So would this be faster than Sql statements or dapper?
@@MilanJovanovicTech I know you mention dapper is fast to Put data is it the same for getting?
@@jodainemoore8300 Dapper should almost always be faster than EF. But it's also more complex. You also have to consider how much data you have in your database, and if you have proper indexes in place.