Understanding IEnumerable VS IQueryable in C#

Last updated on September 5th, 2023 at 02:08 pm

The main difference between IEnumerable and IQueryable is that IEnumerable is mainly used for querying in-memory collections such as Array, List, etc. On the other hand, IQueryable is used for querying out-of-memory collections or external data sources like databases or services.

In this article, we will explore the differences between IEnumerable and IQueryable in C#.

What is IEnumerable?

IEnumerable is an interface that defines a standard way to iterate through a collection of objects. It provides a single method called GetEnumerator() that returns an IEnumerator interface, which allows us to traverse through the collection one element at a time using foreach loop.

IEnumerable is a read-only interface, meaning we can only retrieve data from the collection but cannot modify it. If we need to modify the collection, we must convert it to a list or an array.

IEnumerable Interface exists in System.Collections Namespace.

Example of IEnumerable Interface

Let’s consider a simple example where we have a collection of integers.

IEnumerable<int> numbers = new List<int> { 10, 20, 30, 40, 50 };

We can use the foreach loop to iterate through the collection and print each element.

foreach (int number in numbers)
{
    Console.Write($" {number} ");
}

We will get the following output:

10 20 30 40 50

Key characteristics of IEnumerable Interface:

The primary purpose of IEnumerable is to enable the iteration (traversal) of elements within a collection one at a time.

Key characteristics of IEnumerable include:

1. Namespace and Purpose: IEnumerable is part of the System.Collections namespace, which is essential for working with collections in C#.

2. Forward-Only Movement: When working with IEnumerable, it’s important to note that it allows forward traversal only,  meaning you can access each item sequentially, but you cannot go backward or jump between items within a collection.

3. Ideal for In-Memory Collections: IEnumerable helps when you need to query data from in-memory collections like Lists, Arrays, and other data structures. It is commonly used for filtering, sorting, and selecting data from these in-memory collections.

4. Database Query Behavior: In the context of database operations, IEnumerable performs a series of steps. First, it executes a select query on the server side, then loads the resulting data in memory on the client side, and finally filters the data as needed.

5. LINQ Compatibility: IEnumerable is well-suited for working with LINQ (Language Integrated Query) when dealing with Object and XML data sources. It provides seamless support for LINQ to Object and LINQ to XML queries.

6. Deferred Execution: One of the standout features of IEnumerable is its support for deferred execution. This means that the execution of operations is postponed until you actually enumerate through the collection, which can lead to more efficient query execution.

7. Limitations on Custom Queries: It’s worth noting that IEnumerable does not support custom query operations directly. Custom queries may require additional handling.

8. No Lazy Loading Support: IEnumerable lacks support for lazy loading, making it less suitable for scenarios like paging where you want to load data incrementally as needed.

9. Extension Methods and Functional Objects: When working with IEnumerable, you can take advantage of extension methods that enable the use of functional objects, allowing you to perform a wide range of operations on your collections.

Example2: IEnumerable Interface

using System;
using System.Collections.Generic;
using System.Linq;

class Program
{
    static void Main()
    {
        // Create a list of integers as an in-memory collection.
        List<int> numbers = new List<int> { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };

        // Use IEnumerable to query the collection.
        IEnumerable<int> evenNumbers = numbers.Where(n => n % 2 == 0);

        // The actual query is not executed until enumeration.
        // It's deferred until needed for efficiency.

        Console.WriteLine("Even Numbers:");
        foreach (int number in evenNumbers)
        {
            Console.Write($"{number} ");
        }

        // Here, the query is executed as we enumerate the results.
        // This is called deferred execution.

        Console.ReadLine();
    }
}

Output:

Even Numbers:
2 4 6 8 10

What is IQueryable?

IQueryable is an interface that inherits from IEnumerable and provides additional functionality for querying data from a data source. IQueryable is designed to work with remote data sources, such as databases, where the query is executed on the server side. It is available in System.Linq Namespace.

IQueryable provides a set of extension methods, such as Where(), OrderBy(), and Select(), that allow us to build complex queries that are translated into SQL queries and executed on the server side.

Example of IQueryable

Let’s consider a simple example: a database table called “Customers” containing customer information such as name, email, age, and phone number.

IQueryable<Customer> customers = dbContext.Customers;

We can use the Where() method to filter the customers based on a specific condition.

IQueryable<Customer> filteredCustomers = customers.Where(c => c.Age > 30);

This will generate the following SQL query:

SELECT *
FROM Customers
WHERE Age > 30

IEnumerable vs IQueryable in C#:

The following are the differences between IEnumerable and IQueryable in C#:

Feature	IEnumerable	IQueryable
Namespace:	IEnumerable Interface exists in System.Collections Namespace.	IQueryable Interface exists in System.Linq Namespace.
Query Execution:	IEnumerable is designed to work with in-memory collections such as Array, List and so on.	IQuerable is designed to work with remote data sources such as databases
Type of Execution:	IEnumerable Interface uses deferred execution, which means that the query is executed only when the result is enumerated.	IQuerable, On the other hand, uses immediate execution, which means that the query is executed as soon as it is defined.
Design:	Simple to use and provides a straightforward way to iterate through collections	More complex to use and provides a more extensive set of extension methods for building complex queries
Performance:	IEnumerable may be faster when querying data from an in-memory collection.	IQuerable may be faster when querying data from a remote data source such as databases.
Data Size:	IEnumerable is suitable for small collections because it retrieves all the data from the collection at once.	IQuerable is suitable for large collections because it allows you to retrieve only the data that you need.
Modifiability:	IEnumerable is readonly, which means that you can only retrieve data from the collection	IQuerable is modifiable, which means that you can modify the data source directly.
Lazy loading:	The IEnumerable Interface doesn’t support lazy loading. Hence, it is not ideal for paging-like scenarios.	On the other hand, IQueryable Interface supports lazy loading, and hence it is suitable for paging-like scenarios.
Suitable for:	IEnumerable Interface is mainly used for LINQ to XML and LINQ to Object queries.	IQueryable Interface is mostly used for LINQ to SQL and LINQ to Entities queries.
When to use:	IEnumerable can be used when querying data from in-memory collection such as Array or List collection.	IQueryable can be used when querying data from out-memory such as databases or services.
Custom Query:	IEnumerable does not support custom queries.	IQueryable supports custom queries using CreateQuery() and Execute() methods.

Which Interface is better, IEnumerable or IQueryable?

It’s not a matter of one Interface being better than the other but rather about which Interface is better suited to the specific use case. IEnumerable is ideal for in-memory collections and is simpler to use. 

At the same time, IQueryable is better suited to querying large data sets from remote sources, as it enables server-side processing and returns only the required data. So, the choice of Interface depends on the particular scenario and the developer’s requirements.

Performance-wise comparison between IEnumerable and IQueryable

Here is a comparison between IEnumerable and IQueryable when fetching and filtering records from external sources such as a databases.

Employees table:

EmployeeID	EmployeeName	Department
1	Shekh Ali	IT
2	Amit Kumar	HR
3	Bob Johnson	Sales
4	Sarah Lee	Sales
5	Pooja Singh	IT
6	Kim Lee	Marketing

Note: This is just an example table with a few records to demonstrate the concept. In a real-world scenario, the table may contain many more records and additional columns with various data types.

01. IEnumerable 

When fetching records from a database, IEnumerable will retrieve all records from the table and then filter records in memory. It means that if the table contains a large number of records, the query may be slow and consume a lot of memory. 

02. IQueryable 

On the other hand, IQueryable will filter records at the database level, which means only the relevant records will be retrieved, resulting in faster performance and less memory usage.

Here is an example code using IEnumerable to fetch and filter records from the Employees table:

// Fetch all records from the table
var employees = dbContext.Employees.ToList(); 

// Filter the records in-memory
var filteredEmployees = employees.Where(e => e.Department == "Sales"); 

In this example, all records from the Employees table are fetched into memory using the ToList() method, and then the Where() method is used to filter the records in memory.

Here is an example code using IQueryable to fetch and filter records from the Employees table:

// Filter the records at the database level and fetch only the relevant ones
var employees = dbContext.Employees.Where(e => e.Department == "Sales").ToList(); 

In this example, the Where() method filters the records at the database level, and only the relevant records are retrieved using the ToList() method.

As you can see, using IQueryable can significantly improve the performance and memory usage of the query when working with large datasets. However, it’s essential to note that using IQueryable requires some knowledge of how the database engine works and the query may need to be optimized to get the best performance.

Best Practices 

When working with either IEnumerable or IQueryable, there are some best practices to remember. Here are a few:

• Use IEnumerable when working with small datasets or performing complex operations in memory.
• Use IQueryable when working with large datasets or when you need to perform filtering and sorting at the database level.
• Avoid calling ToList() or ToArray() on IQueryable too early, as this will force the query to execute and load all the data into memory. Instead, try to defer the execution until the data is needed.
• Be careful when using complex queries that involve joins and multiple tables, as these can be challenging to optimize and may result in poor performance.

By following these best practices, you can ensure that your queries are efficient and performant, regardless of whether you are using IEnumerable or IQueryable.

References: MSDN: IEnumerable Interface, IQueryable Interface

FAQs

Recommended Articles:

• Interface in C# with examples
• IEnumerable Interface with code examples
• Array Vs List in C#
• C# Collection with code examples
• DateTime Format In C# (with examples)
• C# Dependency Injection with example
• Singleton Design Pattern in C#: A Beginner’s Guide with Examples
• SOLID Design Principles in C#: A Complete Example
• Difference between interface and abstract class In C#
• C++ vs C#: Difference Between C# and C++
• C# Regex: Introduction to Regular Expressions in C# with Examples
• C# String Interpolation – Syntax, Examples, and Performance
• C# Stopwatch Explained – How to Measure Code Execution Time?
• C# String Vs StringBuilder
• C# Queue with examples