What is LINQ, and what is deferred execution? How do you page results with it?

Say it out loud, then check

The story

1. Before

   UDC Finance's collections team works through overdue contracts every morning on a screen that shows 20 at a time. The first version loaded all contracts with ToList(), then picked the overdue ones and the page in C#.

2. Why it had to change

   That was fine with 2,000 contracts in test. In production there were 20,000, so every page click pulled 20,000 rows across the network to show 20. The screen took seconds, and memory jumped on each click.

3. How it works now

   LINQ queries are deferred: Where, OrderBy, Skip and Take only describe the query. Built on EF Core's IQueryable, the whole description becomes one SQL statement when ToListAsync runs it. So the fix was to move ToList to the end: filter, order and page in the database, bring back 20 rows. The diagram walks it.

4. What happens next

   One query per page, 20 rows each. The same deferred behaviour has one trap left: enumerate a query twice and it runs twice, so the result is turned into a list once and reused.

5. What we gained

   The database does the filtering it's built for, the API moves 20 rows instead of 20,000, and the code reads like the question the user asked.

In the lending API

The whole query is built first and runs once, at ToListAsync, as one SQL statement.

Only the key lines are shown; Deep walks the whole file.

The code, file by file

The UDC Finance collections screen in the lending API: overdue contracts, oldest first, a page at a time. Tap Next to walk the diagram with the code.

What they ask next

The follow-ups interviews really ask. Answer out loud, then tap to check.

• ReportedFirst, FirstOrDefault, Single, SingleOrDefault: what is the difference?

  First returns the first match and throws if there's none. Single expects exactly one and throws if there are none or more than one. FirstOrDefault and SingleOrDefault return null (or the type's default) when nothing matches, but SingleOrDefault still throws on two or more. Use Single on a unique key so duplicates fail loudly; use First with an OrderBy when you want "the latest" or "the top" one.

  // 0 matches: First throws,
  //   FirstOrDefault gives null
  // 2 matches: First returns one,
  //   Single(OrDefault) throws

  Next questions, in Deep →Where this was reported
• InterviewerIEnumerable vs IQueryable: what is the difference?

  IEnumerable<T> runs LINQ in memory, in .NET, over data you already have. IQueryable<T> builds an expression tree, a description of the query as data, that a provider like EF Core translates to SQL and runs in the database. Once a query becomes IEnumerable (with AsEnumerable or ToList), every step after runs in memory, on everything loaded so far.

  Next questions, in Deep →The incident it caused →Microsoft Learn: LINQ to Entities; the most common LINQ follow-up

Follow-up tree

Answer, then open the next level. That is the order an interviewer digs in.

Reporteda candidate was asked Intervieweran interviewer asks it

• ReportedFirst, FirstOrDefault, Single, SingleOrDefault: what is the difference?

  First returns the first match and throws if there's none. Single expects exactly one and throws if there are none or more than one. FirstOrDefault and SingleOrDefault return null (or the type's default) when nothing matches, but SingleOrDefault still throws on two or more. Use Single on a unique key so duplicates fail loudly; use First with an OrderBy when you want "the latest" or "the top" one.

  // 0 matches: First throws,
  //   FirstOrDefault gives null
  // 2 matches: First returns one,
  //   Single(OrDefault) throws

  Where this was reported
• InterviewerIEnumerable vs IQueryable: what is the difference?

  IEnumerable<T> runs LINQ in memory, in .NET, over data you already have. IQueryable<T> builds an expression tree, a description of the query as data, that a provider like EF Core translates to SQL and runs in the database. Once a query becomes IEnumerable (with AsEnumerable or ToList), every step after runs in memory, on everything loaded so far.

  The incident it caused →Microsoft Learn: LINQ to Entities; the most common LINQ follow-up
• ReportedHow does GroupBy work, and does it work with EF Core?

  In memory, GroupBy puts items with the same key into groups, each an IGrouping<TKey, T> with a Key and its items. With EF Core it translates to SQL GROUP BY when you follow it with an aggregate, like Select(g => new { g.Key, Total = g.Sum(p => p.Amount) }). A GroupBy as the last step also works since EF Core 7, but it brings back every row and builds the groups in .NET; aggregate in the query to get a real SQL GROUP BY.

  payments
    .GroupBy(p => p.ContractId)
    .Select(g => new {
      g.Key,
      Total = g.Sum(p => p.Amount) });

  Reported wording: how does GroupBy work in LINQ?
• Find vs FirstOrDefault in EF Core?

  Find looks up by primary key and first checks EF's change tracker, the list of entities this request's database context has already loaded. If it's there, no database call. FirstOrDefault always queries the database, and can match on any condition.

• InterviewerSkip and Take are slow on page 5,000. Why, and what do you do?

  OFFSET still makes the database read and throw away every skipped row, so deep pages get slower. Keyset pagination fixes it: remember the last row's sort key and ask for rows after it, Where(c => c.Id > lastId).OrderBy(c => c.Id).Take(20), which an index (the database's sorted lookup on that column) answers directly. The cost: you can only go next or previous, not jump to page 5,000.

  Microsoft Learn: offset vs keyset pagination

Production bugs, and how to tell them

Each one: what you see, why, the fix in code, and the line to say.

• Try first: predict how many overdue contracts it prints

  var overdue = contracts
      .Where(c => c.DaysOverdue > 30);
  
  contracts.Add(new Contract("C-9", 45));
  
  Console.WriteLine(overdue.Count());
  
  // contracts held 2 overdue before the Add.
  // Does it print 2 or 3? Decide, then open the answer.

  Cause

  It prints 3. Where didn't run when overdue was declared; it only stored the condition. Count() enumerates the list as it is at that moment, which now includes C-9. That's deferred execution: the query reads the data when it runs, not when it's written.

  Fix

  If you want a snapshot, materialise it when you mean to: var overdue = contracts.Where(...).ToList(); runs the filter once, right there, and later changes to contracts don't affect it. And if you need the result twice, the ToList also stops it running twice.

  BeforeAfter

  1var contracts = new List<Contract>2{3 new Contract("C-1", 10),4 new Contract("C-2", 40),5 new Contract("C-3", 90)6};7var overdue = contracts8 .Where(c => c.DaysOverdue > 30);9contracts.Add(new Contract("C-9", 45));10Console.WriteLine(overdue.Count()); // 3

  Before · Program.cs · line 7 · 1 of 2Stores the query. Nothing is filtered yet.

  BackNext line

  Say it

  Deferred execution: the query is a description and runs at enumeration. Count ran after the Add, so it saw three. If I need a fixed result or I'll use it more than once, I call ToList at the point I mean, which also avoids running the query again on every enumeration.

Traps

• Don't sayLINQ runs when you write the query.SayIt runs when you enumerate it: foreach, ToList, Count, First. Each enumeration runs it again.
• Don't sayAdd AsEnumerable() when EF says it can't translate a method.SayThat moves every later step into memory and loads the whole table. Filter in SQL first, then switch to memory for the small remainder.
• Don't saySingleOrDefault never throws.SayIt returns the default on no match, but throws if two or more rows match.
• Don't saySkip and Take are enough for paging.SayWithout OrderBy the order isn't guaranteed, so pages can repeat or skip rows.

Traps

What should you say instead? Tap to check.

• Don't sayLINQ runs when you write the query.

  Say instead

  It runs when you enumerate it: foreach, ToList, Count, First. Each enumeration runs it again.

• Don't sayAdd AsEnumerable() when EF says it can't translate a method.

  Say instead

  That moves every later step into memory and loads the whole table. Filter in SQL first, then switch to memory for the small remainder.

• Don't saySingleOrDefault never throws.

  Say instead

  It returns the default on no match, but throws if two or more rows match.

• Don't saySkip and Take are enough for paging.

  Say instead

  Without OrderBy the order isn't guaranteed, so pages can repeat or skip rows.