hit tracker

IEnumerable

Home » IEnumerable

Usage of AsEnumerable and AsQueryable in LINQ

in C-Sharp, Programming by Balamanigandan B Comments are off

C# is one of the most popular languages today and there are many reasons for this. It has many qualities that make it suitable for development for users at most all levels. Let’s discuss how to efficiently use the AsEnumerable and AsQueryable in C# LINQ.

Language-Integrated Query (LINQ) introduces standard, easily-learned patterns for querying and updating data, and the technology can be extended to support potentially any kind of data store. LINQ is perhaps the most important features in .NET used in the workplace today.

IEnumerable iterates the collection from Bottom to Top without Filters but IQueryable iterates the collection from Bottom to Top with Filters, it’s derived from IEnumerable.

Both IEmumerable and IQueryable are executes the query in deferred mechanism.

Plan your Search strategy before you start your search. It purely depends on the amount of Data and where we are consuming the data?

The LINQ Query may used for querying database or for local in memory collection. Let us see how .AsEnumerable() and.AsQueryable() works in Database over Entity Framework using deferred execution.

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.

The LINQ technologies make extensive use of deferred execution in both the members of core System.Linq classes and in the extension methods in the various LINQ namespaces, such as System.Xml.Linq.Extensions.

Let us consider the SQL Query to create an EmplyeeInfo Table for our further illustration purpose.

AsEnumerable:

.Img1

The above diagram clearly demonstrates how LINQ gets functioned. The .AsEnumerable() splits the LINQ into two parts Before Query and After Query. So, the first part i.e., “Before” .AsEnumerable() is db.Employeeinfo, it initiates to fetch all the records from the database using the automated generated query.

In the Second part i.e., “After” .AsEnumerable() is .Where(s => s.id == 1), it filters the collection which is stored in the local in-memory collection not from the SQL Server. It means it implements the Deferred Query Execution instead of Immediate Query Execution, first it generates the SQL query to fetch the entire records. When it needs, then it execute the query in the database server and then it stores the resultant records in local memory as a read-only collection otherwise just it holds the query. After that .AsEnumerable() method suspends the underlying connection between client and database server. So, the SQL query which is generated in the first part is now transferred to memory as a predicate object, and then it applies the filter over the predicate object.

Now the first question is when the SQL gets execute? It executes the SQL when it gets enumeration; this is called as Deferred Query Execution. For example the Enumerable Collection may transferred into a List or any other data structure, then it executes the SQL and transfers the resultant records in to the local memory as a read-only collection and then the filters gets effect, finally the resultant output is generated as a list or any other preferred data structure. In other words, it executes the query only in the final enumeration statement not for all the statement prior to the final enumeration statement.

Let us consider the C# Code:

Img2

The value stored in the enum1 is

The value stored in the enum2 is

enum2 = {System.Linq.Enumerable.WhereEnumerableIterator<App.PR_EmployeeInfo>}

The value stored in the enum3 is

id empID firstName lastName Dob Gender
1 1089 Balamanigandan B 12-06-1985 Male

 

The above values clearly narrates, the predicate query is generated and awaiting for the Deferred Query Execution instead of an Immediate Query Execution till the third statement execution.

AsQueryable:

Img3

The above diagram clearly demonstrates how the LINQ gets functioned. .AsQueryable() splits the LINQ into two parts Before Query and After Query. So, the first part i.e., Before AsQueryable() is db.Employeeinfo, it initiates to fetch all the records from the database using the automated generated query.

But in the second part, it just appends the filter query with the base query which is generated earlier and the final query would be

IQueryable performs better than IEnumerable in Database related query.

 Because in .AsEnumerable(), it fetches all the records and stores it in a local memory, but .AsQueryable() filters in the SQL Server level and fetches the whatever record needed. So, it reduces the memory requirement and improves the performance of an application.

 What we can’t do in an IQueryable?

 Let us consider the Scenario, and construct a List<EmplyeeBasic>

Now fetch the Employee Recode with the value of id matches with the property id which is present in the above list. The .AsQueryable() LINQ for the said scenario is

NotSupportedException was unhandled

“LINQ to Entities does not recognize the method ‘System.Collections.Generic.List1[System.Int32] ToList[Int32](System.Collections.Generic.IEnumerable1[System.Int32])’ method, and this method cannot be translated into a store expression.”

The above query will converted into a SQL query, within that no Object operation is possible. So, the above LINQ throw an exception NotSupportedException. But we can achieve that in an alternate way, Create a List<int> and then apply filter in a multiple of statement instead of an inline statement. Let us consider

In object, .AsQueryable() would not perform as IQueryable so it internally calls the base inerface IEnumerable. IQueryable is extensively created for an Entity Framework not for the local objects.

It’s a very bad practice of Select an object within IQueryable in LINQ.

Conclusion: This article targets at understanding how efficiently we can use .AsEnumerable() and .AsQueryable(). This article is intended for the beginner/intermediate level. I hope, this post will solve all your basic functionality implementation of LINQ operation in an Entity Framework. Think Big… Start Small… Do Fast…