Entity Framework Core: Querying Data

1. Fundamentals of Execution

In EF Core, you build queries using LINQ (Language Integrated Query).

• Deferred Execution: Methods like Where, OrderBy, and Select return an IQueryable<T>. They do not hit the database. They simply build the SQL statement in memory.

• Immediate Execution (Materialization): The SQL is sent to the database only when you call a "Terminal Operator" (like ToList, Count, First).

Sync vs Async

EF Core provides asynchronous counterparts for all terminal operators.

• Synchronous: Blocks the thread until the DB responds. Used in Console Apps or background jobs.

• Asynchronous: Frees up the thread while waiting for the DB. Mandatory for high-performance Web APIs (ASP.NET Core).

---

2. Retrieving Lists

Fetching multiple rows from a table.

A. Fetch All Records

Retrieves every row in the table.

// Synchronous
List<User> users = context.Users.ToList();

// Asynchronous
List<User> users = await context.Users.ToListAsync();

B. Filtering (Where)

Translates to SQL WHERE. Since Where returns IQueryable, it does not have an Async version itself.

// Build the query (No DB call yet)
var query = context.Users.Where(u => u.IsActive && u.Age > 18);

// Execute Sync
var listSync = query.ToList();

// Execute Async
var listAsync = await query.ToListAsync();

C. Ordering (OrderBy)

Translates to SQL ORDER BY.

var query = context.Users
    .OrderBy(u => u.LastName)
    .ThenByDescending(u => u.CreatedDate); // Secondary Sort

// Execution
var results = await query.ToListAsync();

---

3. Retrieving Single Records

These methods return a single object (or null).

A. Find (By Primary Key)

// Synchronous
User user = context.Users.Find(10);

// Asynchronous
User user = await context.Users.FindAsync(10);

B. First / FirstOrDefault

Used when querying by non-primary keys.

// Synchronous
var user = context.Users.FirstOrDefault(u => u.Email == "admin@test.com");

// Asynchronous
var user = await context.Users.FirstOrDefaultAsync(u => u.Email == "admin@test.com");

C. Single / SingleOrDefault

Used when business logic dictates uniqueness (e.g., retrieving a user by a unique reset token).

// Synchronous
var user = context.Users.SingleOrDefault(u => u.Token == "abc-123");

// Asynchronous
var user = await context.Users.SingleOrDefaultAsync(u => u.Token == "abc-123");

---

4. Projection (Select)

Fetching only specific columns. This is highly recommended for performance (fetches less data).

A. Select to Anonymous Type

var query = context.Users.Select(u => new 
{ 
    u.FirstName, 
    u.Email 
});

// Execute
var results = await query.ToListAsync();

B. Select to DTO (Data Transfer Object)

var query = context.Users.Select(u => new UserDto 
{ 
    FullName = u.FirstName + " " + u.LastName,
    Role = u.Role 
});

// Execute
List<UserDto> dtos = await query.ToListAsync();

---

5. Aggregation

Calculating values directly in the database (Sum, Count, Min, Max).

A. Count

// Synchronous
int count = context.Users.Count(u => u.IsActive);

// Asynchronous
int count = await context.Users.CountAsync(u => u.IsActive);

B. Any (Check Existence)

Translates to EXISTS in SQL. Very fast.

// Synchronous
bool exists = context.Users.Any(u => u.Email == "test@test.com");

// Asynchronous
bool exists = await context.Users.AnyAsync(u => u.Email == "test@test.com");

C. Sum / Min / Max / Average

// Synchronous
decimal total = context.Orders.Sum(o => o.TotalAmount);
decimal max = context.Orders.Max(o => o.TotalAmount);

// Asynchronous
decimal total = await context.Orders.SumAsync(o => o.TotalAmount);
decimal max = await context.Orders.MaxAsync(o => o.TotalAmount);

---

6. Loading Related Data (Joins)

A. Eager Loading (Include)

Loads related entities in the initial query.

var query = context.Blogs
    .Include(b => b.Posts)             // Level 1
    .Include(b => b.Owner)             // Level 1
        .ThenInclude(o => o.Address);  // Level 2 (Nested)

// Execution
var blogs = await query.ToListAsync();

B. Explicit Loading

Loads data for an entity already in memory.

var blog = await context.Blogs.FindAsync(1);

// Synchronous Load
context.Entry(blog).Collection(b => b.Posts).Load();

// Asynchronous Load
await context.Entry(blog).Collection(b => b.Posts).LoadAsync();

---

7. Grouping (GroupBy)

Used to group records by a key. Note that strict SQL grouping rules apply (you can only select the Key or an Aggregate of the group).

var query = context.Users
    .GroupBy(u => u.Role)
    .Select(g => new 
    {
        Role = g.Key,
        UserCount = g.Count(),
        AverageAge = g.Average(u => u.Age)
    });

// Execution
var stats = await query.ToListAsync();

---

8. Pagination (Skip / Take)

Used for paging grids. You must use OrderBy before Skip.

int pageNumber = 2;
int pageSize = 10;

var query = context.Products
    .OrderBy(p => p.Name)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize);

// Execution
var pageData = await query.ToListAsync();

---

9. Advanced & Performance Scenarios

A. No Tracking (AsNoTracking)

Disables change tracking for read-only queries (lower memory, faster execution).

// Standard
var users = await context.Users.AsNoTracking().ToListAsync();

// With Identity Resolution (prevents duplicates in complex graphs)
var users = await context.Users.AsNoTrackingWithIdentityResolution().ToListAsync();

B. Split Queries (AsSplitQuery)

By default, huge Include chains create one massive SQL JOIN (Cartesian Explosion). This splits them into separate SQL queries.

var data = await context.Blogs
    .Include(b => b.Posts)
    .AsSplitQuery()
    .ToListAsync();

C. Raw SQL (FromSql)

When LINQ cannot generate the specific SQL you need.

string status = "Active";

// Synchronous (Iteration)
var users = context.Users.FromSqlRaw("SELECT * FROM Users WHERE Status = {0}", status).ToList();

// Asynchronous
var users = await context.Users.FromSqlRaw("SELECT * FROM Users WHERE Status = {0}", status).ToListAsync();

---

10. Cheat Sheet: Sync vs Async