Common mistakes in EF Core - Jernej Kavka - NDC London 2024

This talk was recorded at NDC London in London, England. #ndclondon #ndcconferences #developer #softwaredeveloper
Attend the next NDC conference near you:
ndcconferences...
ndclondon.com/
Subscribe to our RUclips channel and learn every day:
/ ‪@NDC‬
Follow our Social Media!
/ ndcconferences
/ ndc_conferences
/ ndc_conferences
#dotnet #sql #apps
When JK worked with many different clients and projects, he frequently heard "EF Core is slow" or "We should do this in raw SQL" only to realize they haven't used EF Core correctly.
JK will show you how to improve your EF Core statements as well as how various configurations impacts the performance and scalability of your application. You'll be blown away at how small changes can significantly impact not only the performance but also stability of the application.

[@HaoNguyen-km9xj]

That's really nice and useful knowledge. Thank you for sharing!

[@dkazbek]

On 41:45 he call SaveChanges but ExecuteUpdate and ExecuteDelete execute directly on database when called. SaveChanges doesn't affect them. Just for information.

[@jernejkavkajkssw6662]

Thank you! It makes sense but for some reason it never clicked. 😊

[@dy0mber847]

This presentation is gold! Thanks

[@MIS2000Lab]

Thanks your kindly sharing, It's very useful. Thanks again.

[@robertogonzalorodriguez845]

Very informative and really well-presented content. Thanks!

[@leakyabstraction]

Nice. Thank you, I had no idea about the ExecuteUpdate and ExecuteDelete methods (I skipped a few versions of EF Core)

[@margosdesarian]

What a super video!!!!

[@TappinMyFoot]

Great tips. I've been guilty of some of these, but the people I've helped onboard for EF seem to do most of theese.
For pagination, it seems like it would be a good idea to have the IQueryable include a sort clause. It's always possible with pagination that your next set will have new or deleted records between pages, causing you to have dupes or skipped records between loaded pages. But without a sort order, you don't even know that SQL's optimized you might even have SQL's optimizer choose to use different optimization plans between executions, meaning the 2nd page you load wouldn't be close to the "next page", since it may be the next X records using a different sort. For a single table, it's probably usually going to be the same optimization plan if the where clause doesn't change. But if there are joins involved, stats could change, and you could get totally different ordered results between executions. Beyond that, paged result sets don't make a lot of sense to the user unless the pages have some kind of order, so they have a sense whether they should be looking near page 1-5 of 100 for or pages 50ish of 100 for something in the middle of the sort.

[@jernejkavkajkssw6662]

Yes, the table changing while navigating through the table is a potential issue that is not trivial to fix. Most common pagination we use is called "Offset Pagination" (Skip() + Take()).
Take data like latest RUclips videos or Twitter/X posts, the data is always added at the start. For these kind of data, you want to use "Keyset pagination" where you remember the ID of last item and you filter from there. I'm not aware of any strategies for that may be deleted. Also, "Keyset pagination does not allow you to skip pages or go back to previous pages!
Google "Microsoft Docs EF Core Pagination" to find Microsoft's documentation on this. I've added a summary before I found the docs to verify my code. :) (Also, all code is hand written)
Here is how to do a "Keyset pagination" by a integer primary key. (won't work with GUID)
```
int pageSize = 10;
var firstPage = dbContext.Videos
.OrderBy(x => x.VideoId)
.Take(pageSize)
.ToList();
// Additional data to pass to UI
bool hasNextPage = firstPage.Count == firstPage;
int? lastVideoId = hasNextPage
? firstPage.Select(x => x.VideoId).Last()
: (int?)null;
// Next page
if (!hasNextPage) return [];
var nextPage = dbContext.Videos
.OrderBy(x => x.VideoId)
.Where(x => x.VideoId > lastVideoId.Value)
.Take(pageSize)
.ToList();
```
This is gives you a smooth pagination experience, one page at a time, from the item that you started.
Here is how to do a "Keyset pagination" by PublishedDate instead. You'll notice that we still need to know last VideoId as well as last PublishedDate for this to work correctly. Query is a bit more complex because you can theoretically have multiple videos with the same PublishedDate and it's an edge case that is handled down below.
Example:
```
int pageSize = 10;
var firstPage = dbContext.Videos
.OrderByDescending(x => x.PublishedDate)
.ThenBy(x => x.VideoId)
.Take(pageSize)
.ToList();
// Additional data to pass to UI
bool hasNextPage = firstPage.Count == firstPage;
var lastItem = hasNextPage ? firstPage.Last() : null;
// Next page
if (!hasNextPage) return [];
var nextPage = dbContext.Videos
.OrderByDescending(x => x.PublishedDate)
.ThenBy(x => x.VideoId)
.Where(x => x.PublishedDate > lastItem.PublishedDate || (x.PublishedDate == lastItem.PublishedDate && x.VideoId > lastItem.VideoId))
.Take(pageSize)
.ToList();
```

[@RatonBroyeur]

Second one took me 2 days to find when I had weird update somewhere.
Having EFCore making updates I did not explicitely asked what so out of my thought paradigms I never thought it would be possible xD

[@djchrisi]

I'm really not into EF Core. I have always the feeling I'm battling the framework. This talk only strengthens my opinion. With Dapper I always have the feeling to work together with the database. I know exactly what is going on and why.
With EF not so much.

[@md.redwanhossain6288]

Why do you want to work with Database when writing OOP code? Relational approach and OOP approach are different. Thats why you need an ORM which lets you speak in OOP where the DB is abstracted out.

[@djchrisi]

​@@md.redwanhossain6288
People might argue about it, but I don't think that the 'O' in ORM means the same as the 'O' in OOP. OOP is just so much more than just objects. It's about inheritance, encapsulation, polymorphism, etc. The 'O' in ORM means more data and records to me.
You can, of course, abstract the database away - no problem about it. But I personally do not feel good about it, and I like to know exactly what is going on. Even if it means that I need to use a DSL (in this case, SQL).
It also means to probably move slower, and if all goes well, EF is indeed a fast and efficient tool. You can focus on other things than the DB. However, if things do not go smoothly, then I do not think EF is doing a good job to help me figure out what is wrong.

[@shaddadfalcon]

Not having to work with DB is the whole idea of ORM :). EF Core is powerful tool and with great power comes great responsibility and that lies completely on developers to know the tools they are using.

[@Dalamain]

I know where your coming from - I will never EVER use code first or EF Migrations - yes it works nice on simple demos but on real projects... don't even go there. However ... you should check out EF-Core 8 ... "database first" of course - there is a VS extension called EF Power Tools that will reverse engineer a dbcontext for you from the existing database. I also provide my own mappings for the domain - sure EF has conventions but like you say I like to know exactly what's going on because if/when the app breaks the manager will be coming to my ass for a fix!

[@rogeurroger7119]

It is... hard to listen. But interesting anyways for those who are starting with EF.