`IEnumerable` vs. `IQueryable`: The Key Difference

Interview Question: 'What's the difference between IEnumerable and IQueryable?'

This is arguably the most important LINQ interview question. Getting this wrong can mean you're writing extremely slow, inefficient database queries.

The simple answer:

• 'IEnumerable is for in-memory queries (LINQ-to-Objects). The query is executed locally by your C# application.
• IQueryable is for out-of-memory queries (LINQ-to-SQL). The query is translated into another language (like SQL) and executed remotely (e.g., on the database server).

Code Example: The Performance 'Gotcha'

Imagine you have a Users table with 10 million users. You are using Entity Framework (_db).

1. The IEnumerable (BAD) Way:

This is a catastrophic performance bug.

// _db.Users is IQueryable, but .AsEnumerable() casts it to IEnumerable
IEnumerable users = _db.Users.AsEnumerable();

// This .Where() clause is LINQ-to-Objects.
// It is NOT translated to SQL.
var admins = users.Where(u => u.IsAdmin == true).ToList();

// What happened?
// 1. You fetched ALL 10 MILLION USERS from the database.
// 2. You pulled them all into your server's memory.
// 3. Then, in your C# application, you filtered them down to the 5 admins.
// This will crash your server.

2. The IQueryable (GOOD) Way:

This is the correct, efficient way.

// _db.Users is IQueryable
IQueryable users = _db.Users;

// This .Where() clause is LINQ-to-SQL.
// It is translated into a SQL 'WHERE' clause.
var admins = users.Where(u => u.IsAdmin == true).ToList();

// What happened?
// 1. The .Where() clause builds an expression tree.
// 2. The .ToList() executes the query.
// 3. EF Core generates this SQL:
//    'SELECT * FROM Users WHERE IsAdmin = 1'
// 4. The database does the filtering. Only 5 ADMINS are sent to your server.
// This is instant and uses almost no memory.

The simple answer: 'IQueryable builds an expression tree that gets translated to SQL, so the database does the filtering. IEnumerable pulls all the data into memory first and then filters it. You always want to use IQueryable for as long as possible when querying a database.'

Deferred Execution in LINQ: What You Must Know

Interview Question: 'What is deferred execution in LINQ?'

This is a follow-up to the IQueryable question. It tests if you know when a LINQ query actually runs.

Answer: 'Deferred execution means that building a LINQ query does not execute it. The query is just a 'plan'. It is not executed until you 'materialize' or 'iterate' over the results.'

This is what allows IQueryable to work. You can chain 10 LINQ methods together, and it's all just building one big 'plan' (an expression tree). No database call is made.

When is the query 'executed'?

The query 'plan' is executed when you do one of two things:

1. Immediate Execution (Materialization): Call a method that demands a concrete result.
• .ToList()
• .ToArray()
• .ToDictionary()
• .Count()
• .First(), .FirstOrDefault()
• .Single(), .SingleOrDefault()
• .Any()
2. Implicit Execution (Iteration): Use the query in a foreach loop.

Code Example:

// Get our database context
var context = new MyDbContext();

// 1. Build the 'plan'.
// NO database query is run here. 'query' is just an IQueryable plan.
IQueryable query = context.Users
    .Where(u => u.IsActive)
    .OrderBy(u => u.LastName)
    .Select(u => u.Email);

// ... some time passes, more logic ...

// 2. Execute the 'plan'.
// NOW, the full SQL query is generated and sent to the database.
// 'SELECT Email FROM Users WHERE IsActive = 1 ORDER BY LastName'
List emails = query.ToList();

// --- OR --- 

// 2. Execute the 'plan' via iteration.
// The 'foreach' loop triggers the execution.
foreach (string email in query) {
  Console.WriteLine(email);
}
// Be careful! If you loop this 'query' object twice,
// you will hit the database twice! This is a common bug.
// That's why .ToList() is often used to get the results once.

Top 5 LINQ Methods: Where, Select, GroupBy, OrderBy

Interview Question: 'Can you solve this coding problem...?'

Often, an interviewer won't ask about LINQ; they'll give you a problem where LINQ is the best solution. 'Given a list of Product objects, find the average price for each category, sorted by name.' You must know these methods.

public class Product {
  public int Id { get; set; }
  public string Name { get; set; }
  public string Category { get; set; }
  public decimal Price { get; set; }
}

var products = new List() {
  new Product { Id = 1, Name = "Apple", Category = "Fruit", Price = 1.0m },
  new Product { Id = 2, Name = "Carrot", Category = "Veg", Price = 0.5m },
  new Product { Id = 3, Name = "Banana", Category = "Fruit", Price = 0.8m },
  new Product { Id = 4, Name = "Broccoli", Category = "Veg", Price = 1.2m },
};

1. .Where() - To Filter

Use: Filters a sequence based on a condition (a predicate).
SQL: WHERE

// Get only the fruit
var fruits = products.Where(p => p.Category == "Fruit");
// Result: Apple, Banana

2. .Select() - To Transform

Use: Projects each element into a new form.
SQL: SELECT

// Get only the names of the products
var names = products.Select(p => p.Name);
// Result: "Apple", "Carrot", "Banana", "Broccoli"

3. .OrderBy() / .OrderByDescending() - To Sort

Use: Sorts the elements in ascending or descending order.
SQL: ORDER BY

// Get products sorted by price, cheapest first
var sorted = products.OrderBy(p => p.Price);
// Result: Carrot (0.5), Banana (0.8), Apple (1.0), Broccoli (1.2)

4. .GroupBy() - To Group

Use: Groups elements based on a key. This is very powerful.
SQL: GROUP BY

// Group products by their category
var groups = products.GroupBy(p => p.Category);

// 'groups' is now an IEnumerable>
// It has two groups: 'Fruit' and 'Veg'

foreach (var group in groups) {
  Console.WriteLine(group.Key); // 'Fruit', then 'Veg'
  foreach (var product in group) {
    Console.WriteLine(" - " + product.Name); // Apple, Banana... then Carrot, Broccoli
  }
}

5. .First(), .Single(), .Average(), .Sum() - To Aggregate

Use: Gets a single value from the sequence.

• .First(): Gets the first element. Throws an error if empty.
• .FirstOrDefault(): Gets the first element, or null (or default) if empty. (Safest!)
• .Single(): Gets the only element. Throws an error if not exactly one.
• .Average(), .Sum(), .Count(): Perform aggregations.

Putting It All Together (Solving the Interview Problem)

'Find the average price for each category, sorted by name.'

var categoryAverages = products
    .GroupBy(p => p.Category) // Group into 'Fruit' and 'Veg'
    .Select(group => new {
      CategoryName = group.Key,
      AveragePrice = group.Average(p => p.Price)
    })
    .OrderBy(g => g.CategoryName);

// Result:
// [ { CategoryName = "Fruit", AveragePrice = 0.9m },
//   { CategoryName = "Veg", AveragePrice = 0.85m } ]