.NET Data Community Standup: 8 Real-World Query Anti‑Patterns (and How to Fix Them)

Added: 2026-05-23

[00:00:14]entity framework framework.

[00:00:22]Azure cosmos >> entity framework power >> countdown commencing t-minus 2 Next.

[00:01:08]Jeremy can hear me. Jeremy witness.

[00:01:12]Sh.

[00:01:16]Brazil bracelet net data tus 1 minutey A sharp f yeah.

[00:02:10]30 seconds.

[00:02:14]Yes.

[00:02:30]10 9 8 7 6 5 4 3 2 1 liftoff. We are go fornet data community standup.

[00:02:51]>> Awesome. Hello everybody. We are live again. We have Shai. Actually you were there last month as well, right? But before you you are muted but still fine.

[00:03:04]But before you ditched me a couple of times. So >> hey don't don't be like that. I'm here now. That's what counts.

[00:03:12]>> Okay. Awesome.

[00:03:14]Um, so what's new? Are we going What's new in EF Core? Do you remember what the preview currently is?

[00:03:22]>> Uh, we're just, uh, right now we're at preview 4 and we're about we're locking down preview 5, which means it will get released in a few weeks.

[00:03:30]>> Um, correct.

[00:03:32]>> We are doing quite a bit of work on stuff like JSON indexes. That might not land in time for preview 5. Andre is working on that quite furiously. Um so that you can index columns inside JSON um in SQL server etc and stuff like this. Uh what else have we been doing? I actually have uh don't don't have the list right now in my head. I would have to >> let me go through the list for preview 4 because that's >> that's what matters uh for the people that because they can try it.

[00:03:58]>> Indeed that makes sense.

[00:03:59]>> The biggest feature in preview 4 probably is the approximate search for SQL server. So that's big one. If you are doing some vector search on on SQL server definitely uh give it a try. You can do dot vector search dot with approximate and it will use the proper functions proper syntax um and it will be translated. Yes, I know what you want to say. Say it.

[00:04:25]>> What do I want to say?

[00:04:27]>> The with approximate and top.

[00:04:32]>> No, no. All I wanted to all I wanted to say is this goes together with vector index support for the first time. So SQL server vector indexes are now supported.

[00:04:41]All this is is still preview on the SQL server side but we're supporting it in EF already. That's like uh we're kind of making sure to expose all of that stuff and that goes very well together with uh with some full text search improvements that we did earlier in this release.

[00:04:56]This is in the previous preview. So we gave uh we gave a lot of love to full text search in SQL server uh because it's frequently something that you want to use together with vector search. Uh the two really work well together. We usually call this hybrid search. And on that note I can give you guys an upcoming uh uh thing as well. We just merged forn net. This is not just the f.

[00:05:16]We merged a new full join full outer join operator to link. So just like you've got an inner join, a left join and a right join. we will now have net 11 will have a full outer join where you can take two sets and do a join even for even if in on both sides some of the things are missing. Uh that's not a formal definition of what a full arrow join is but you guys get it. And uh one thing that we will do probably for preview 6 is add support for this in EF6 so that you can mix vector search and uh full text search together. So there's a whole nice kind of composite feature coming to your way.

[00:05:50]Yeah. The next one is that uh if you have the temporal period properties, you can map them now to properties on your um net classes and types.

[00:06:03]We have more uh translations for datetime uh offset. So if you have uh like year and time of day and stuff like that, it will get translated uh correctly. Bunch of improvements on net ef uh so if you have net ef.json JSON, you can have the defaults in there, so you don't have to specify them all the time and stuff like that. Um, and we have a couple of breaking changes uh that you can get in the preview docs and definitely will be part of the breaking changes together on the website because we will have more breaking changes and stuff like that in there. So, this is basically the biggest features in preview 4. If you're interested in having the full overview of what happened, you can go into the release notes and check the document in there.

[00:06:54]And with that, we have one important uh thing to say. The build Microsoft build is close. It's June 2nd to June 3rd. So if you are interested in that, you should definitely check the website build.microsoft.com >> and uh register stuff like that. Check the the sessions, the schedule and just enjoy all the new features, all the new announcement that will happen during the build. registration is open probably still um and you can see everything that you need to see from the build. With that, I think we are done with this like boring stuff. Let's bring in our friend Woody. Uh he's been on the show a couple of times and he will tell us today couple of common mistakes people do with entity framework queries and how to fix them. So, Woody, do you care to introduce yourself?

[00:08:03]>> Thanks. Uh, yeah. So, Chris Woodruff, everyone calls me Woody, all my friends, and everyone's my friend, so everyone can call me Woody. Uh, so I'm here in Grand Rapids, Michigan. So, uh, thanks guys for having me on. And yeah, I'm just a an old guy in the uh.net net community that likes to uh uh talk about EF Core. Actually, I have a I have a talk next week uh on EF Core at Update Crackout if anyone is going to be there at that conference next week. But uh but yeah, that's a little bit about me. I mean, I I let's let's talk about EF Core and and data more than me. Okay.

[00:08:55]Yeah, >> absolutely. Of course, what we're here to discuss.

[00:09:00]>> Yep.

[00:09:02]>> So, where do you guys want to start?

[00:09:05]>> It's up to you. I will be checking the chat and stuff like that and you can demo.

[00:09:11]>> Okay.

[00:09:11]>> Um based on your priority.

[00:09:15]>> Sure. Uh we can I I thought this could just be a a conversation between the three of us. So uh I mean I I don't really have actually I have to admit I don't really have slides or anything.

[00:09:31]>> So I thought this could just be a discussion and talk about stuff. Um, and I I have to admit also that uh when you guys hit me up about a week ago, I forgot all about my old blog post that I did that I did like last year on this.

[00:09:53]So, I created a whole new eight anti- pattern. So, I I think I'll do the the newest one because I I think those are a little more uh they're a little more uh uh reent a little better for today. So, we'll start. So, so let's talk about the first one. The first one's a a lowhanging fruit, but you know, we I think I've done it before and I think a lot of people that are new to uhnet and ef do this. It's the M plus1 query.

[00:10:32]So, it's this idea that I get something back and so I say I get a bunch of orders back and I'm going to loop through using C. I'm going to do a for each loop and I'm going to loop through each one of those and do some something with it. say like lazy load uh or or write something or work with that in some way.

[00:11:05]And what you have to remember is is uh you're you're lazy loading and you're you're going back and sometimes you're going back across a wire to do a lot of that uh uh work. So you're getting a query one query to get the master list but then to get details. So say you're uh you want to get orders and then you want to get the uh uh I don't know the number of the number of items in each order and you want to get those those item uh the items in that order.

[00:11:53]Each time you loop through your your for each loop you're calling SQL Server again.

[00:12:02]So the solution is uh and I'm not showing it on the screen and uh but uh you need to use include.

[00:12:13]So use include in your uh SQL uh link statement in your link. So you would probably say like and I'm I'm looking at something. So I would say like var orders equals await and then you want to say like orders and then items and then whatever else you need to do. So, so by using include which has been around for a while in EF core that will bring in uh the items for the orders in the same in the same uh uh result set coming back from SQL server which is a lot more what it'll it's a lot more uh uh uh uh help me out guys.

[00:13:09]Yeah, it's a little more efficient. It's it's more >> it's dramatically more efficient. I would even say like I would I would go all out. I think that it's it's a great first uh antiattern like it's one of the worst things that you can do is N plus1 I think like in many cases.

[00:13:24]>> Yeah.

[00:13:25]>> Especially because when you are testing your application locally, first of all, the local host is like super fast and you usually have like 10 rows or something like that. Then you go, >> it looks great, right?

[00:13:37]Exactly.

[00:13:39]Production you have web server and you have the database server that are completely different. Suddenly it's not >> more or less zero milliseconds. Now it's one or two milliseconds and instead of 10 rows you have like 200 and the application goes completely down because it takes couple of seconds to load the page something like that.

[00:13:59]>> That's correct. Then if if your application is going to have many users and those queries are going to happen a lot user base >> that's even a bigger impact where where when you're testing is just one user >> like you said using a local. So that is anti-attern one. So um anti-attern 2 also uses include Yeah. Go ahead.

[00:14:31]>> Can I say a word about this just because this is an awesome one. First of all, uh uh you know uh developers tend to many many times get uh you know look at benchmarks and get obsessed with millisecond like submillisecond changes and you profile or you benchmark your thing and we we we think about like we read Steven Top's blog post about the reduction of 20% of allocations on some call and those are obviously amazing and very impactful things but people should understand that these runtime changes where you're cutting off some allocations or you're using vectorization like processor vectorization to you know to speed up uh some some function. These are absolutely negligible compared to something like this. This kind of thing where you're doing extra round trips to the database this is like a whole different order of magnitude compared to these smallcale optimizations to the extent that I would tell you forget about everything in terms of you know uh optimizing your code and writing good C# and using spans and all that stuff. All that stuff is worth worth absolutely nothing. If at the end of the day you're going to be doing an N plus1, right? N plus1 it's particularly devastating because it's a it's a latency sensitive antiattern. The farther away you're from the your database, let's say you're you're in the cloud, your database is already not right next to you, it's not on your local host on the same machine, the moment you start having latency, that that kind of makes it it compounds. It makes it much much much worse. And we've seen like this is a very very classical kind of thing. By the way, this is not even just uh if you guys look, this is not even just an EF core thing. This is a general database programming thing. So if you go to other languages, right, you go to Python, you go to Java, you can make the exact same mistake. And it's the main reason why in general in EF core, if you look at our docs, we will say that we explicitly discourage using lazy loading. So we develop lazy loading. There are reasons like some some people really really, you know, really think lazy loading is an amazing idea. I think it was more popular, I don't know, a certain amount of time ago. Nowadays, it's maybe a bit a bit less. But one of the main problems is that lazy loading tends to promote N plus1 because it hides it makes it kind of difficult to see when you look at code. You don't see where what will actually cause a network roundtrip and a query and what will not, right? It kind of lazy loading obscures basically what's happening with your networking. And that's generally what you don't want to do in in programming.

[00:16:56]You don't want to obscure heavy operations. You want to actually make them very explicit. Um and so on and so forth, right? Yeah.

[00:17:03]>> So the my recommendation would say in general uh uh don't use lazy loading.

[00:17:08]Like I'm going to say this in a very forceful way. Of course it's it's okay if you are using lazy loading. Uh fine, use it, but then be extra careful looking auditing your code to make sure that you're not doing N plus1. And it's basically about Woody just said it before. It's basically when you're going to do a query and you're going to you you know you need both the blogs and the posts. Stop for a second. If you already know when you query for the blogs that you're also going to need the posts, if you already know this ahead of time, then also fetch them ahead of time rather than later. That's basically the advice here that right >> and we'll talk about this that same kind of idiom uh in in another anti-attern because there's a step further on that. But we'll talk about that in a couple anti-atterns coming up. So, so in the first one I talked about using.

[00:18:03]Well, sometimes if you overuse include you can you can get yourself in trouble also. And and so for anti-attern two, we're going to talk about cartisian explosions.

[00:18:19]So, cartisian explosions is where you you have uh for this say I am getting my orders and then I'm getting my my items my line items for every order and then I want to also get something that is not related to line orders.

[00:18:43]Say I'm I want to get maybe the shipping details or or something else that has multiple records to go with it. Say say I want to get the events. So So I store all the events to do with this order like when the order happened, when it was fulfilled, when it was shipped, when it was delivered. And so I'm getting back these orders. And if I'm just saying include the line items and dot dotincclude the the events that can that can uh lead to what we call a cartisian explosion where you you get not maybe not uh 10 lines but 100 lines because of that the the way that that SQL server handles that uh it would be better if that was split into two queries and you would get your orders with your item numbers and you would get your orders and then your events. Well, in EF Core, we do have the ability to do that. It's called as split query and that will allow you to to not have this cartisian explosion and and return a lot more data in the return payload.

[00:20:12]So with the first at anti- pattern, we want that that associated data to come over in in the return payload, but we don't want it to be inefficient in the way that it returns. So, so by using asplit query, we will get two queries and then EF core will bring those together and and and give you what uh when things get hydrated back into your uh entity classes, things will look look uh uh what you were expecting originally. So, uh so yeah. Um, and so, so let's talk about that first and then I've got to follow up with that. So, anyone want to I'm sure Shai has something he wants to say about that.

[00:21:06]So, >> I always have something I want to say.

[00:21:08]>> I know you do.

[00:21:09]>> That doesn't mean anything. No, I'm just kidding. Uh, yeah. First, I I love your choices up to now. These are these are definitely the most horrible things like and I I think I would pick exactly these two things to start with as well, by the way. Um the so again what I like about this is that this cartian explosion problem is once again not uh specific to EF core. It's a general database programming thing. If you're doing any sort of uh you know you're doing database programming in Python or Rust you're going to have the same you have to deal with the exact same problem. So this is a very general lesson we're talking about here and it's it has to do with how SQL or relational databases work. That's just the way they work.

[00:21:46]when you uh when you include an EF core, we translate that by default to join and then joins for collections. I mean that's maybe an important thing to say.

[00:21:56]There's one to one joins and then there's one to many joins. One to one joins are fine. There's no problem with one to one joins. And in fact, if you do as split query, we don't split them the one to one joints. That's a little little uh detail maybe that not many people know because there's no real reason to do it. The problem is one to many and uh in one to many joins like even there you can there's like two two cases here. The really really really bad case is when you have uh let's say a customer and you are join you are including two collections at the same level as opposed to when they're nested to each other. You can take this as homework for home if you want to think about why that's worse. But if you're if you have an uh you know you have a customer and they have both orders and addresses and you include both of these then you will be multiplying each order by all the addresses and vice versa. So you get a really really large results and that's just how databases work. It has it's not even SQL server postcrist is the same. SQLite is the same and so on.

[00:22:52]>> My SQL is the same way. Yep.

[00:22:53]>> My SQL all relational SQL databases work the same. It's part of the SQL standard.

[00:22:58]That's how joint works. That that's that's by definition the thing. The only question that I think is is maybe worth saying to give this a tiny bit more depth is if it's so bad then why do we do that by default? Like why don't we split queries? Why isn't as split query kind of the default, right? If it's if it's that bad.

[00:23:16]>> Go ahead.

[00:23:17]>> Do you want me to go somebody else?

[00:23:19]>> Yeah. Well, I that is what my followup was had to do with. We we can turn that on by default if we want to. Yeah.

[00:23:28]>> Right. But but then why that's true.

[00:23:31]Yeah, that's absolutely true. So you can do this >> I would love to know why it's single query by default. Yeah >> very good question. So just before that like as you said you can do this on a per query basis. You can take a specific query and say as split query and all collection joins all collection includes in that query will be split rather than joined or you can at your context options uh level you can basically say not really there's a global option where you can basically say yes all of my implicitly everything should be and then you can still use as single query to opt back into joints.

[00:24:02]>> Exactly.

[00:24:02]>> Uh so why would why why not always just do split query is the is the question here. Right. And there's there's a good an there's one main good answer here.

[00:24:11]There's several answers. The thing is that databases when you issue multiple queries, you start have to think about to think about the consistency issue of those different queries, right?

[00:24:22]Databases are about consistency. They're about transactions, right? And if you do a single query, then the database generally generally guarantees that the query is going to be consistent within itself, right? Otherwise, you just get back a mess of data. But the moment you do two queries, maybe the first one, you know, gives you a snapshot of all the blogs. And then before the second query runs, somebody updated things in the database. And now you're getting something that's out of sync. So your blogs and your posts, which you got in two separate queries, are not in sync anymore. And that's a pretty severe consistency problem. It can cause real serious bugs in concurrent environments which once again you will not discover while testing because while testing there are no concurrent updates. It's only a it's a production only problem the worst kind of problem. So then what what's the mitigation uh for this you can if you choose databases have very good tools for dealing with this.

[00:25:18]Databases have transactions which have isolation levels. Not so many people surprisingly few people know know about SQL isolation levels in with any depth.

[00:25:27]Uh by default we use something called databases do something called read committed which only say which doesn't which only says that you will a query will only show you stuff that's committed but it doesn't protect you if you have two queries and something happens in the middle then the second query will you know might give you a different snapshot of the database but you can go up to a higher isolation level serializable which means that the entire transaction will behave on let's let's say as if it's on a single snapshot of the database and that's safe that makes everything safe, but it also makes the transaction much more expensive. It could mean that the the transaction is now holding locks. The transaction could also fail. There's a there's a whole thing there. So, we also c could not opt you in by default into serializable isolation level without you telling us that this is what you want because that impacts database performance. So, we were I remember these these discussions like what should we do here? We had very very uh hefty discussions around this and our our end uh conclusion was that we can't safely just say split because then users have consistency issues. We can't mitigate we cannot mitigate this by doing serializable because that imposes all kinds of consequences which users might not want. And in general when people do includes the general like the universal way is still to do joints. That's kind of like the expectation that users have.

[00:26:48]So we'll do that by default. It's safe.

[00:26:51]It might be problematic for Perf, >> but at least you'll know about it pretty quickly if it is problematic for Perf.

[00:26:57]So >> it's it's one of those situations. I'm sorry I'm talking so so long, but it's one of these situations.

[00:27:03]>> It's one of those situations with databases where there is no right perfect solution. There's no one solution that you can say we'll do this and this fits everybody. Like I'd love to have a solution here, but each decision we'd make has a serious downside for somebody and we have no choice but to pick one.

[00:27:20]>> Yeah. So, so yeah. So, so I was just going to bring up if you do decide you want to have a split by default, you don't need to add that, but you would have to say if you wanted to go back to a single query, you would say add single query. And yeah, >> so awesome.

[00:27:42]But that's the thing with uh relational databases. They are complex beasts.

[00:27:48]>> Yes.

[00:27:48]>> That that uh have lots of information that most developers sadly don't know much about. So uh >> absolutely I don't know everything. So >> nobody knows nobody knows at all. Like absolutely it's it's a question of what you've had experience with. I'll add one small note. This is a this is a clear drawback of the relational model here that uh this thing where you represent collections uh via foreign key constraints right now you have two tables and you have a foreign key constraint and it's a one to many and that means that you join and then you get the cartisian explosion if you model your data via document semantics. So if you think about this more as a JSON document, either you're using JSON inside SQL server or posters or maybe you're using something like the problem disappears. It goes away because you have a document, you've got your customer and then under that is a JSON list of of orders and another JSON list of addresses and you can get the entire document in one go and it's hyperefficient. So this is one place where the document way of modeling your world is much better than the relational way of doing it. And I would really advise like people nowadays we're now in 2026 right there it's not just about relational tables anymore like you can do many things including in SQL server and in Postgress when you're modeling your data think about how you're going to be querying for it. If you're going to be doing a lot of these sorts of collectiony things and you want to load the whole customer as one block, consider doing uh you know going in the JSON direction and doing it more like a document.

[00:29:23]>> Um it's it's a modeling question. It's a complicated question.

[00:29:26]>> Yeah, it's an architecture discussion.

[00:29:29]So, >> exactly.

[00:29:30]>> Exactly. Okay. So, let's let's go on to to uh anti-attern three. And you know I used to do this a lot and I think a lot of people did it a lot in the early days of entity framework with.NET framework where queries before EF would would do a lot of uh server side uh uh work. We would get result sets being brought back to our application and then we would filter we would work on those to to sort and filter and do things but now that is all in the all in the past and we have the ability to do a uh I'll say almost everything on the on the uh relational database side but the problem is is that when you in your link statement if you have some type of uhnet car method that you are including in your in your link statement to say like maybe checking to see if something is active or checking to see uh if a if a price is above or below a certain amount. out.

[00:30:57]By doing that, you are taking the power away from and and guys, if I'm not saying it right, please correct me, but in my opinion, you're kind of taking the power away from from Entity Framework Core and you're not letting it do its job. And that way that result set has to come entirely back to the client and you have and thennnet has to or link has to to uh uh work on that to get the results that that you really wanted in the first place.

[00:31:35]>> Yeah, we call it client site evaluation basically.

[00:31:39]>> Yes, it's not universal but in some instances this happens. I I I mean, Shai probably wants to talk about it. I saw it.

[00:31:47]>> Do you want to Do you want to take this one?

[00:31:48]>> I I I see it on your face, so it's fine.

[00:31:51]>> I'm always going to want to talk. Please go ahead.

[00:31:54]>> No, I'm fine.

[00:31:56]>> Okay. Um, so uh yeah, this is a complicated history here. Uh the good news is that for the past several releases, we we no longer do cliental where we almost never do clinaval where it might hurt you. The story is that EF Core before 30 used to automatically switch to client of val. The moment it saw something that it couldn't translate. That's basically what Woody just said. Let's say you have a wear in your link and inside is something that we see that we can't translate it. EF course 2 would basically say, "Oh, okay.

[00:32:27]So, I can't translate this to SQL to a wear clause. So, we'll just get all the rows from the database, all of the rows from the database, and we'll just apply the function locally." And in fact, one of the stories, one of the stories is uh uh um if I'm not mistaken, at some point that that this specific thing brought down Stack Overflow like this very very specific uh mistake because once again, like all of the other bugs we're discussing here, you discover this only in production. So it looks great. It works fine on your limited test data set. And then the moment you do this on a table that's like whatever 10 million rows, you're dead.

[00:33:03]>> You you're dead. You're done. But the good news is this is ancient history by now and since EF core 3 if you try to do this we will fail the query immediately.

[00:33:13]We will say sorry we cannot execute this query because it has something untransatable in it. So we force you basically to put only stuff that can be translated into SQL with one exception more or less. You can you can do stuff that you can do client evaluation but only in the last select. So at the end of your query you can add a select that projects stuff out of the database out of the SQL query. At that point you can do you can project out and use u uh you know client evaluation because that doesn't lead to us fetching any more information. That's basically the point.

[00:33:47]So client evaluation inside where is devastating. Client evaluation inside select is fine. That's basically the the idea right top level select.

[00:33:59]>> Yes. Yeah, exactly. This is this is a bit trickier than it should be uh Yi unfortunately in our current architecture. So there are certain cases where if you put a select even before it will still get it will move to the end and get a client evaluated. I that should not be the case but anyway we will never do this in a case where this causes us to bring in more rows from the database. That's the important part. So this was it's a good it's a good by the way example. I love these stories where we made a mistake originally. We we made a big design mistake here and then we fixed it in EF Core 3 and today you are protected and this very specific antiattern can no longer happen to you.

[00:34:38]>> Good.

[00:34:38]>> It's very important.

[00:34:40]>> Awesome. So, uh we'll get to antiattern four. So, this is called overfetched columns. So, I call it working with projection. So, uh, you know what? We've all been taught that, uh, never do a select star from a table. Uh, but as as developers, net developers, we we don't think in terms of of that. Uh, also, and there's there's reasons because of that, and I'll I'll kind of explain it, but uh, here here's the problem. If I have a table and say a table has uh Shai's been talking about JSON a lot. So, say it has a a JSON, a column that holds JSON, or maybe it has a column that just maybe it's a a uh column for a blog post and it has an Envar Envir Max uh column. So, that can go up to thousands and thousands of uh of characters. and say you do have thousands of characters in that in that record for that column.

[00:36:06]By pulling back that post in general, you are basically doing a select star from post and bringing everything back from that from those records.

[00:36:21]So the best thing to do is to do when you do a your select do a projection in your uh link statement to only return back the columns that you need.

[00:36:40]But remember in EF Core you to when you bring that result set back you have to have a matching entity type and Shai may correct me on this but you you do have to have a or you could have an anonymous type I guess you could have an anonymous type >> to return it back into. So just remember to make sure that what you're bringing that result back into that's using projection can hold the data and and doesn't uh cause an exception to uh to be thrown. So uh and I use this this is a big anti-attern for me especially if I'm doing mobile uh development. So, if I have a a mobile app or some type of device that uh has a a small or uh doesn't have a wide pipe uh getting back to the internet. So say it maybe it's a it's an edge an IoT edge device or some something that that has a has a slower uh bandwidth for getting data. I always look at uh doing projection so I'm not overfetching those columns that uh are huge and will affect my uh uh return payload. So up to you guys what you guys want to say.

[00:38:20]>> Absolutely. Overfetching is is is one of the big problems 100%. Uh whether it matters in your specific app like you said Woody that depends, right? Like if you're if you're on a mobile app and and so on, then that's definitely going to be more important or or any sort of place where bandwidth is is more restricted, let's say. But it's always a good idea to only fetch what you're actually going to need. That's basically the the key here. There's one thing I do I do want to say. So yeah, the the very standard thing to do in in in link and ENF is to project out to anonymous types. Basically, you do select at the end of your query and then you do new open your curlies and then you get exactly the fields you want and then you pick and choose. In fact, uh I love link but I think one of link's drawbacks is that link promotes overfetching because by default if you don't add that select then you're getting everything. So it's a get everything by default sort of uh uh uh thing, right? link whereas it would have been better. Um this als the the reason for this is that link is originally also a link for objects. It's in memory. It's linked to to over memory where there is no fetching. So you don't really care. You just have the objects that you want. But as a language used to express queries that get executed remotely. It's maybe not the best thing in that sense, right? Because it kind of forces you to have to opt into and to constantly think about what you want to project. If you look at uh for example if you look at like a language like GraphQL >> preventing overfetching is part of the design of the language. So you act you actually have to say what you want like always you can't just say there is no star in in GraphQL if you look at it like you don't have that option basically right so it would have been better also if link kind of forced you to think about this question but I do have there's one note that must be said >> um about why why not always do this it's a bit like before so why not always do this and the reason is if you want to use change tracking let's say you want to load the blog make a change and then cause safe changes then you can't do this cuz change tracking only works when you load the entity out of the database.

[00:40:22]So you have to project out the blog as it is which means getting all of its properties and then only then you have this nice machinery of changing something innet the properties on that entity and then calling save changes to have if persistent back >> you can still like if you're per if you're perminded though I would still encourage you to I would still tell you get that anonymous type and then you can use execute update or execute delete these other APIs instead of save changes the non-change tracking update APIs you can still use those things to say exactly what you want to change.

[00:40:55]>> But that's a more manual sort of style of programming, right?

[00:40:59]>> You have to you get exactly the fields you want and then you tell EF exactly which fields to change to exactly which values. It's a lower level way of thinking about database programming, right? But that's the efficient way.

[00:41:10]That's basically the the efficient way.

[00:41:12]>> Yeah. And is me as a API, I do a lot of memo API and web API development. And so projection for me is is easy because I don't need change tracking. Uh and I turn all that off.

[00:41:28]>> Okay. So uh I see we we got to get through some few.

[00:41:33]>> Shut it up.

[00:41:34]>> No, no. I I love your your uh your insights, Shai. So So the last ad time pattern talked about columns and not doing columns. Now let's shift pivot and talk about only returning uh the number of rows that you really need. And so the next ad type pattern is when when we don't add pageionation to our to our applications. So imagine um imagine you're a uh you're developing application and I'm going to use orders again and you're saying I need all the orders for a certain store. So you have a bunch of brickandmortar stores. I want uh the orders for the New York City uh store. Well, that could be hundreds of thousands of of records and you're going to bring it into your ASP.NET Razer Pages application and all anyone's going to see is 20 20 rows at any one time because that's what your table's set up for.

[00:42:49]So many developers will return the whole thing even though their tables are set up for pageionation but they don't set up link and EF core for pageionation. So this is where you need to understand take and skip in the in the uh link language. So, so say I I want the first page of my I I never get just one page.

[00:43:21]I usually get three pages at once. So, if I'm going to get three pages uh initially, I'm going to say and it's 20 20 rows per visible page in my uh HTML table. I will say uh skip zero. So skip zero just means you're going to start at the top of your result set and I'm going to take 60. So I'm going to take 60 rows in this result set and then when I need the next set of uh uh data I will say skip 60 take 60. So I'm always taking the same number, which for me is three pages, but I'm skipping the page size. So it would be zero, then 60, then 120, then 180, and and on and on and on. And so that will also help out with the performance and the efficiency of your of your uh application, whatever application. And this also relates to all databases and all uh OMS or the or data fetching. It's not just EF core.

[00:44:44]>> Always think about pageionation in your in your application. Um so I'll let you guys give your give your take.

[00:44:56]>> Very good point. I I've got two things to say about this. So much like before.

[00:45:00]So there's there's a really nice comment I like here by DZ ver 9999. A good SQL programmer always adds limit to the queries. I 100% sign that like there should be in fact I consider it almost um almost a flaw in the language in SQL the fact that you can that by default once again unless you explicitly give a limit you get all the rows and and you know and the I would have rather had SQL force you to use a limit and if you really want all the all the rows then you put some limit all or something like that like I would I would have preferred if the language forced us to think about this because of all the reasons you just That as that is a good question. So I'm going to ask you is there a global setting when I set up my DB context is there a global setting for query limit?

[00:45:50]>> No, >> not really. Uh like I would I would I would first think about this you know I would love this to be part to be in SQL itself but that obviously is not happening and and we have no say over this. one could think about as an EF core uh feature uh 100% like nobody's asked this this because it's not typically the kind of thing that somebody asks for. It's more a thing that we think about like I think it's a great idea. I think we should open an issue uh for the backlog and and allow people to opt in to us forcing them to specify a limit. I think that's >> I'll create it so I can so I can have a >> so I can say I I actually added something for EF course. So >> I love it.

[00:46:30]>> I want to be stingy. I I want to be okay.

[00:46:33]>> However, I do have one thing I want to say about the the the style of pagenation. That's that's that's an important point. So you you just described using skip and take, which is what most people do, and this is the classical way to do pagionation.

[00:46:46]>> I do encourage people. So there's the the link that's posted here down below.

[00:46:51]I really like in in our docs. I encourage people to go and read that that thing. uh in two words basically using skip or offset for pagionation is a bit of an antiattern in itself >> like a little bit basically when you do >> I love that I'm getting I'm getting some good insight so >> but it's it's a very it's not like uh it's not like uh you know n plus1 it's it's a thing that few people know and I'm very happy to have an opportunity to to talk about it the problem with with skip and take or offset and limit in in in standard SQL is that when you do a query and you say offset one then the database actually has to go and skip the first row, but it still has to calculate it and go over it.

[00:47:31]>> So if you if you now as you're pagionating and you get further ahead in the result set, that skip costs more and more and more and more. And if you do skip over a million rows, that's actually a huge amount of performance that you're losing by doing skip. So now not everybody is going to develop apps that have pagenation for a million uh uh you know elements. But if you are dealing with very big very large results set pagionation then instead of doing uh pagenation via skip you should be doing something called uh key set pagenation which in one word is basically use the wear clause instead of the skip clause and or the offset clause and what that means is basically you order by the let's say you order by the ID right you're pagionating instead of skipping the first 100 elements simply remember what is the last ID that you showed the user and Then the your next query should be give me all the rows after that ID and limit that to 10. And once you do this, the wear clause obviously is very very well optimized for indexing. So the database will very efficiently use the index to go precisely to the row that needs to be shown to the user.

[00:48:38]>> Yeah.

[00:48:39]>> And then work from there. Okay.

[00:48:41]>> Then work from there.

[00:48:43]>> So I'm going to be updating all my talks that that have pageionation to to use that. So He said pagenation like take a look at the at the at the docs there like it's it used to be one of my crusades like a couple years ago I was very excited about this and everybody who listened I I said yeah convert all your offsets to wear clauses because when you're pagionating uh it's it's more complicated than this but it's definitely u it's an important >> piece thank you shy that's that is good so so one thing yeah go ahead can offset >> and that's that you can miss or yeah show some records multip multiple times if update happens on the database side and then some people are confused like >> why I'm seeing this record twice or why I'm missing this one because hey I know it's there it's like >> and that's where if you have updates and you're and people are updating the uh >> exactly >> then then that's going to throw out throw off your uh skip >> but not your wear that's that's another advantage >> wear but not the wear because because those new records even though they may show up be because of the wear clause earlier they won't because of the >> exactly so the other advantage which thank you very much ei for bringing it up it's not just more efficient it's also better in the face of concurrence concurrent updates um exactly it's it's more bulletproof pagenation that will not miss rows and it will not duplicate rows in the pagionation interface >> so I I was wrong I love being correct I love learning No, I love learning the the better ways of doing it. So, and I think our audience probably loves it too. So, anti-attern six is the common don't do a don't do synchronized uh uh queries. Don't synchronize. Use async.

[00:50:41]Uh, and this really means don't use result or or uh uh that's a big one result. Don't don't do a query and then say result because what that is doing is that is locking up uh the IO thread and that is causing the your application to basically wait for that whole result set to uh to come back. use uh and we have great async uh capabilities in innet.

[00:51:20]So just know that uh so the fix could be doing a two two list async and that will that will uh is a much better way to to use asynchronous uh programming. But remember you have to do you have to async the method you have to await the uh the query. So just re uh remember that. So that's a that's just basically we're telling developers that uh async is very very important with uh uh querying the database because we don't want to just sit there and wait for that uh for that query to come back which during testing isn't going to be exposed because you're you're the only user and usually the database is on your local machine. So, it's not going to surface so much until it gets out to production in my opinion.

[00:52:25]>> In fact, I don't know whether it was in nine or 10, we removed support for synchronous basically operations in the Cosmos provider.

[00:52:37]>> Oh, good.

[00:52:39]>> So, you cannot execute synchronous operations. We removed it from the tests and everything. Everything for Cosmos needs to be asynchronous otherwise uh we throw an exception or something like that.

[00:52:51]>> Yeah.

[00:52:53]>> Is there any any thought of doing that for SQL server?

[00:52:59]>> So the the the important thing here is that the Cosmos SDK the low-level Cosmos thing actually doesn't support sync operations. In fact most >> most modern things by the way all new things in all new APIs getting introduced. Uh uh everything basically is async only. Sync is basically sort of dead. It's I'm going to I'm I'm saying this very forcefully. I may be exaggerating. I'm pushing the point a bit too hard, but I I want to double down on what Woody said. Uh you should at this point in 2026 only be using async in general, right? You should be avoiding all sync APIs. And if we had been designing.NET from scratch today, we would not have sync APIs at all.

[00:53:40]Specifically with the the Cosmos thing, we actually made a bad again another one of those bad decisions back in the day.

[00:53:46]We said so the Cosmos SDK is sync only, but uh we're going to still allow you to so it's async only, but we're still going to allow you to do sync by doing uh uh sync over async, which is a really really bad sort of antiattern.

[00:54:00]>> And yeah, I mean we don't have to talk about this. It's >> horrible. It makes me >> It's horrible. Exactly. So then eventually like we did the the breaking change of removing that and and saying yeah okay Cosmos only supports async so the Cosmos EF provider will only support async because that's what we have. SQL server is different because SQL client the the driver does support sync and async. So >> it's really I know but it shouldn't be done. So I mean I I I still don't think it's it's um it shouldn't be our role to force people, you know, to force people to do stuff. But I agree with you. I agree.

[00:54:37]>> But that's another glo that would be another EF Core global setting to say do not allow anyone to do uh synchronous queries.

[00:54:49]>> So why stop there? We should be doing this at the level of.NET itself maybe.

[00:54:53]Right? That's even better.

[00:54:55]>> Okay. Well, you you take that up with uh that team. So, yeah. Awesome. Well, I know we don't we only have about five minutes left. So, we have two anti-atterns left. So, the seventh is >> using I'll say in here in my notes it says uh as no tracking on mutations. Uh so what that really means is you know so many people uh like the idea of uh as no tracking but don't use it when you're actually going to uh to do save changes because it wasn't meant for it wasn't really meant for that or if you are going to do it there there's some hoops to jump through and stuff. So, I always tell people uh only use as no tracking when you're when you know that that result set only needs to be read only and you're not going to uh change anything on it like in a matter of like minimal APIs and web APIs. Uh so that there is like there is a fix. So if you want to do it uh EF Core will detect the change and and generate uh updates.

[00:56:22]But I know in in uh EF Core 10 in my notes it says execute update async is a preferred path for bulk and targeted uh setbased updates. Uh no entity loading notes change tracking. So uh so yeah that's a delegatebased uh setter. So uh will allow for conditional logic but for the most part uh as no tracking leave that for your your APIs and for your uh result sets that you know are just are just uh read only and uh then you don't have to get into too much trouble. But I'm sure Shai will uh tell some some uh >> since our time is short, let's let's move on to the last point. I don't want to >> just because I don't want to.

[00:57:20]>> Cool. Cool. So the last one is one that I will say 80 developers don't look into and and this is the anti-attern 8 missing index coverage.

[00:57:33]So for me I every query that I write I go into if it's SQL server I go into SQL server and take a look at the query plan. that every every single one and I take a look at that query plan and that query plan will tell me if indexes are missing, if an uncclustered uh index is missing, which is which will totally be the case. Uh in here in my notes I have some some stuff to do on the net side to say uh to do a fix like in your creation of your DB context you can say has index you can do aindex uh which is which is great and and that helps or you can do it in your uh annotation on your uh entity class, you can say that there's an index on this on the table that this entity represents.

[00:58:43]But I always like to just go back to SQL Server and and find uh where my indexes are missing. You can SQL server will either tell you or if you take a look at the execution plan the visualization of it and you find uh one of the sub queries uses like 80 90% of the of the uh uh total allocation for your your overall query. that usually will tell you uh that you have some type of missing index also. So, so my my suggestion is to be a detective, learn how uh SQL server works and execution plans and go in there and actually make sure that your queries run efficiently on SQL Server and don't have missing indexes.

[00:59:46]That's a that's a great point. It reminds me like the the what we started with with N plus1. I sometimes see developers concentrating on the wrong thing like going and hunting down allocations or microoptimizing their code while their database query is missing u you know an index which is completely completely ridiculous because that is so much more important than any little uh you know optimization. So always always concentrate on the right thing. Um I I will also say it's important. So indexes also aren't free.

[01:00:17]Indexes both cost in terms of like disk space as a user here just pointed out they also cost that people think about this a bit less maybe indexes have a cost in terms of write performance. So if you're right heavy >> y >> uh as every time you're you're doing a write then you need to update the indexes and if you have uh 10 indexes on a given table because you need to do wear on every each and every one of the the columns you're going to be paying for that on the right side of things right y >> so it it's it's also I would advise it's also a matter of not always just adding indexes you have to stop and think about modeling your database like a good DBA and all of that stuff it's also worth remembering that some queries are hot and others are not like hot or not kind of thing, right? You don't want an index and you don't want to optimize a query that's going to run once and nobody cares about. That's an analytical kind of like once a day report a report that run once a once a week and you don't care how long it takes >> and exactly rate too right that's why this is why you hire a DBA. Yeah.

[01:01:21]>> Well, you hit I was going to bring it up but Raith too, thank you for bringing it up. There is a reason why we have database developers and DBAs and and people that it's their jobs to uh >> absolutely work on that.

[01:01:38]>> One of the one of the things about EF is it makes it easy for you to you're a developer. You're you're insulated away.

[01:01:44]You don't even need to know SQL, right?

[01:01:46]All you have to do is write link. It's wonderful. It makes it very easy for you to kind of lose touch with the actual reality of what's happening under the hood, right? and the fact that you have to think about you know you do have to be able to read a query plan you have to be able to go and for your database right this is different for postgress for SQL server you have to be a like if if you care about performance in any way maybe you're working on something where there's no performance fine doesn't matter if you care about performance in any way in a in a serious production app you have to be able to look you have to look at the logs of the SQL that EF is actually producing you have to look at the query plans produced by these queries and you have to identify the hot queries that are worth your attention, not get lost, you know, because there's tons of queries, not all of them matter.

[01:02:28]And you have to be able to actually, like Woody just said, look at your query plan and identify what is making it slow. Are you if you're just missing an index, that's wonderful. Obviously, sometimes it's much worse and you have to tweak things around and remodel like your entire thing.

[01:02:42]>> Exactly.

[01:02:43]>> That's much worse. If you can just fix it with an index, you're relatively, you know, in a good position.

[01:02:48]>> Yeah. It comes down to I always tell people just because your query gets data back doesn't mean that it's it's efficient.

[01:02:58]>> Absolutely.

[01:02:59]>> And that that applies to almost everything that you you you mentioned today by the way. That that exact sentence I I love it. I love that way of saying it. Like N plus1 works fine. It brings you the data. Uh cartisian explosion works fine. It brings you the data. All of these things will kill your app if you're doing if you're doing them wrong like if these antiatterns. So it looks fine. It brings you the data and it will still kill your application with any serious um load. So >> yep. Awesome. Well, that's that's all I have. So >> that's awesome because we are right on time.

[01:03:33]>> Yes.

[01:03:34]>> Amazing.

[01:03:35]>> So thank for presenting us these anti patterns. It's uh it's always nice to have a some kind of opinion. So somebody brings a list and gives us like, hey, these are the ones that I see the most and stuff like that because obviously we see all sorts of weird usages of VF, but having like an outside perspective um that you bring is uh is always helpful and you can see it in the chat that people are discussing different different approaches and stuff like that. So it's uh it's really really nice >> and I'm sorry I didn't bring any slides with me. So >> that's slides.

[01:04:15]>> Yeah. Yeah, I think I think uh just having good good discussion, but there are lots of other anti-atterns that we could talk about too, but uh >> but we could do those for another day.

[01:04:29]So, >> absolutely.

[01:04:30]>> Awesome.

[01:04:31]>> Absolutely.

[01:04:32]>> Awesome. Thanks for showing us your antiatterns, Moody. Uh I'm pretty sure we will have you back in sometime in the future.

[01:04:41]>> For now, that's all we have. Uh guys, please uh check the schedule and the website because we will be scheduling a new stream for next month again. And yeah, see you next time.

[01:04:54]>> Is it is it my thing about temporal tables? Do you remember what's up next time?

[01:04:59]>> I don't remember from the head. So it will be a surprise.

[01:05:02]>> Okay.

[01:05:03]>> I hope so.

[01:05:03]>> Surprise then.

[01:05:05]>> Awesome.

[01:05:06]>> All right. Very cool. Bye everybody.

[01:05:09]>> Bye.

[01:05:11]Heat.

[01:05:25]Heat.

[01:05:33]Heat. Heat. N.

[01:05:48]Heat. Heat. N.

[01:06:36]Heat up here.

[01:06:52]Heat. Heat. N.

[01:07:02]Heat. Heat. N.

[01:07:26]Hallelujah.