Code Example

In this example, we are going to fetch the top name of the person who works with the project "TAAGUNG". The table structure and data are as below.

Table Structure and Data

For this, we will be using the same query with different return types as shown below. Certainly, they return the same result.

  1. static void QueryWithIEnumerable(StudyEntities studyEntities)
  2. {
  3. IEnumerable<PROJECT_TEAM> projects =
  4. studyEntities.PROJECT_TEAM.Where(e => e.PRJ_ID.Equals("TAAGUNG"));
  5. Console.WriteLine(projects.ToString());
  6. projects = projects.Take(1);
  7. Console.WriteLine(projects.ToString());
  8. foreach (var project in projects)
  9. {
  10. Console.WriteLine(project.FRST_NM + " " + project.LAST_NM);
  11. }
  12. }
  13. static void QueryWithIQueryable(StudyEntities studyEntities)
  14. {
  16. IQueryable<PROJECT_TEAM> projects =
  17. studyEntities.PROJECT_TEAM.Where(e => e.PRJ_ID.Equals("TAAGUNG"));
  18. projects = projects.Take(1);
  19. Console.WriteLine(projects.ToString());
  21. foreach (var project in projects)
  22. {
  23. Console.WriteLine(project.FRST_NM + " " + project.LAST_NM);
  24. }
  25. }

How does it work?

For IQueryable, the generated query is this. Please note the TOP(1) in the first line.

  1. SELECT TOP (1)
  2. [Extent1].[PRJ_ID] AS [PRJ_ID],
  3. [Extent1].[FRST_NM] AS [FRST_NM],
  4. [Extent1].[LAST_NM] AS [LAST_NM]
  5. FROM (SELECT
  6. [PROJECT_TEAM].[PRJ_ID] AS [PRJ_ID],
  7. [PROJECT_TEAM].[FRST_NM] AS [FRST_NM],
  8. [PROJECT_TEAM].[LAST_NM] AS [LAST_NM]
  9. FROM [dbo].[PROJECT_TEAM] AS [PROJECT_TEAM]) AS [Extent1]
  10. WHERE 'TAAGUNG' = [Extent1].[PRJ_ID]

While IEnumerable generates the following SQL query and then performs in-memory filtering of data, i.e., getting the required data is a two-step process.

  1. SELECT
  2. [Extent1].[PRJ_ID] AS [PRJ_ID],
  3. [Extent1].[FRST_NM] AS [FRST_NM],
  4. [Extent1].[LAST_NM] AS [LAST_NM]
  5. FROM (SELECT
  6. [PROJECT_TEAM].[PRJ_ID] AS [PRJ_ID],
  7. [PROJECT_TEAM].[FRST_NM] AS [FRST_NM],
  8. [PROJECT_TEAM].[LAST_NM] AS [LAST_NM]
  9. FROM [dbo].[PROJECT_TEAM] AS [PROJECT_TEAM]) AS [Extent1]
  10. WHERE 'TAAGUNG' = [Extent1].[PRJ_ID]

Based on this, we can easily depict the processing of these two in the following 2 images.

IQUERYABLE
Figure 1 - IQUERYABLE

IEnumerable
Figure 2 - IEnumerable

Moreover, we can see above that the query is written in two statements. If our LINQ is written in one statement like this,

  1. IEnumerable<PROJECT_TEAM> projects = studyEntities.PROJECT_TEAM
  2. .Where(e => e.PRJ_ID.Equals("TAAGUNG")).Take(1);
  4. IEnumerable<PROJECT_TEAM> projects = studyEntities.PROJECT_TEAM
  5. .Where(e => e.PRJ_ID.Equals("TAAGUNG")).Take(1);

Then, it will generate this same query in both of the scenarios. So, please notice the different ways of writing the queries in both of the scenarios, i.e., everything is mentioned in a single query.

  1. SELECT TOP (1)
  2. [Extent1].[PRJ_ID] AS [PRJ_ID],
  3. [Extent1].[FRST_NM] AS [FRST_NM],
  4. [Extent1].[LAST_NM] AS [LAST_NM]
  5. FROM (SELECT
  6. [PROJECT_TEAM].[PRJ_ID] AS [PRJ_ID],
  7. [PROJECT_TEAM].[FRST_NM] AS [FRST_NM],
  8. [PROJECT_TEAM].[LAST_NM] AS [LAST_NM]
  9. FROM [dbo].[PROJECT_TEAM] AS [PROJECT_TEAM]) AS [Extent1]
  10. WHERE 'TAAGUNG' = [Extent1].[PRJ_ID]

Relationship

When we see the definition (F12) of IQueryable, we see it implements IEnumerable.

  1. public interface IQueryable<out T> : IEnumerable<T>, IEnumerable, IQueryable
  2. {
  3. }
  5. public interface IQueryable : IEnumerable
  6. {
  7. }

With this, IQueryable gets these 3 additional read-only properties.

  1. public interface IQueryable : IEnumerable
  2. {
  3. //
  4. // Summary:
  5. // Gets the expression tree that is associated with the instance of System.Linq.IQueryable.
  6. //
  7. // Returns:
  8. // The System.Linq.Expressions.Expression that is associated with this instance
  9. // of System.Linq.IQueryable.
  10. Expression Expression { get; }
  11. //
  12. // Summary:
  13. // Gets the type of the element(s) that are returned when the expression tree associated
  14. // with this instance of System.Linq.IQueryable is executed.
  15. //
  16. // Returns:
  17. // A System.Type that represents the type of the element(s) that are returned when
  18. // the expression tree associated with this object is executed.
  19. Type ElementType { get; }
  20. //
  21. // Summary:
  22. // Gets the query provider that is associated with this data source.
  23. //
  24. // Returns:
  25. // The System.Linq.IQueryProvider that is associated with this data source.
  26. IQueryProvider Provider { get; }
  27. }

Similarities

Differences

Feature IEnumerable IQueryable
Namespace System.Collection System.Linq
Lazy Loading No Support Supported
Custom Query No Support Supported

What to use when?

IEnumerable

IQueryable

References

https://blogs.msdn.microsoft.com/charlie/2007/12/10/linq-and-deferred-execution/