Last week I published a post about why I hate stored procedures. The internet had opinions.
There was a fair bit of pushback that I'd summarize as "EF Core generates ugly SQL."
Ok. Yah. Sometimes. Quite frequently, actually. And why does that matter? Why would that make me write stored procedures?
I want to take this seriously because I think there are two different complaints hiding inside "EF Core generates ugly SQL," and they deserve very different responses. One of them is basically aesthetic. The other is a real, legitimate concern — but it doesn't lead where the stored procedure defenders think it does.
"The SQL Is Ugly"
Let's start with the aesthetic complaint. EF Core generates SQL with subqueries, weird aliases, redundant-looking column selections, verbose formatting. It's 100% not the SQL you'd write by hand. It's not elegant. It's not pretty. It's functional.
Who. Cares.
First off, it's not meant for you. It's meant for EF Core. SQL is not poetry. Nobody's reading it at a coffee shop. Nobody's framing it on the wall. It's an instruction set for a query optimizer. The query optimizer doesn't care if your aliases are meaningful. It doesn't care about your indentation. It cares about the execution plan, and it's going to generate the same plan whether your SQL reads like prose or looks like it was written by a robot.
(Spoiler: it was written by a robot. That's the point.)
When someone tells me "EF Core generates ugly SQL," my first question is: did you actually detect a performance problem? Not "did you look at the SQL and feel uncomfortable." Did something actually run slow? Did a user complain? Did a query show up in your monitoring? Did it time out? Is it causing locking issues somewhere else in the app?
If the answer is no — if the SQL is ugly but the application works fine — then you don't have a problem. You have an aesthetic preference. And you're proposing to add architectural complexity (stored procedures, raw SQL, a parallel deployment pipeline, untestable data access code) to satisfy that preference.
We're paid to write and deliver features. We're paid to maintain existing systems. We're charged with and entrusted to preserve the return on investment (ROI) for the systems that have been written for the benefit of our employer. If you're working in enterprise software (and I suspect you are if you're reading this article), it took a LOT of money to write the system or systems that you work on. Maintainability matters.
So if the SQL is ugly, here's my advice: just don't look at it.
Seriously. Unless you have a reason to — a known, measured performance problem, a timeout, a bottleneck that showed up in monitoring — don't open the SQL logs and go looking for things to be upset about. You'll always find something. EF Core's SQL will always look weird. That's fine. It's doing its job.
You have other things to worry about.
"The SQL Is Inefficient"
Now here's where the conversation starts getting nuanced. Because sometimes EF Core does generate SQL that's genuinely inefficient. Not just ugly — actually slow. And the people raising this concern aren't wrong. But there are things to validate first.
First off, ok... so the query shows up in some monitor log somewhere as having problems. Maybe it's a pretty expensive query and maybe it has "Issues." Does this query dim the lights in your datacenter? Is it bankrupting you on your Azure SQL spend? Is it run 10,000 times a second and the disks are on fire? No? Then the right answer is to consider the heat death of the universe and just perhaps move on.
Heat Death of the Universe?
Wait. What? Heat Death of the Universe? What's that about? It's basically something that's destined to happen at some point in our universe when there is no more available thermodynamic energy left for chemistry to occur. The universe is essentially cold and inert.
Not to worry though. It's not expected to come any time soon. Current estimates say about 10100 years from now. That's a 1 followed by 100 zeros. And since I know we're a nerdy group and we like percentages: from Big Bang to Heat Death, the universe is approximately 0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000138% complete.
What's the Optimization ROI?
So when you're looking at that slow-ish query, you need to ask yourself about the level of effort it would take to fix it. Whether you fix it by tweaking the EF Core calls (not a horrible idea, BTW) or whether you fix it by dropping in your own SQL or a stored proc call — whatever you do to "solve" this performance problem, you have to ask yourself if you'll ever even measurably see any meaningful, impactful improvement before the heat death of the universe occurs.
If it takes you 8 hours to tune a query, are you ever going to see any meaningful improvement? Quite often the answer is no. You're optimizing something that nobody noticed was slow. Congratulations — you've spent a day making a query faster and the only person who'll ever know is you, staring at a profiler trace. (I know...that can be hella satisfying...but...come on...just between us nerds: that was a waste of time, right?)
Here's my actual advice: measure, set a threshold, monitor. When something crosses the line, fix that thing. Don't pre-optimize every query on the off chance that someday it might be slow. Because pre-optimization has a cost — it's complexity, it's stored procedures, it's raw SQL scattered through your codebase, it's additional complexity in your deployment pipeline. All of that is weight you're carrying for nothing.
That query that EF Core generated that's not breaking records for awesomeness? There's a real good chance that it's "fit for purpose" -- which is a fancy way of saying "good enough".
But Let's Say There Really Is a Problem
But let's say you DO have a real performance problem. It happens. Something is genuinely, measurably slow and users are noticing or it's burning up DTUs on Azure SQL and costing you Real Money™. Ok. Now I care. Let's talk about the places where EF Core actually gets developers into trouble — because the fix is almost never "switch to stored procedures."
Common Performance Problems with EF Core
Lazy loading in web apps. This one's a classic (that I've literally done myself). Lazy loading sounds like a performance optimization — "only load what you need, when you need it." In reality, for web applications, it's almost always a trap. First off, you need to think of the lifecycle of your DbContext: assuming ASP.NET Core, its life is almost certainly for the duration of a Request. Put another way, any lazy loading has to provide a measurable payoff inside of ONE HTTP request.
This sound grim but let's flip it around. The lifetime of your DbContext is known and it's short -- but it's also pretty well-defined. That means you know how your app will tend to query data to support a given operation. You know what you need. Lazy loading turns one efficient query into potentially dozens of one-off queries that fire individually as you touch navigation properties.
You think you're saving on performance. Instead you just won 200 extra round trips to the database that you didn't need.
For web apps, I'd say just turn lazy loading off. Know what you need, load it explicitly, move on.
The Include/ThenInclude doom loop. Here's where things get genuinely gnarly. You've got an entity with relationships, and those relationships have relationships, and you need to bring it all back. So you chain .Include() and .ThenInclude() calls, and EF Core generates a join query that's... let's be charitable... incomprehensible. Five, six, ten tables joined together. Columns everywhere. The SQL is just plain gross. 🤢🤮
But I want you to consider something. It might be ugly simply because you need a lot of data. Or maybe you're bringing back too much data.
If you're bringing back too much data, then maybe you can skip some of those Includes and now your JOIN count drops.
But what if you're in those cases where you really do need all that data?
If it takes JOINs on 10 related tables to populate your object structure, you're going to bring back 10 tables worth of data no matter what. Stick that in a stored procedure. You're still joining 10 tables. Did it actually meaningfully improve performance? You're still bringing back the same rows. You moved where the query lives, but you didn't change the fundamental cost of the operation.
The SQL looks ugly because the operation is complex. That's not an EF Core problem. That's a "you need a lot of related data" problem. And a stored procedure doesn't make that cheaper — it just moves it.
Subtle, Hidden N+1 queries. Another golden oldie. You load a list of entities, then iterate over them, and each iteration triggers another query for a related entity. This one's real, it's a fact of life, and it can be monster. But it's probably a developer mistake, not an EF Core deficiency. EF Core is just doing what you asked for. These things almost behave like lazy loading. The fix for this perf problem is to anticipate what you need for data -- and that probably means adding .Include(), or .Select() with projection, or AsSplitQuery() — not abandoning the ORM.
Architect for Being Wrong
I've been doing this stuff for a while. My rodeo count is getting up there. I'm good at my job and I know things. But you know what? I make mistakes ALL THE TIME. And performance problems usually come outta nowhere -- they almost never come from where you'd expect.
So here's the thing I actually care about that bigger than EF Core or stored procedures or whatever.
Accept that you're going to make mistake and accept that you'll have unpredictable performance problems. Then leave yourself room to make those mistakes.
You are going to write code that doesn't perform well. This is guaranteed. I do it. You do it. Everyone does it. The question is not "how do I make sure every query is perfectly optimized from day one?" That's a fantasy and a gigantic waste of time. The question is: did you leave yourself a place in your architecture to fix it?
This is what I mean by "architect your application so you have places to be wrong."
If your business logic is in C# behind interfaces, and your data access is behind repository contracts, and your EF Core implementation is hidden behind those contracts — then when something performs badly, you have a seam. You can swap in a raw SQL query. You can add caching. You can redesign the query. You can (yes, fine) call a stored procedure for that one specific case. You do it behind the interface. You minimize the blast radius of the problem and ideally, more often than not, nothing else in your application changes. You didn't even have to tell anyone you did it.
That's what good architecture buys you. Not perfection. Flexibility. Room to be wrong and recover.
Now compare that to the stored procedure approach. Your business logic is in the database. Your query is in the database. You're committed. Everything is welded together. When something performs badly... you rewrite the stored procedure? What if the performance problem is actually in the business logic? What if you need to restructure the data? What if the UX is the problem? What if the right answer is caching, not a faster query? You've got no seams. No flexibility. No room to pivot.
The developers worshiping at the altar of hand-tuned SQL are almost certainly optimizing the wrong thing. They're optimizing individual query performance at the expense of system-level adaptability and maintenance. They're making every query A+ and making the architecture an F.
The Tradeoff Nobody Wants to Acknowledge
Every architectural decision is a tradeoff. Here's ugly fact about "ugly SQL":
Hand-tuned SQL in stored procedures gives you maximum control over query performance. But as a bonus, it also gives you untestable business logic, a parallel deployment pipeline, string-based contracts that break at runtime, and an architecture has real limits on how it can grow and adapt to the needs of the application.
EF Core behind interfaces (think: repositories, adapters, mocks, and stubs) gives you testable data access, one deployment pipeline, compile-time safety, refactoring support, and architectural seams for when you need to pivot. It also generates ugly SQL.
That's the tradeoff. And I'll take ugly SQL with a testable, maintainable, adaptable architecture over beautiful SQL welded into an unmovable monolith. Every single time.
The SQL that EF Core generates is ugly. Yeah. I know. I'm not paid to care about that. I'm paid to deliver features and preserve application ROI. I'm paid to make my bosses and stakeholders happy. Make sure you're optimizing for what really matters.
-Ben
