Your First Query

Analogy categoryAnalogies

Once DbContext and DbSets are configured, you query data using LINQ methods (Where, FirstOrDefault, ToList) or LINQ query syntax against a DbSet, and EF Core translates that into SQL executed against the database, returning materialized entity objects.

🏏

Cricket analogy: Like telling a scorer 'give me every century scored by Rohit Sharma against Australia' and getting back a formatted list, writing context.Books.Where(...) tells EF Core what you want and it returns materialized C# objects.

Writing a Simple Query

A simple query like context.Books.Where(b => b.PublishedYear >= 2020).ToList() does not run until you enumerate it — EF Core builds an expression tree and only sends SQL to the database when you call ToList(), FirstOrDefault(), or otherwise iterate the query, a behavior known as deferred execution.

🏏

Cricket analogy: Like a request for 'all wickets by Bumrah in death overs' sitting unexecuted until the scorer actually pulls the report, a LINQ query stays unexecuted until you call ToList() or enumerate it.

Filtering, Projecting, and Async Queries

Use Select() to project entities into lightweight shapes (anonymous types or DTOs) so only the needed columns are fetched, and prefer async methods such as ToListAsync() and FirstOrDefaultAsync() in ASP.NET Core so a query's I/O wait doesn't block a request-handling thread.

🏏

Cricket analogy: Like requesting just 'name and strike rate' instead of a player's full profile for a quick scoreboard, Select() projects only the columns you need instead of loading the full entity.

Understanding Deferred Execution and the N+1 Pitfall

Because navigation properties can be lazily loaded on access, accessing a related entity (like book.Author.Name) inside a loop over many rows silently triggers one extra query per row — the N+1 query problem. Use Include() to eager-load related data in the original query and avoid the extra round trips.

🏏

Cricket analogy: Like a commentator who calls out each fielder's individual stats one by one during a live over instead of pulling the full team sheet beforehand, lazy-loading navigation properties inside a loop triggers a separate query per entity — the N+1 problem — which Include() avoids by eager-loading.

csharp

public class BooksController : ControllerBase
{
    private readonly BookStoreContext _context;
    public BooksController(BookStoreContext context) => _context = context;

    [HttpGet]
    public async Task<IActionResult> GetRecentBooks()
    {
        var books = await _context.Books
            .Where(b => b.PublishedYear >= 2020)
            .Include(b => b.Author)
            .Select(b => new { b.Title, AuthorName = b.Author.Name })
            .ToListAsync();

        return Ok(books);
    }
}

Accessing a lazy-loaded navigation property (like book.Author.Name) inside a foreach loop over many books triggers one extra query per iteration — the N+1 problem. Use Include() to eager-load related data in a single query instead.

LINQ queries against a DbSet are translated into SQL by EF Core, not executed in memory.
Query execution is deferred until you call ToList()/ToListAsync() or otherwise enumerate the query.
Select() projects entities into lightweight shapes, reducing the columns fetched.
Async methods (ToListAsync, FirstOrDefaultAsync) avoid blocking threads in web applications.
Include() eager-loads related entities in the same query, avoiding the N+1 problem.
Lazy-loading navigation properties inside loops can silently trigger many extra queries.