IEnumerable and IQueryable in C#

In this article, we will learn IEnumerable Vs IQueryable. Both IQueryable and IEnumerable are interfaces in .NET that represent a collection of objects. However, they have some important differences in how they work and when to use them. So, let’s begin.

What is IEnumerable?

IEnumerable lets you iterate through a collection of objects. It contains only one method: GetEnumerator(). This method returns an IEnumerator object which we use to loop through the collection. IEnumerable exists under System.Collections namespace.

The elements of the collection are not retrieved until they are accessed, which means that any filtering, sorting, or projection has to be done in memory. We call this “deferred execution.”

IEnumerable example with C# Code:

ProductEntities prdent = new ProductEntities();
IEnumerable<Product> prd = prdent.Products;
IEnumerable<Product> prdResult = prd.Where(x => x.ProductCategory == 1).ToList<Product>();

What is IQueryable?

IQueryable extends IEnumerable and provides a more powerful way to query data from a data source. It defines methods that enable the creation of complex queries that translate into a query language, such as SQL, and execute on the data source. IQueryable exists under System.Linq namespace.

IQueryable executes the query on the data source, which means that any filtering, sorting, or projection is performed on the data source rather than in memory. This is referred to as “deferred execution with server-side processing.”

IQueryable example with C# Code:

ProductEntities prdent = new ProductEntities();
IQueryable<Product> prd = prdent.Products;
IQueryable<Product> prdResult = prd.Where(x => x.ProductCategory == 1).ToList<Product>();

IQueryable vs IEnumerable: Realtime performance analysis

In this section, we will see a demonstration with real-time examples to understand the use of IQueryable and IEnumerable.

First, we will create an array of integers and then we will fetch even numbers to compare IQueryable vs IEnumerable using Benchmark.

Let’s see this code snippet :

public string QueryableResult()
{
    int[] numbers = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
    IQueryable<int> queryableNumbers = numbers.AsQueryable();
    var result = queryableNumbers.Where(n => n % 2 == 0);  

    foreach (var num in result)
    {
        Console.WriteLine(num);  
    }
    return null;
}
public string EnumerableResult()
{
    int[] numbers = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
    IEnumerable<int> numberEnumerable = numbers;
    var result = numberEnumerable.Where(n => n % 2 == 0); 
    foreach (int num in result)
    {
        Console.WriteLine(num);  
    }
    return null;
}

Here, we create two methods QueryableResult() and EnumerableResult(), so that we can run the benchmark to analyse the performance. Both methods perform and display the same result.

Based on benchmarking, we found the below report:


| Method               | Mean     | Error    | StdDev   | Rank | Gen0   | Gen1   | Allocated |
|--------------------- |---------:|---------:|---------:|-----:|-------:|-------:|----------:|
| IEnumerableBenchmark | 573.8 us |  9.65 us |  9.02 us |    1 |      - |      - |     119 B |
| IQueryableBenchmark  | 588.6 us | 11.59 us | 12.40 us |    2 | 4.8828 | 3.9063 |   11055 B |

Here, we see that there is a slight gap in time for IEnumerable and IQueryable to perform the same operation. However, we can see a good amount of allocated memory for these.

So, in this case, IEnumerable is the winner whether it is time or allocated memory.

Now, we will filter data using IEnumerable and IQueryable, but this time we will use SQL Server as our data source.

We have a list of products with prices. So, we are trying to get the list of products whose prices are less than 5000.

Let’s look at the code snippet:

public string QueryableResultDB()
{
    using (var db = new ProductDBContext())
    {
        IQueryable<Product> products = db.Products.AsQueryable().Where(p => p.Price < 5000);
        products = products.Take<Product>(5);
        foreach (Product p in products)
        {
            Console.WriteLine("{0} {1}", p.Id, p.ProductName);
        }
    }
    return null;
}
public string EnumerableResultDB()
{
    using (var db = new ProductDBContext())
    {
        IEnumerable<Product> products = db.Products.Where(p => p.Price < 5000);
        products = products.Take<Product>(5);
        foreach (Product p in products)
        {
            Console.WriteLine("{0} {1}", p.Id, p.ProductName);
        }
    }
    return null;
}

Here, we are using two methods QueryableResultDB() and EnumerableResultDB(). First, we fetch the list of products using LINQ, finally, we print the values using the foreach loop.

Now, we will run and see the output:

| Method               | Mean       | Error    | StdDev   | Median     | Rank | Gen0    | Allocated |
|--------------------- |-----------:|---------:|---------:|-----------:|-----:|--------:|----------:|
| IQueryableBenchmark  |   695.3 us | 13.88 us | 23.19 us |   696.6 us |    1 | 46.8750 |  99.48 KB |
| IEnumerableBenchmark | 1,128.4 us | 22.50 us | 56.87 us | 1,103.8 us |    2 | 44.9219 |   93.4 KB |

Here, we can see a good improvement in the IQueryable benchmark in the context of execution time (Mean and Median value).

So, finally, when it comes to in-memory data, IEnumerable is a good choice, however, if the data source is RDBMS, in our case SQL Server then IQueryable is the better choice because it presents filtered records.

In other words, IQueryable is a good choice for LINQ to SQL Queries.

The below image shows how IQueryable and IEnumerable work while fetching data from RDBMS.

IQueryable vs IEnumerableable

Note: For benchmark analysis, we can use the BenchmarkDotNet package.

In the below video, you may see the major difference between IQueryable and IEnumerable while working with an RDBMS-like SQL server.

Conclusion

In this article, we learnt about IEnumerable and IQueryable. In general cases, we should use IEnumerable when working with in-memory data collection, and IQueryable when querying a data source such as SQL Server.

It’s also worth noting that IQueryable is more powerful but also has a higher overhead compared to IEnumerable, so it’s important to choose the appropriate interface for your scenario.

To summarize, when querying data from in-memory collections like List and Array IEnumerable works best. On the other side, IQueryable is suitable for querying data from a remote database.

Keep following: SharePointCafe.NET

Leave a Comment

RSS
YouTube
YouTube
Instagram