It started like any other workday. I was building an endpoint to return a list of users along with their posts and comments. Easy, right? Fire up Entity Framework, write a clean LINQ query, hit F5. Done.

Until my API took forever to respond. My local machine’s fan went full blast. And the SQL logs? Oh boy. Turns out, Entity Framework fetched over 100,000 rows. In one query. All because of a monster JOIN I didn’t mean to create.

The Problem

Here’s what I wrote:

var users = await _context.Users
    .Include(u => u.Posts)
    .Include(u => u.Comments)
    .ToListAsync();

I thought, “Cool, I’ll get users with their related posts and comments. Simple.” But what I actually got was a Cartesian explosion. Every user was joined with every post and every comment, multiplying rows exponentially.

Result? A dataset large enough to crash my browser and shame my database.

The Solution

The fix wasn’t just adding pagination (which is definitely important in real apps). It was restructuring the query to avoid fetching everything in one giant join.

I broke the query up by using:

var users = await _context.Users
    .AsSplitQuery() // Let EF run separate queries behind the scenes
    .Include(u => u.Posts)
    .Include(u => u.Comments)
    .ToListAsync();

This stopped the explosion. EF ran separate queries internally, dramatically reducing row duplication and memory pressure.

Deeper Explanation

When you include multiple one-to-many relationships (like Posts and Comments), EF creates SQL JOINs for each. But JOINs multiply rows. So if a user has 10 posts and 15 comments, you don’t get 25 rows — you get 150 (10 × 15).

That’s how I ended up with a 100,000-row query from just 100 users.

Best Practices

• Use .AsSplitQuery() when including multiple collections to avoid Cartesian explosions.
• Project only the data you need with .Select().
• Be cautious with multiple .Include()s on collection navigation properties.
• Remember to paginate large queries in real apps (not shown here for simplicity).
• Profile and analyze SQL queries during development.

According to Microsoft’s official documentation for the .AsSplitQuery() method:

To work around the performance issues described above, EF allows you to specify that a given LINQ query should be split into multiple SQL queries. Instead of JOINs, split queries generate an additional SQL query for each included collection navigation

Conclusion

I learned that Entity Framework gives you great power — and with great power comes… lots of JOINs. Now, I’m more careful with includes, always profile my queries, and keep a wary eye on SQL logs.

Don’t be like me. Don’t accidentally fetch 100,000 rows.

Unless you want to test your laptop’s emergency thermal shutdown.