Basic LINQ Queries & IQueryable Execution

The primary job of EF Core is taking C# Language Integrated Query (LINQ) syntax and translating it into highly optimized SQL. However, if you do not understand the difference between IQueryable and IEnumerable, you will accidentally download millions of database rows into your web server's RAM and crash the system.

1. Deferred Execution (The IQueryable Buffer)

A fundamental rule of LINQ to Entities: Writing a query does not execute the query.

// 1. This returns an IQueryable. The database HAS NOT BEEN TOUCHED yet.
// It is merely building the SQL string in memory.
IQueryable<User> query = _context.Users.Where(u => u.Age > 18);

// 2. We can dynamically attach more logic! Still no database hit.
query = query.OrderByDescending(u => u.CreatedDate);

// 3. ToListAsync() is an "Execution" command.
// ONLY NOW does it open a SQL connection and fire the SELECT query!
var adultUsers = await query.ToListAsync(); 

2. Filtering & Projection (SELECT)

Downloading columns you don't need is an I/O performance killer. Use the .Select() method to project exactly the data you need into a DTO.

var userDtos = await _context.Users
    .Where(u => u.IsActive == true) // Translates to SQL WHERE
    .Select(u => new UserDto        // Translates to SQL SELECT Name, Email
    {
        Name = u.Name,
        Email = u.Email
    })
    .ToListAsync(); // EXECUTED!

3. The Fatal IEnumerable Mistake

This is the most common reason senior developers fail code reviews.

// Danger: Calling AsEnumerable() forces execution instantly!
// EF Core generates: SELECT * FROM Users 
var targetUser = _context.Users
    .AsEnumerable() // Downloads all 1,000,000 users into C# memory!
    .Where(u => u.Id == 55) // The filtering happens in RAM, not in SQL.
    .FirstOrDefault();

// EF Core generates: SELECT * FROM Users WHERE Id = 55
// Downloads exactly 1 row over the network.
var targetUser = await _context.Users
    .Where(u => u.Id == 55) 
    .FirstOrDefaultAsync();

4. Interview Mastery

Q: "Is it faster to use '.Count()' or '.Any()' when checking if records exist in the database?"

Architect Answer: "You should ALWAYS use '.Any()' when checking for existence. If you execute 'await _context.Users.CountAsync(u => u.Role == \'Admin\') > 0', EF Core translates that to a 'SELECT COUNT(*) FROM Users WHERE Role=\'Admin\'' SQL query. The SQL engine is forced to scan the entire table and physically tally up every single admin. If you use 'await _context.Users.AnyAsync(u => u.Role == \'Admin\')', EF Core translates it using the 'EXISTS' operator. The millisecond SQL server finds the very first matching 'Admin' row, it aborts the scan immediately and returns 'true', resulting in massive performance gains on large datasets."