Building Read Models with EF Core Projections

You can't really argue against the importance of performance in any application. Even if the system is not time-critical, efficient and time-saving operations are cornerstones of the system. Data fetching is the primary step for improving the performance of most applications. If you use Entity Framework Core (EF Core), a slightly inefficient query can painfully hurt the user experience. Just like a plane that offers comfortable, fast travel, but a slight mishandling or misconfiguration can cause turbulence or slow you down. Likewise, EF Core is a powerful ORM library with tons of features, but if it is not used correctly, it can burden the application. In this post, I will show you how projections can give you greater leverage with EF Core.

What are projections in EF Core?

A projection is a way to map a set of properties to a different output format. In EF Core, it maps fetched results to a desired structure, eliminating unnecessary properties.

What Happens When You Don't Use Projection

Using EF Core without projections can lead you to the following pitfalls:

• EF Core queries load the entire dataset into memory, including unnecessary columns. Even if your table has 20+ columns and you only need 4, each row still wastes resources. It gets costly when you haven't applied lazy loading, since the associated entities are loaded into memory.
• SQL queries that return more data take longer to deserialize.
• EF Core's change tracking tracks more columns and rows when you get data without projection, which adds significant CPU and memory overhead.
• Such queries trouble the API serializer with unnecessary data, leading to performance bottlenecks.
• Loading unnecessary associations along with the central entity can lead to the N+1 problem.

Examples of Non-projection and projection with EF Core

To understand the concept, let's create a simple Console application with a database. I am creating a PostgreSQL database and will see how non-projection and projection perform.

Step 1: Install the packages

Install the necessary packages

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.Extensions.Configuration
dotnet add package Microsoft.Extensions.Configuration.Json

Step 2: Create appsettings.json

Adding appsettings.json with a database connection string.

{
  "ConnectionStrings": {
    "PostgresConnection": "Host=localhost;Port=5432;Database=moviesdb;Username=postgres;Password=1234"
  }
}

Step 3: Define Models

Movie class.

namespace MovieProjectionDemo.Models;

public class Movie
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public int ReleaseYear { get; set; }
    public string Genre { get; set; } = string.Empty;
    public decimal Budget { get; set; }

    public int DirectorId { get; set; }
    public Director Director { get; set; } = null!;
}

Director class.

namespace MovieProjectionDemo.Models;

public class Director
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Country { get; set; } = string.Empty;

    public List<Movie> Movies { get; set; } = new();
    
}

Step 4: Configure DbContext

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using MovieProjectionDemo.Models;

namespace MovieProjectionDemo;

public class AppDbContext: DbContext
{
    public DbSet<Movie> Movies => Set<Movie>();
    public DbSet<Director> Directors => Set<Director>();

    private readonly string _connectionString;

    public AppDbContext()
    {
        // Simple reading from appsettings.json
        var config = new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile("appsettings.json")
            .Build();

        _connectionString = config.GetConnectionString("PostgresConnection");
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql(_connectionString);
    }
}

Step 5: Set up appsettings.json in the project

By default, a console app will expect the file in the bin directory. To read newly added appsettings from the root directory, add the following inside the <Project> tag of the application's project file:

<ItemGroup>
  <None Update="appsettings.json">
    <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
  </None>
</ItemGroup>

Step 6: Non-Projected Query

In Program.cs, add the query with a stopwatch to observe the time difference.

using System.Diagnostics;
using Microsoft.EntityFrameworkCore;
using MovieProjectionDemo;

using var context = new AppDbContext();

// Start the stopwatch
var sw = Stopwatch.StartNew();
var movies = context.Movies
    .Include(m => m.Director)
    .ToList();

// Stop the stopwatch
sw.Stop();

Console.WriteLine("\n--- Non-Projected Query ---");
foreach (var movie in movies)
{
    Console.WriteLine($"{movie.Title} ({movie.ReleaseYear}) - Directed by {movie.Director.Name}");
}

Console.WriteLine($"\nTime taken for non-projected query: {sw.ElapsedMilliseconds} ms");

The underlying SQL query looks something like this:

SELECT 
    m."Id",
    m."Title",
    m."ReleaseYear",
    m."Genre",
    m."Budget",
    m."DirectorId",
    d."Id" AS "Director_Id",
    d."Name" AS "Director_Name",
    d."Country" AS "Director_Country"
FROM "Movies" AS m
LEFT JOIN "Directors" AS d ON m."DirectorId" = d."Id";

Step 7: Project query

Add the project query in Program.cs with a stopwatch.

sw = Stopwatch.StartNew();
var movieSummaries = context.Movies
    .AsNoTracking()
    .Select(m => new
    {
        m.Title,
        m.ReleaseYear,
        DirectorName = m.Director.Name
    })
    .ToList();

sw.Stop();

Console.WriteLine("\n--- Projected Query ---");
foreach (var m in movieSummaries)
{
    Console.WriteLine($"{m.Title} ({m.ReleaseYear}) - Directed by {m.DirectorName}");
}

Console.WriteLine($"\nTime taken for Projected query: {sw.ElapsedMilliseconds} ms");

Or, a better way we can define a model.

public class MovieDetails
{
    public string Title { get; set; } = string.Empty;
    public int ReleaseYear { get; set; } = 0;
    public string DirectorName { get; set; } = string.Empty;
}

Then fetch the results.

var movieSummaries = context.Movies
    .AsNoTracking()
    .Select(m => 
        new MovieDetails()
    {
        Title = m.Title,
        ReleaseYear = m.ReleaseYear,
        DirectorName = m.Director.Name
    })
    .ToList();

An equivalent SQL query will be:

SELECT 
    m."Title",
    m."ReleaseYear",
    d."Name" AS "DirectorName"
FROM "Movie" AS m
LEFT JOIN "Director" AS d ON m."DirectorId" = d."Id";

Step 8: Run and test

dotnet run

Output

We intended to return only the title, release year, and director's name. However, there is a noticeable difference in just 3 record fetches in both approaches. That's the magic of using EF Core properly. Actually, in the prior query, the entire movie data, along with the associated director table, is fetched and stored in memory. Have a look at the debugger:

I not only loaded all the movie fields, such as Budget and Genre, but also everything from the Director table. Also, the Director table again loads all the Movie associations, which means you are falling into unwanted entity navigations.

We observed only the execution time. Imagine the memory penalty by not using projections. Also, when the same data is sent over the network as the API response, it can cost you in your pocket and user experience.

When looking at the projected code, we see another pattern:

Conclusion

EF Core is a powerful ORM and is counted among the top choices by .NET developers. Its extensive list of methods and ease of data manipulation make it ideal for the data access layer. However, like any other tool, you need to use it properly to leverage it more effectively. Using projections is a critical technique for performance and resource optimization when fetching data. It reduces fetched properties to only the required ones, eliminating memory and time penalties. In this post, I highlighted some issues in EF Core queries and how projection can address them.

elmah.io: Error logging and Uptime Monitoring for your web apps

This blog post is brought to you by elmah.io. elmah.io is error logging, uptime monitoring, deployment tracking, and service heartbeats for your .NET and JavaScript applications. Stop relying on your users to notify you when something is wrong or dig through hundreds of megabytes of log files spread across servers. With elmah.io, we store all of your log messages, notify you through popular channels like email, Slack, and Microsoft Teams, and help you fix errors fast.

See how we can help you monitor your website for crashes Monitor your website