Don't Make These Entity Framework Core Mistakes

I don't think a t shirt's ever been more appropriate than if you're doing one of these five things with Entity Framework Core. Let's cover some big fails. Check out this code for instance. It looks pretty safe. All we're doing is pulling some orders out of the database that we're gonna send out to an API request or perhaps show on a report somewhere.

So you're thinking, what's wrong with that? Well, the way Entity Framework works is when you pull data out of a database, it wraps it in its own little Entity Framework secret sauce that helps it track whether you've changed that object. So if you change a property, it knows that that object is dirty now. It needs to be saved, or and specifically, that property even needs to be saved. It's really efficient at doing that.

But if you're not gonna be changing this data and saving it back, there's no reason for it to do any of that processing. And you think, you said it's pretty efficient. Yeah. But if you're pulling a lot of data out of the database, it really adds up. You'll find great efficiencies by using the as no tracking extension method.

The as no tracking extension method actually pulls your data out just the same. It feels the same to you. The only difference is Entity Framework isn't tracking that object for changes anymore, which means while you could technically attach that object back to your context and save it, managing what's changed and all that would be on you and probably shouldn't be done unless you've found some specific bottleneck through your own profiling or benchmarking that means you need to take away the Entity Framework adding all of its tracking to it.

Next up, well, let's start by saying I'm a lazy developer. I wanna automate all the things. So lazy loading sounds amazing to me. Why would I wanna get that data eagerly, when I can just wait and get it when I need it? It sounds fantastic.

Why would that be a problem? Well, let's look at some more code. This is a simple example where we're just foraging through a list of blogs out of the context. And then for each of those blogs, we're getting all the posts associated with it. And for each of those posts, we are writing it to the console.

That seems innocent enough. What's the big deal, Michael? Well, let's look at EF Core's statement logging to see what's going on behind the scenes. What the heck are all these database requests? With lazy loading, we're loading those blogs initially, but each time we step through a blog, we're hitting the database to load in the posts for that specific blog, which could really add up over time.

You can see where this makes it really simple to come upon n plus one problems. And for that reason, Microsoft doesn't recommend using lazy loading. So what do we do instead? Our first option is eager loading, where we say dot include those posts. So the data we get back in that to list includes all the blogs and all the posts at one time.

But if we look at that code, we're only using the URL property of the blog. So our best option here is to use a select and just select out the URL from the blog and the posts. This is gonna reduce all the data that we're sending back and forth over the network. Well, that's fantastic, Michael. That's what we're gonna do from now on.

Well, hold on. That may not be your best choice, especially if you need all the fields from the blog and all the fields from the post, and especially if you need all the fields of some other related entity. You might find yourself sending a lot more data over the line than you need because of Cartesian exploding, which leads us to our next fail.

Cartesian explosion. It sounds like a term from Star Trek, right? But what is it? Let's look at some code where we're pulling back that blog, but we're also pulling back its posts and any contributors to that blog. In this example, both contributors and posts are navigation properties of a blog.

That means that if a specific blog has 10 contributors and 10 posts, you're gonna get a record set of 100 records back. That means you're sending a lot of data unintentionally to the client, and it only gets worse as you add other related joins. One important thing to note here, though, when you're going to look back at your code, Cartesian explosion doesn't happen if those includes happen at different levels of relationship. For instance, a blog to a post to a comment. You wouldn't have Cartesian explosion here.

Only when two objects are at the same level of relationship from the parent. Luckily, Entity Framework Core makes it really simple to avoid this. They have the split query extension method that will actually break those up into separate SQL queries, and then it will combine them all together before it returns you back the list. And as we refactor that to use AskSplitQuery, you see the SQL that it generates is actually two statements that get executed, but luckily, Entity Framework makes that pretty and seamless for us once we get it back. One thing to note, though, is if you're using AskSplitQuery with pagination or if you're using skip or take, you need to make sure what you're sorting by is unique.

Don't just sort by, say, a date if that date could be duplicated. Perhaps append the ID to the sort just to make it wholly unique.

Otherwise, as you paginate, you may not get the same results every time. The next fail is one you may not even notice. It's one that you may overlook until it bites you. And that's buffering versus streaming. Buffering means loading all your query results into memory, where streaming is in the framework giving you one record at a time, and the record set is never completely loaded into memory.

In theory, the memory requirements for a streaming query are never larger than one record at a time, whereas buffering, it could grow exponentially depending on how many records you're pulling out of the database. Now whether a query is streamed or buffered depends on how it's evaluated. Let's take a look at some more code. These first two lines use to list and to array. That causes our entire result set to be buffered into memory because we have to load them there to have that object.

In comparison, when we use that for each, we're actually streaming that data in and it's only loading one record set at a time. There are definitely use cases for both. Like if you were sending that data out of a API response, you need the whole record set, so to list that thing. But if you're just gonna go for each through something, maybe you wanna go the streamed route. Now sometimes you have a separate method in C sharp that you wanna run against each of those records.

For instance, like an additional filter. But of course, Entity Framework's not going to convert your C sharp method into SQL. So you'll run like dot to list and then say dot where some dot net method and pass in your object. But this is usually terrible, especially on large record sets because you're buffering the entire result set when you call to list or to array and then passing it into that where clause. A better option would be to use as enumerable because that still streams the records into that method so that you've never loaded more than one record at a time into that where clause.

Now if your query only returns a few records, then you could probably overlook all of this because it's not gonna make a big difference and it's probably why it's overlooked most of the time. But if you are returning a large set of records, you may need to start thinking about whether you are streaming or buffering to improve the efficiency of your code.

Lastly, let's talk about query parameterization. When Entity Framework first receives your linked query tree, it has to go compile it or turn it into SQL. Now because that takes some overhead, Entity Framework is smart enough to cache those queries based on their query tree shape. That way, queries with that same structure can reuse compiled queries from previous outputs, and it speeds up subsequent requests. Check out these two queries.

Now since the expression tree for each contains different constants, they're going to get compiled into two separate queries. That means the compiled SQL is gonna look a little different for each. And because the SQL is different, your database is also gonna have two different query plans for each version. But a small modification could change this considerably. Let's look at this change.

Because the title is now parameterized, we can create one EF Core compiled execution plan, and we can have one query plan on the database that just takes in a parameter. One thing to note, is that there's no need to go in and parameterize every query. It's perfectly fine to have some queries with constants. And in fact, some databases can perform some optimizations when you provide constants that they can't when the query is parameterized. So your your mileage is gonna vary based on your use case and situation.

So be aware of that. Hopefully, this reminded you of some things that, you should not do or you learned some things you should stop doing. Until next time.