IEnumerable vs IQueriable

IEnumerable:

-IEnumerable exists in System.Collections Namespace.
-IEnumerable can move forward only over a collection, it can’t move backward and between the items.
-IEnumerable is best to query data from in-memory collections like List, Array etc.
-While query data from database, IEnumerable execute select query on server side, load data in-memory on client side and then filter data.
-IEnumerable is suitable for LINQ to Object and LINQ to XML queries.
-IEnumerable supports deferred execution.
-IEnumerable doesn’t supports custom query.
-IEnumerable doesn’t support lazy loading. Hence not suitable for paging like scenarios.
-Extension methods supports by IEnumerable takes functional objects.

IEnumerable Example:

MyDataContext dc = new MyDataContext ();
IEnumerable list = dc.Employees.Where(p => p.Name.StartsWith("S"));
list = list.Take(10);

Generated SQL statements of above query will be:

SELECT EmpID, EmpName, Salary FROM Employee WHERE EmpName LIKE '%S%'

Notice that in this query "top 10" is missing since IEnumerable filters records on client side

IQueryable:

-IQueryable exists in System.Linq Namespace.
-IQueryable can move forward only over a collection, it can’t move backward and between the items.
-IQueryable is best to query data from out-memory (like remote database, service) collections.
-While query data from database, IQueryable execute select query on server side with all filters.
-IQueryable is suitable for LINQ to SQL queries.
-IQueryable supports deferred execution.
-IQueryable supports custom query using CreateQuery and Execute methods.
-IQueryable support lazy loading. Hence it is suitable for paging like scenarios.
-Extension methods supports by IQueryable takes expression objects means expression tree.

IQueryable Example:

MyDataContext dc = new MyDataContext ();
IQueryable list = dc.Employees.Where(p =>p.Name.StartsWith("S"));
list = list.Take(10);

Generated SQL statements of above query will be :

SELECT TOP 10 EmpID, EmpName, Salary FROM Employee WHERE EmpName LIKE '%S%'

Notice that in this query "top 10" is exist since IQueryable executes query in SQL server with all filters.

Extending using Extension methods

If we want the new method to accept some parameters. Well to do this we can define additional parameters after the first parameter that is of the type to be extended (used with this keyword . Let define one more function in int called Multiply to see this in action.

static class MyExtensionMethods
{
     public static int Multiply(this int val, int multiplier)
     {
          return val * multiplier; //10*2
     }
}
static void Main(string[] args)
{
     // Passing arguments in extension methods
     int i = 10;
     Console.WriteLine(i.Multiply(2).ToString());
}

Why only one Clustered Index per table?

• Clustered index defines the way in which data is ordered physically on the disk. And there can only be one way in which you can order the data physically.


• Imagine if we have two clustered indexes on a single table – which index would determine the order in which the rows will be stored?


• Since the rows of a table can only be sorted to follow just one index, having more than one clustered index is not allowed.