LINQ to SQL Improve Query Performance and IEnumerable<T> vs. IQueryable<T>

CodeProject Most of the business developments involve with the Data tier integration; which has a data layer consist of queries. Entity Framework is one solution to write a data layer quickly. 
In writing a data layer completely using LINQ to SQL, it has to be precisely understood how the query is going to be executed.
I have a Table with 20,000 records and a typical select query statistics as follows.

Since my database on the same machine where result shows; it took very little time. but if we check the number of bytes received from the server is: 1410798 Bytes which is about 1.34 MB. So this is normal selection with all data.
Let say I wants to count number of rows, and that is my business requirement. Typical SQL Query would be,

SELECT COUNT(*)
  FROM [CodeFirstDemoAppDB].[dbo].[Students]

Now I’m going to do the same through LINQ, And I’ve used three different way to do so.

   1:  DAL.SchoolContext db = new DAL.SchoolContext();

   2:  // using var 

   3:  var results = db.Students;

   4:  Console.WriteLine(results.Count());

   5:  // using IEnumerable type

   6:  IEnumerable<Model.Student> resultsEnumerable = db.Students;

   7:  Console.WriteLine(resultsEnumerable.Count());

   8:  // using IQueryble type

   9:  IQueryable<Model.Student> resultsQueryble = db.Students;

  10:  Console.WriteLine(resultsQueryble.Count());

Following shows the respective query executed at the DB tracked with SQL profiler.

Type	Query
using var (which remains DbSet Type)	SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT COUNT(1) AS [A1] FROM [dbo].[Students] AS [Extent1] ) AS [GroupBy1]
using IEnumerable	SELECT [Extent1].[ID] AS [ID], [Extent1].[FirstMidName] AS [FirstMidName], [Extent1].[LastName] AS [LastName], [Extent1].[EnrollmentDate] AS [EnrollmentDate] FROM [dbo].[Students] AS [Extent1]
using IQueryble	SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT COUNT(1) AS [A1] FROM [dbo].[Students] AS [Extent1] ) AS [GroupBy1]

As we can see here use of IEnumerable type caused query executed at the line;

IEnumerable<Model.Student> resultsEnumerable = db.Students;

and retrieve all the records to the business layer to make count at business layer level while transporting approximately about 1.34MB. For quick information we can check the client statistics for other two types which has same SQL query output;



It is about 6 KB information transport through the network.

Conclusion

As a conclusion for business operations, which does not need to bring whole data set to the business layer Beware of your LINQ query and types you use to hold results.

For further learning about LINQ’s deferred execution please refer this article.