Friday, October 18, 2019

IEnumerable Vs IQueryable

When working with a database, sometimes the situation may become confusing. Some questions may develop such as what we should use to fetch data from SQL Server, should I go with IEnumerable or should I go with IQueryable?

So here I am trying to show the difference between these two and you can use them depending on your requirements.

IEnumerable
  1. System.Collection namespace
  2. No Base Interface
  3. Supports Deferred Execution
  4. No Lazy Loading
  5. No Custom Query support
  6. Suitable for LINQ to Object and LINQ to XML Queries
  7. When querying data from a database IEnumerable executes a SELECT query on the server side. It loads the data in-memory on the client side and does a filter.
Now we will see how IEnumerable works in real life.

I have a Table Employee in my database as in the following:

Table Employee
Figure 1: Employee table

Now I created a new application and right-click on the solution then select Add New Item -> LINQ To SQL Classes -> Employee.dbml.

Now write your query to select records from the Employee Table as in the following:

Table
Figure 2: Selecting records from Employee table
  1. EmployeeDataContext dc = new EmployeeDataContext();  
  2. IEnumerable<Employee> list = dc.Employees.Where(p => p.Name.StartsWith("S"));  
  3. list = list.Take<Employee>(10);  
Now debug and you will see the following:

Debug
Figure 3: After debugging

The following is the SQL Query corresponding to the preceding statement:
  1. SELECT [t0].[ID], [t0].[Name], [t0].[Email], [t0].[Country]  
  2. FROM [dbo].[Employee] AS [t0]  
  3. WHERE [t0].[NameLIKE @p0  
There is no Select TOP statement. So the filter will be applied on the client side after getting all the records from the database.

IQueryable
  1. System.Linq namespace.
  2. Best to query data from Out-Memory.
  3. When querying data from a database IQueryable executes a SELECT Query on the server side with all filters.
  4. Suitable for LINQ To SQL Queries.
  5. Supports Deferred Execution.
  6. Supports custom query using Create Query and Execute methods.
  7. Supports Lazy Loading.
Now we will see it programmatically as in the following:

see it program
Figure 4: Code Snippet
  1. EmployeeDataContext dc = new EmployeeDataContext();  
  2. IQueryable<Employee> list = dc.Employees.Where(p => p.Name.StartsWith("S"));  
  3. list = list.Take<Employee>(10  
Now debug the query:

Now debug Query
Figure 5: After debugging
  1. SELECT TOP (10) [t0].[ID], [t0].[Name], [t0].[Email], [t0].[Country]  
  2. FROM [dbo].[Employee] AS [t0]  
  3. WHERE [t0].[NameLIKE @p0  
So now you can see the difference between these 2 and use it depending on your business needs.

see the difference
Image 6. 

No comments:

Post a Comment

Lab 09: Publish and subscribe to Event Grid events

  Microsoft Azure user interface Given the dynamic nature of Microsoft cloud tools, you might experience Azure UI changes that occur after t...