IEnumerable<T> vs IQueryable<T>: what to choose in C#? Full breakdown

IEnumerable<T> vs IQueryable<T>: a duel that will decide the fate of your application

Have you ever wondered why your database query is slow, even though there's barely any data? Or why after adding one line of code your application turns into a turtle? Most likely, you've encountered the classic dilemma: IEnumerable<T> or IQueryable<T>? These are not just two interfaces from the world of C# and LINQ. These are two different worlds, two approaches to working with data. One is for local collections, the other is for remote sources, such as databases. In this article, we won't just list the differences, but will break them down with real examples, so you never fall into the performance trap again.

Who is who: basic concepts

Let's start with the basics. IEnumerable<T> is an interface that represents a sequence of elements in memory. It is ideal for working with collections such as List<T>, Array, or Dictionary. When you use IEnumerable, all operations (filtering, sorting) are performed on the client side, i.e., in your application.

IQueryable<T> is a more advanced interface designed for working with external data sources (databases, web services). It does not store data, but builds an Expression Tree. This allows the LINQ provider (e.g., Entity Framework) to convert your C# code into an efficient SQL query and execute it on the server side.

The key phrase to remember: IEnumerable works in memory, IQueryable works on the data source side.

The main difference: where and when the logic is executed

Imagine you need to filter a list of 10,000 products that cost more than 1000 rubles and sort them by price.

Example 1: IEnumerable (filtering in memory)

// Suppose products is a List<Product> (IEnumerable)\nvar expensiveProducts = products\n    .Where(p => p.Price > 1000)\n    .OrderBy(p => p.Price)\n    .ToList();\n

What happens here? First, ALL 10,000 products are loaded into memory. Then C# applies the Where filter and OrderBy sorting in your application. If you have 10,000 records, this is not critical. But if you have 10 million — the application will crash or freeze.

Example 2: IQueryable (filtering on the database server)

// Suppose dbContext.Products is a DbSet<Product> (IQueryable)\nvar expensiveProducts = dbContext.Products\n    .Where(p => p.Price > 1000)\n    .OrderBy(p => p.Price)\n    .ToList();\n

Here's the magic: IQueryable does not load data immediately. Instead, it builds an SQL query: SELECT * FROM Products WHERE Price > 1000 ORDER BY Price. This query is sent to the database, and it returns only the filtered and sorted data. In memory, you get only what you need. The resource savings are enormous.

Practical example: the ToList() trap

One of the most common mistakes beginner developers make is prematurely calling ToList() or ToArray(). This turns IQueryable into IEnumerable.

Bad code (kills performance):

var allProducts = dbContext.Products.ToList(); // Load ALL data into memory!\nvar filteredProducts = allProducts.Where(p => p.Price > 1000).ToList();\n

In this example, we first load the entire Products table into memory, and then filter it. This is equivalent to unloading the entire warehouse into the parking lot and then searching through the pile for the right box.

Good code (optimal):