LINQ providers: IQueryable and IEnumerable

The LINQ architecture is extensible through providers. A provider translates LINQ queries into the query language of the target data source. Examples:

• LINQ to Objects queries in-memory collections like List<T> or arrays.
• LINQ to XML queries XML documents.
• LINQ to Entities (EF Core) queries relational databases through Entity Framework Core. The EF Core provider translates IQueryable<T> LINQ queries into SQL.

IQueryable<T> versus IEnumerable<T>

When you work with databases through EF Core, the difference between these two is critical for performance.

• IQueryable<T> represents a query that can be translated into an expression tree and executed remotely, for example as SQL on a database server. Operations chained on an IQueryable<T> modify the expression tree, and are typically translated into the remote query.
• IEnumerable<T> represents a sequence that can be iterated in memory. If you convert an IQueryable<T> to an IEnumerable<T>, by calling .AsEnumerable() or implicitly when certain LINQ methods that only exist for IEnumerable<T> are used, later LINQ operations run in memory, on the data after it was fetched from the database.

The pitfall

Switching from IQueryable<T> to IEnumerable<T> too early, for example calling .ToList() early in a query chain, can fetch far more data from the database than necessary. The filtering or sorting then happens in application memory, which is usually much less efficient.

// BAD: pulls the ENTIRE table into memory first, then filters in C#var people = dbContext.People.ToList().Where(p => p.Age > 18); // GOOD: the filter becomes SQL, and only the matching rows come backvar people = dbContext.People.Where(p => p.Age > 18).ToList();

In the bad version, .ToList() runs immediately, dragging every row from the database into memory, and then Where filters that huge in-memory list. In the good version, Where is still IQueryable, so it gets baked into the SQL query itself (SELECT * FROM People WHERE Age > 18), and the database does the filtering before anything crosses the network. What ToList() is, is in its own noteToList and what it returnsToList runs the query and returns a List, which offers more than IEnumerable.Opens in a new tab.

Build the IQueryable<T> expression as completely as possible before materializing it, so the database does the heavy lifting.

Side by side

Both use the same lambda syntax on the surface, which is the point of LINQ’s design. What happens underneath is completely different.