A .NET application using Entity Framework calls ToList() on the Customers set and then filters the resulting list with a LINQ Where clause for customers in one country. Database monitoring shows the full table being read on every request. What should be changed?
Select an answer to reveal the explanation.
Short Explanation
ToList() is the moment the ORM goes to the database, so anything after it happens in your app's memory, not in SQL. Put the Where first and the filter becomes part of the query the database runs. The trap is adding an index, because the SQL being sent has no WHERE clause for that index to help.
Full Explanation
With Entity Framework, a LINQ query against a DbSet is composed as an expression and translated into SQL only when it is executed, which happens when the results are materialized, for example by ToList(). Calling ToList() on the Customers set first sends SELECT over the entire table, loads every row into application memory, and only then applies the Where clause in .NET code. Reordering the query so Where runs on the DbSet before materialization lets the provider translate the filter into a SQL WHERE clause, so the database returns only the matching customers. This is why reviewing the SQL an ORM generates is a key part of measuring programming impact on database performance. Adding an index on country cannot help, because the generated SQL contains no predicate on country for the optimizer to use. Increasing the command timeout only allows the oversized read to finish; it does not reduce the rows read, transferred or held in memory. Reading customers one row at a time changes how results are consumed but still retrieves the full table, and adds overhead if each row becomes a separate round trip. Exam caveat: projecting only the needed columns with Select further reduces data transferred. Operational check: capture the SQL generated before and after the change and confirm the new statement includes a WHERE clause on country and returns far fewer rows.