Comparing deferred execution for IQueryable, IEnumerable, and IList

queries.cs

void Main()
{
	IQueryable<C_Patient> queryable = C_Patient.AsQueryable();
	
	IEnumerable<C_Patient> enumerable = C_Patient.AsEnumerable();
	
	// Expensive execution triggered here.
	IList<C_Patient> list = C_Patient.ToList();

	// Cheap SQL query: Does the Count in SQL.
	queryable.Count();
	
	// Triggers an *expensive* SQL Query!
	// Selects all the patients from the DB, then does the count in memory.
	enumerable.Count();
	
	
	CountQueryable(queryable); // Cheap SQL query
	CountEnumerable(queryable); // Expensive SQL query, because the method is using the IEnumerable interface...
	CountEnumerableCastedToQueryable(queryable); // Cheap SQL query
	
 	// Expensive SQL Query. It appears that even if `enumerable.Count()` is called
	// previously (triggering an SQL query), the SQL query is triggered *again* here.
	CountEnumerable(enumerable);
	// Cheap SQL query, because in this case the original Query can be recovered (since the underlying query object never changed; just the compile-time type),
	// and a cheap SQL query is run. If it were an object that implements IEnumerable but not IQueryable, it would be done in-memory (see below the example with IList).
	CountEnumerableCastedToQueryable(enumerable); 
	
	CountList(list); // Done in memory. The list is already loaded in memory.
	CountEnumerable(list); // Done in memory.
	CountEnumerableCastedToQueryable(list); // Done in memory.
}

int CountQueryable(IQueryable<C_Patient> query) { return query.Count(); }

int CountEnumerable(IEnumerable<C_Patient> query) { return query.Count(); }

int CountEnumerableCastedToQueryable(IEnumerable<C_Patient> query) { return query.AsQueryable().Count(); }

int CountList(IList<C_Patient> query) { return query.Count(); }

int CountListCastedToQueryable(IList<C_Patient> query) { return query.AsQueryable().Count(); }