Return IQueryable or IEnumerable What Happens?

Question

As far as I understand, IQueryable is a query that has not been executed yet, and the filter conditions like .Where(x=> x != 3) are added to the SQL query and get the final result when executed. IEnumerable, on the other hand, first takes all the data and puts it into the memory, the filtering is done over all the data in the memory.

1. If IEnumerable takes the data and puts it into memory, is the query executed?
2. So why is it said to be executed when using methods like .Count() if the IEnumerable has already run and dumped the data into memory?
3. Why do we do .Count() and not .Count like List type?

public IEnumerable<Number> GetNumbers()
{            
     IQueryable<Number> result = _context
         .Numbers
         .Where(x => x != 3);
     return result;
}

4. In the above code, the result is of type IQueryable, but IEnumerable returned from the method. What kind of operation takes place here, while a query that hasn't been executed yet, is the query executed and thrown into memory when the return statement is executed?

Answer 1

As far as I understand, IQueryable is a query that has not been executed yet, and the filter conditions like .Where(x=> x != 5) are added to the SQL query and get the final result when executed.

Yes, you understanding is correct (in case if you are working with queryable provider which performs such translation).

IEnumerable, on the other hand, first takes all the data and puts it into the memory, the filtering is done over all the data in the memory

Yes, exactly (with small but important nitpick, that IEnumerable is also lazy and all of that will be performed only when materialization happens like with .Count(), foreach, ToList etc.)

What kind of operation takes place here, while a query that hasn't been executed yet

As mentioned in the "nitpick" part earlier - nothing will happen here, but when GetNumbers(). SomeEnumOps .Count() will be called, it will fetch all the data client side, perform "SomeEnumOps" in-memory and then do the counting. Basically it will have the same effect like calling AsEnumerable over queryable in EF

Notes:

• IQueryable<T> is IEnumerable<T>

  public interface IQueryable<out T> : IEnumerable<out T>, IQueryable
  

• From Enumerable.AsEnumerable docs:

  The AsEnumerable<TSource>(IEnumerable<TSource>) method has no effect other than to change the compile-time type of source from a type that implements IEnumerable<T> to IEnumerable<T> itself.
  AsEnumerable<TSource>(IEnumerable<TSource>) can be used to choose between query implementations when a sequence implements IEnumerable<T> but also has a different set of public query methods available. For example, given a generic class Table that implements IEnumerable<T> and has its own methods such as Where, Select, and SelectMany, a call to Where would invoke the public Where method of Table. A Table type that represents a database table could have a Where method that takes the predicate argument as an expression tree and converts the tree to SQL for remote execution. If remote execution is not desired, for example because the predicate invokes a local method, the AsEnumerable method can be used to hide the custom methods and instead make the standard query operators available.

Bonus.

Why do we do .Count() and not .Count like List type?

Because LINQ is based on extension methods (like Queryable.Count) and you can't have extension properties in C# ATM.