Case 3: The Data Hog - Over-fetching and the Power of Projections

The Scenario 📝

• System: An ASP.NET Core web application.
• Problem: A page that lists blog posts only needs to display the ID, Title, and AuthorName. However, the Post object in the C# code contains many other fields, including a Content field that can hold thousands of characters.

The Problematic Code (Fetching the Whole Object)

The most natural way to write this code with EF Core is to fetch a list of Post objects.

public async Task<List<Post>> GetPostListAsync()
{
    // The mistake: Fetching ALL columns for 100 posts,
    // including the huge "Content" column.
    var posts = await _context.Posts
                              .AsNoTracking()
                              .OrderByDescending(p => p.CreatedAt)
                              .Take(100)
                              .ToListAsync();
    return posts;
}

The Hidden Bottleneck 🧐

1. Wasted Network Bandwidth:
   • EF Core will generate a SQL query like SELECT "p"."id", "p"."title", "p"."content", ... FROM "posts" p ....
   • The database has to read the large Content column from the disk and send all of that data over the network to your application. If each post's content is 10KB, you are sending 1MB of unnecessary data just to get 100 posts.
2. Wasted Application Memory (RAM):
   • Your application has to use memory to store that entire 1MB of data.
   • The JSON serializer (which sends the data back to the browser or client) also has to work harder to process these large objects.
3. Increased Database Load: The database also has to do more work (more I/O) to read the large columns from the disk.

The Solution: Use Select to Create a Projection ✅

• The Logic: Instead of fetching the entire object, we tell EF Core to only get the columns we actually need and "project" them into a smaller, custom object (often called a DTO - Data Transfer Object, or a ViewModel).

• The Optimized Code:

  // Define a small DTO class
  public class PostListItemDto
  {
      public int Id { get; set; }
      public string Title { get; set; }
      public string AuthorName { get; set; }
  }
  
  public async Task<List<PostListItemDto>> GetPostListAsync()
  {
      var posts = await _context.Posts
                                .AsNoTracking()
                                .OrderByDescending(p => p.CreatedAt)
                                .Take(100)
                                // Use .Select() to create a projection
                                .Select(p => new PostListItemDto {
                                    Id = p.Id,
                                    Title = p.Title,
                                    AuthorName = p.Author.Name // EF Core will create the JOIN automatically
                                })
                                .ToListAsync();
      return posts;
  }

The Results ✨

1. More Efficient SQL: EF Core now generates a highly efficient SQL query: SELECT "p"."id", "p"."title", "a"."name" FROM "posts" p JOIN "authors" a .... It completely ignores the Content column.
2. Reduced Network Bandwidth: The amount of data sent over the network is drastically reduced, maybe from 1MB down to just a few kilobytes.
3. Reduced RAM Usage: The application only needs to allocate memory for the small DTO objects.
4. Faster Overall Request: The entire process, from the database to the application, is faster because less data is being handled.

Conclusion:

• Golden Rule: "Select only what you need."
• Never fetch an entire entity just to use a few of its properties.
• Always use .Select() to project data into DTOs or ViewModels. This is one of the most important and effective optimization techniques when working with an ORM.