Loading


Microsoft .Net 6 LINQ

  • Home
  • Microsoft .Net 6 LINQ
  • What is Differences between IEnumerable and IQueryable in C#?. The Complete Microsoft .Net 6 LINQ Developer Course 2024.
Microsoft .Net 6 LINQ

What is Differences between IEnumerable and IQueryable in C#?. The Complete Microsoft .Net 6 LINQ Developer Course 2023 [Videos].

By admin / 14-Feb-2022 / in Blog, Microsoft .Net 6 LINQ / 455 Comments / 38 Likes / Share This Post

In this article, I am going to discuss the Differences between IEnumerable and IQueryable in C# with an example. Please read our previous article before proceeding to this article where we discussed the basics of IEnumerable and IQueryable in C# with some examples.

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.

Differences between IEnumerable and IQueryable in C#

Example:

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.

Differences between IEnumerable and IQueryable in C#

Please use the below SQL Script to create and populate the Student table with the required test data.

-- Create the required Student table
CREATE TABLE Student
(
ID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Gender VARCHAR(50)
)
GO
-- Insert the required test data




GO

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.

using System;
using System.Collections.Generic;
using System.Linq;
namespace LINQDemo
{
class Program
{
static void Main(string[] args)
{
StudentDBContext dBContext = new StudentDBContext();
IEnumerable<Student> listStudents = dBContext.Students.Where(x => x.Gender == "Male");
listStudents = listStudents.Take(2);
foreach(var std in listStudents)
{
Console.WriteLine(std.FirstName + " " + std.LastName);
}
Console.ReadKey();
}
}
}

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.

SELECT
[Extent1].[ID] AS [ID],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Gender] AS [Gender]
FROM [dbo].[Student] AS [Extent1]
WHERE "Male" = [Extent1].[Gender]

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.

using System;
using System.Linq;
namespace LINQDemo
{
class Program
{
static void Main(string[] args)
{
StudentDBContext dBContext = new StudentDBContext();
IQueryable<Student> listStudents = dBContext.Students
.AsQueryable()
.Where(x => x.Gender == "Male");
listStudents = listStudents.Take(2);
foreach(var std in listStudents)
{
Console.WriteLine(std.FirstName + " " + std.LastName);
}
Console.ReadKey();
}
}
}

Once you run the application, it will create the following SQL Script.

SELECT TOP (2)
[Extent1].[ID] AS [ID],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Gender] AS [Gender]
FROM [dbo].[Student] AS [Extent1]
WHERE "Male" = [Extent1].[Gender]

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:
  1. IEnumerable is an interface that is available in the System.Collections namespace.
  2. 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.
  3. So you need to use the IEnumerable when you need to query the data from in-memory collections like List, Array, and so on.
  4. The IEnumerable is mostly used for LINQ to Object and LINQ to XML queries.
  5. The IEnumerable collection is of type forward only. That means it can only move in forward, it cant move backward and between the items.
  6. IEnumerable supports deferred execution.
  7. It doesnt support custom queries.
  8. The IEnumerable doesnt support lazy loading. Hence, it is not suitable for paging like scenarios.
IQueryable:
  1. The IQueryable is an interface that exists in the System.Linq Namespace.
  2. 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.
  3. So you need to use the IQueryable when you want to query the data from out-memory such as remote database, service, etc.
  4. IQueryable is mostly used for LINQ to SQL and LINQ to Entities queries.
  5. The collection of type IQueryable can move only forward, it cant move backward and between the items.
  6. IQueryable supports deferred execution.
  7. It also supports custom queries using CreateQuery and Executes methods.
  8. 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.

Read more
See All

Comments (455 Comments)

Submit Your Comment

See All Posts

Related Posts

Microsoft .Net 6 LINQ / Blog

What is Microsoft .Net 6 LINQ?

LINQ stands for Language-Integrated Query and it is a powerful query language that was introduced with .Net 3.5 & Visual Studio 2008. You can use LINQ with C# or VB to query different types of data sources such as SQL, XML, In memory objects, etc.
14-Feb-2022 /38 /455

Microsoft .Net 6 LINQ / Blog

What is Architecture of LINQ?

In this article, I am going to discuss the Architecture of LINQ. The term LINQ stands for Language Integrated Query and it is pronounced as LINK. Nowadays the use of use LINQ increasing rapidly. So, as a developer, you should understand the Linq and its architecture. At the end of this article, you will have a very good understanding of the following pointers.
14-Feb-2022 /38 /455

Microsoft .Net 6 LINQ / Blog

How to write Different Ways to Write LINQ Query?

In this article, I am going to discuss the Different Ways to write LINQ Query i.e. Linq Query Syntax and Linq Method Syntax with examples. Please read our previous article where we discussed the Architecture of LINQ i.e. how LINQ works. In this article, we are going to discuss the following pointers.
14-Feb-2022 /38 /455