# 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](http://ASP.NET) Core).
    

---

## 2\. Retrieving Lists

Fetching multiple rows from a table.

### A. Fetch All Records

Retrieves every row in the table.

```csharp
// 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.

```csharp
// 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`.

```csharp
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).

| **Method** | **Behavior** | **Throws Exception if...** |
| --- | --- | --- |
| **Find** | Checks Local Memory (ChangeTracker) first, then DB. | N/A (Returns `null`) |
| **First** | `TOP(1)`. Gets the first match. | No match found. |
| **FirstOrDefault** | `TOP(1)`. Safe; returns null if empty. | N/A (Returns `null`) |
| **Single** | `TOP(2)`. Expects exactly 1 match. | No match OR &gt;1 match. |
| **SingleOrDefault** | `TOP(2)`. Expects 0 or 1 match. | \&gt;1 match found. |

### A. Find (By Primary Key)

```csharp
// 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.

```csharp
// 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).

```csharp
// 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

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

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

### B. Select to DTO (Data Transfer Object)

```csharp
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

```csharp
// 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.

```csharp
// 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

```csharp
// 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.

```csharp
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.

```csharp
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).

```csharp
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`.

```csharp
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).

```csharp
// 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.

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

### C. Raw SQL (`FromSql`)

When LINQ cannot generate the specific SQL you need.

```csharp
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

| **Operation** | **Synchronous** | **Asynchronous** |
| --- | --- | --- |
| **List** | `.ToList()` | `.ToListAsync()` |
| **Array** | `.ToArray()` | `.ToArrayAsync()` |
| **First** | `.First()` / `.FirstOrDefault()` | `.FirstAsync()` / `.FirstOrDefaultAsync()` |
| **Single** | `.Single()` / `.SingleOrDefault()` | `.SingleAsync()` / `.SingleOrDefaultAsync()` |
| **Count** | `.Count()` | `.CountAsync()` |
| **Any** | `.Any()` | `.AnyAsync()` |
| **All** | `.All()` | `.AllAsync()` |
| **Sum** | `.Sum()` | `.SumAsync()` |
| **Find** | `.Find(id)` | `.FindAsync(id)` |
| **Save** | `.SaveChanges()` | `.SaveChangesAsync()` |