Lazy Loading (1): Difference Between IEnumerable and IQueryable

Note: this article is published on 06/05/2023.

In this series of articles, we discuss Lazy Loading and related. There is no Lazy Loading Category on this site; we put the articles in the LINQ category because the behavior of Lazy Loading is based on or related to LINQ.

Lazy Loading (0): Overview
Lazy Loading (1): Difference Between IEnumerable and IQueryable --- this article
Lazy Loading (2): HTML
Lazy Loading (3): JavaScript
Lazy Loading (4): EntityFramework
Lazy Loading (5): Angular --- not done

A - Introduction

This article will discuss the major differences between interfaces of INumerable and IQueryable, such as

Execution Time Scale: deferred feature
Execution Space Scale: Client Side or Server Side, in Memory or in Database
Extension Method Supporting
Lazy Loading supporting --- not discussed

There are a lot of articles to discuss the same topic; besides listing the major features of the topic, we will concentrate on showing the results by a piece of running code.

The content of this article:

A - Introduction
B - Basic Features for IEnumerable and IQueryable
C - Features we discuss
D - Test Environment
E - Test by SQL Profiler:
- Time Scale: Deferred Feature
- Space Scale: Client Side vs. Server Side
F - Test by Debugging
G - Summary

B - Basic Features for IEnumerable and IQueryable

They are listed below [ref], where Bold are the differences:

IEnumerable

IEnumerable exists in System.Collections Namespace.
IEnumerable can move forward only over a collection; it can't move backward and between the items.
IEnumerable is best for querying data from in-memory collections like List, Array, etc.
While querying data from a database, IEnumerable executes a select query on the server side, loads data in-memory on a client-side, and then filters data.
IEnumerable is suitable for LINQ to Object and LINQ to XML queries.
IEnumerable supports deferred execution.
IEnumerable doesn't support custom queries.
IEnumerable doesn't support lazy loading. Hence not suitable for paging like scenarios.
Extension methods supported by IEnumerable takes functional objects.

IQueryable

IQueryable exists in System. Linq Namespace.
IQueryable can move forward only over a collection; it can't move backward and between the items.
IQueryable is best for querying data from out-memory (like remote Database, service) collections.
While querying data from a database, IQueryable executes the select query on the server side with all filters.
IQueryable is suitable for LINQ to SQL queries.
IQueryable supports deferred execution.
IQueryable supports custom queries using CreateQuery and Execute methods.
IQueryable support lazy loading. Hence it is suitable for paging like scenarios.
Extension methods supported by IQueryable take expression objects means expression tree.

Note:

lIQueryable is derived from IEnumerable; therefore, all features IEnumerable has, lIQueryable will have. On the other hand, some features IQueryable has, IEnumerable might not have.

The following covers the major features:

Source

C - Features we Discuss

The following features that we will examine and show in the rest of the article:

IEnumerable
1. IEnumerable is best for querying data from in-memory collections like List, Array, etc.
2. While querying data from a database, IEnumerable executes a select query on the server side, loads data in-memory on a client-side, and then filters data.
3. IEnumerable is suitable for LINQ to Object and LINQ to XML queries.
4. IEnumerable supports deferred execution.
5. Extension methods supported by IEnumerable takes functional objects.
IQueryable
1. IQueryable is best for querying data from out-memory (like remote Database, service) collections.
2. While querying data from a database, IQueryable executes the select query on the server side with all filters.
3. IQueryable is suitable for LINQ to SQL queries.
4. IQueryable supports deferred execution.
5. Extension methods supported by IQueryable take expression objects means expression tree.

Actually, No. 1, 2, and 3 say the same thing:

The Space (Location), the filter is running:

IEnumerable works in memory, step by step. Meaning, for example,

var q = from a in b
        where a > 5
        select a;

It creates a list out “b” depending on “where” then it creates another list for “select”. This is the behavior of IEnumerable or LINQ to Object and LINQ to XML.

While IQueryable or LINQ to SQL generates T-SQL at the backend (database) to be able to get the exactly requested data for us.

In short:

IEnumerable: Client side

Source

IQueryable: Server side

Source

No. 4 talks about the Time Scale: when the Query is running:

Deferred execution means that the evaluation of an expression is delayed until its realized value is actually required. Deferred execution can greatly improve performance when you have to manipulate large data collections, especially in programs that contain a series of chained queries or manipulations. In the best case, deferred execution enables only a single iteration through the source collection.

In short: the execution does not happen in the location of the definition of the execution but in the location where the execution result is used, which could be a demo, such as

Source

In both cases for INumerable or IQueryable, if you don't call a ToList() or ToArray() then the query will NOT be executed, while a ToList() or ToArray() is called the query will be executed and executed each time when called. So, say, you have an IQueryable and you fill 4 list boxes from it, then the query will run against the database 4 times.

We will show these space and time scale features below:

D - Test Environment

We use the ready Entity Framework Mode from Article Entity Framework (1), With .Net MVC, Code-First. If you want to run the code, following the instruction from that article, you can setup your local environment.

The data entity model is like this:

The created Table Movies in the Database is like this

The Entity Front end will show this MVC module:

E - Test by SQL Profiler

We use the Movie Entity Framework MVC module as our test app. The following code is the test code added into Index action from Line 26 to Line 39, where _context.Movie will bring the Movie entity in.

We define an IEnunerabke at Line 26 and an IQueryable at Line 27.

We will run the code to examine the time scale feature and the space scale feature (running location) below:

Time Scale: Deferred Feature

We use SQL profiler to examine the SQL traffic.

First, we make a breakpoint at Line 38 in the code above, run the code here, and we find out there is no SQL traffic at all in the SQL profiler.

That means although we have defined the queries three times previously before Line 38, the execution has not happened so far. --- The execution is deferred.

Space Scale: Client Side vs. Server Side

Run the code one step more to Line 39, now Line 38 has been executed:

The profiler shows SQL traffic:

The SQL Select Clause returns the whole table to the client side. Actually, it is the execution of Line 26:

SELECT [m].[ID], [m].[Genre], [m].[Price], [m].[ReleaseDate], [m].[Title]
FROM [Movies] AS [m]

Now, we run the code one step more to Line 42; Line 39 has been executed:

SQL Profiler shows the SQL traffic

The SQL Select Clause is Line 39 execution:

SELECT TOP(@__p_1) [t].[ID], [t].[Genre], [t].[Price], [t].[ReleaseDate], [t].[Title]
FROM (
    SELECT TOP(@__p_0) [m].[ID], [m].[Genre], [m].[Price], [m].[ReleaseDate], [m].[Title]
    FROM [Movies] AS [m]
    WHERE [m].[Genre] = N''Comedy''
) AS [t]
WHERE [t].[ID] = 3

This is the full SQL command:

exec sp_executesql N'SELECT TOP(@__p_1) [t].[ID], [t].[Genre], [t].[Price], [t].[ReleaseDate], [t].[Title]
FROM (
    SELECT TOP(@__p_0) [m].[ID], [m].[Genre], [m].[Price], [m].[ReleaseDate], [m].[Title]
    FROM [Movies] AS [m]
    WHERE [m].[Genre] = N''Comedy''
) AS [t]
WHERE [t].[ID] = 3',N'@__p_1 int,@__p_0 int',@__p_1=1,@__p_0=2

F - Test by Debugging

Besides SQL profiler, we can get detailed info from Visual Studio Debugger: Run to LIne 32:

Line 29: list (using IEnumerable) is in Enumerable type:

list = {System.Linq.Enumerable.EnumerablePartition<MvcCoreMovie1.Models.Movie>}

Line 30: query (using IQueryable) is in EntityQueryable type:

query = {Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable<MvcCoreMovie1.Models.Movie>}

We can also get the SQL Statement: query => DebugView => Query => Dropdown => Text Visualizer

Open Text Visualizer or anyone else; we can get the SQL Query at Line 30:

Repeat the same at Line 33; we get the SQL Query running in the server:

SQL Query at Line 36

G - Summary

This article shows:

Both INumerable and IQueryable are supported by Extension Methods --- such as .where(...);
In Time Scale: Both INumerable and IQueryable are supported by deferred execution --- execution happens in implementation (such as ToList(), ToArray(), foreach loop), not in the definition
In Space Scale,
- INumerable:
  - Supports: Client side execution in Memory
  - Suitable: LINQ to Object and LINQ to XML
- IQueryable
  - Supports: Server side execution in Database
  - Suitable: LINQ to Object and LINQ to SQL
Lazy Loading Supporting --- not discussed here
- INumerable: No
- IQueryable: Yes

References

What is the difference between IEnumerable and IQueryable in C#? --- tutorialspoint.com
What's the difference between IQueryable and IEnumerable --- stackoverflow.com
What is the difference between IQueryable<T> and IEnumerable<T>? --- stackoverflow.com
IEnumerable VS IQueryable --- dotnettricks.com
IEnumerable Vs. IQueryable In LINQ --- C# Corner
Deferred Execution of LINQ Query --- .tutorialsteacher.com
IEnumerable vs IQueryable --- samueleresca.net
Deferred Execution vs Immediate Execution in LINQ --- samirbehara.com
IQueryable vs. IEnumerable in .NET --- medium.com
IEnumerable and IQueryable in C# - Dot Net Tutorials
(3) Difference between IEnumerable and IList and List, IQueryable? | LinkedIn
Deferred execution and lazy evaluation - LINQ to XML - .NET | Microsoft Learn