Engineering/Coding Standards/C#/Entity Framework Core/Ef Linq/

EF Core LINQ Query Standards · EF-03

Project to DTOs or anonymous types for read-only queries · EF-03.1 · MUST

Use .Select() to project only the required columns into a Data Transfer Object (DTO) for the callers needs. This is crucial to reduce the queries impact on database performance, application memory and network load.

If a projection is returned across an API boundary, or is reused multiple times, use a DTO. Anonymous types are fine for locally-scoped or one-off projections.

Good Example: Projecting to a DTO

var bookSpineDtos = await _context.Books
    .WhereIsPublished()
    .Select(book => new BookSpineDto
    {
        Id = book.Id,
        Title = book.Title,
        AuthorName = book.Author.Name,
        PublisherName = book.Publisher.Name
    })
    .ToListAsync(cancellationToken);

Good Example: Projecting to an anonymous type

var publishedBookTitles = await _context.Books
    .WhereIsPublished()
    .Select(book => new
    {
        book.Id,
        book.Title
    })
    .ToListAsync(cancellationToken);

Bad Example: Fetching full entities for a read

// Pulls every column on Books, Authors and Publisher
var bookSpineContents = await _context.Books
    .Include(book => book.Author)
    .Include(book => book.Publisher)
    .WhereIsPublished()
    .ToListAsync(cancellationToken);

Don't `Include` unnecessarily · EF-03.2 · MUST

Only .Include() related entities when they’re needed.

Use Include() when you are modifying entities via navigation properties without projection.

Including a navigation will load all columns of that table row into an entity, powering navigation properties. This could wastefully load an entire, unused collection of objects into memory.

IMPORTANT: When a query ends in a .Select() projection, .Include() is unnecessary, EF generates the necessary joins from the navigations referenced in the projection (see EF-03.1 Good Example).

There is

Good Example: Include only what's needed

var order = await _context.Orders
    .Include(order => order.LineItems)
    .FirstOrDefaultAsync(order => order.Id == orderId, cancellationToken);

foreach (var lineItem in order.LineItems) 
{
    lineItem.MarkAsShipped();
}

await _context.SaveChangesAsync(cancellationToken);

Bad Example: Including unused navigations

// Customer and Invoices are loaded but never read by the caller.
var order = await _context.Orders
    .Include(order => order.LineItems)
    .Include(order => order.Customer) // Customer is unused.
    .FirstOrDefaultAsync(order => order.Id == orderId, cancellationToken);

foreach (var lineItem in order.LineItems)
{
    lineItem.MarkAsShipped();
}

await _context.SaveChangesAsync(cancellationToken);

Bad Example: Redundant includes

var bookSpineDtos = await _context.Books
    .WhereIsPublished()
    .Include(book => book.Author) // Ignored by EF because the projection's book.Author reference already generated a join
    .Include(book => book.Publisher) // Ignored by EF because the projection's book.Publisher reference already generated a join
    .Select(book => new BookSpineDto
    {
        Id = book.Id,
        Title = book.Title,
        AuthorName = book.Author.Name,
        PublisherName = book.Publisher.Name
    })
    .ToListAsync(cancellationToken);

Use async methods for database queries · EF-03.3 · MUST

Database access is I/O-bound. Synchronous query methods block the calling thread for the duration of the round trip, which under load exhausts the thread pool and degrades throughput. Always use the async equivalents (ToListAsync, FirstOrDefaultAsync, AnyAsync, CountAsync, SaveChangesAsync, etc.) and pass a CancellationToken where one is available.

Good Example: Async query

var book = await _context.Books
    .FirstOrDefaultAsync(book => book.Id == id, cancellationToken);

Bad Example: Sync query

// Blocks the calling thread for the full database round trip.
var book = _context.Books
    .FirstOrDefault(book => book.Id == id);

Use `AsSplitQuery()` when including multiple collection navigations · EF-03.4 · SHOULD

Use .AsSplitQuery() when a query includes two or more collection navigations. EF then issues one SQL query per included collection instead of a single joined query, avoiding the duplication.

Without this, the resulting SQL will create a severe performance issue, resulting in an exponentially multiplied dataset.

Good Example: Splitting a query with multiple collection includes

var order = await _context.Orders
    .Include(order => order.LineItems)
    .Include(order => order.Shipments)
    .AsSplitQuery()
    .FirstOrDefaultAsync(order => order.Id == orderId, cancellationToken);

Bad Example: Single query with multiple collection includes

// LineItems and Shipments are joined together, so each order row is repeated and the result set is (LineItems.Count x Shipments.Count).
var order = await _context.Orders
    .Include(order => order.LineItems)
    .Include(order => order.Shipments)
    .FirstOrDefaultAsync(order => order.Id == orderId, cancellationToken);

Prefer navigation properties over explicit joins · EF-03.5 · SHOULD

EF Core can translate navigation properties directly into the appropriate SQL joins. Writing explicit .Join() calls duplicate information already expressed by the relationship in the model, is harder to read, and bypasses configured behaviour like global query filters. Use navigation properties unless joining on something that isn’t a configured relationship.

Good Example: Using navigation properties

var books = await _context.Books
    .Where(book => book.Author.Country == "GB")
    .Select(book => new { book.Title, AuthorName = book.Author.Name })
    .ToListAsync(cancellationToken);

Bad Example: Manual join

var books = await _context.Books
    .Join(_context.Authors,
        book => book.AuthorId,
        author => author.Id,
        (book, author) => new { Book = book, Author = author })
    .Where(bookAuthor => bookAuthor.Author.Country == "GB")
    .Select(bookAuthor => new { bookAuthor.Book.Title, AuthorName = bookAuthor.Author.Name })
    .ToListAsync(cancellationToken);

ef entity configuration

ef migrations