In this Video, I am going to discuss the Differences between IEnumerable and IQueryable in C# with an example. Please read our previous Video before proceeding to this Video where we discussed the basics of IEnumerable and IQueryable in C# with some examples.
What is Differences between IEnumerable and IQueryable in C#?. The Complete Microsoft .Net 6 LINQ Developer Course 2023 [Videos].
The IEnumerable and IQueryable are used to hold a collection of data and also used to perform data manipulation operations such as filtering, Ordering, Grouping, etc. based on the business requirements. Here in this Video, we will see the difference between IEnumerable and IQueryable with examples.
Here in this demo, we will create a console application that will retrieve the data from the SQL Server database using Entity Framework database first approach. We are going to fetch the following Student information from the Student table.
Please use the below SQL Script to create and populate the Student table with the required test data.
Once you created the Student table with the required test data then create a new Console application. Once you create the Console application then add the ADO.NET Entity Data Model Database approach.
Example: Using IEnumerable
Let us modify the Program class as shown below.
Here we create the LINQ Query using IEnumerable. Please use SQL Profiler to log the SQL Script. Now run the application and you will see the following SQL Script is generated and executed.
As shown in the above SQL Script, it will not use the TOP clause. So here it will fetch the data from SQL Server to in-memory and then it will filter the data.
Example: Using IQueryable
Let us modify the Program class as shown below to use IQueryable.
Once you run the application, it will create the following SQL Script.
As you can see it includes the TOP clause in the SQL Script and then fetches the data from the database. With this keep in mind let us discuss the differences between IEnumerable and IQueryable.
Differences between IEnumerable and IQueryable in C#:
- IEnumerable is an interface that is available in the System.Collections namespace.
- While querying the data from the database, the IEnumerable executes the “select statement” on the server-side (i.e. on the database), loads data into memory on the client-side, and then only applied the filters on the retrieved data.
- So you need to use the IEnumerable when you need to query the data from in-memory collections like List, Array, and so on.
- The IEnumerable is mostly used for LINQ to Object and LINQ to XML queries.
- The IEnumerable collection is of type forward only. That means it can only move in forward, it cant move backward and between the items.
- IEnumerable supports deferred execution.
- It doesnt support custom queries.
- The IEnumerable doesnt support lazy loading. Hence, it is not suitable for paging like scenarios.
- The IQueryable is an interface that exists in the System.Linq Namespace.
- While querying the data from a database, the IQueryable executes the “select query” with the applied filter on the server-side i.e. on the database, and then retrieves data.
- So you need to use the IQueryable when you want to query the data from out-memory such as remote database, service, etc.
- IQueryable is mostly used for LINQ to SQL and LINQ to Entities queries.
- The collection of type IQueryable can move only forward, it cant move backward and between the items.
- IQueryable supports deferred execution.
- It also supports custom queries using CreateQuery and Executes methods.
- IQueryable supports lazy loading and hence it is suitable for paging like scenarios.
If you go to the definition of where method, then you will see that it is implemented as an extension method of IQueryable interface as shown below.