Convert an IQueryable linq query to IEnumerable<T> cancels out linq optimized way to work?

Question

I'm kinda newbie on .NET, and I was wondering how does linq works, since you can aply many linq queries, one after another, but none of them are really executed until they're used to transfer information or converted to list, etc.
There are 2 important ways to get a linq query, by using IQueryable<T>, which aplies the where filters directly on the Sql, and IEnumerable which get all the records and then it work with them on memory. However, let's take a look on this code:

            //Linq dynamic library
            IQueryable<Table> myResult = db.Categories
                .Where(a => a.Name.Contains(StringName))
                .OrderBy("Name")
                .Skip(0)
                .Take(10);

            if (myResult != null)
            {
                return myResult.AsEnumerable();
            } 
            else
            { return null; }

Depsite i'm using Linq dynamic library, the direct result from this query is being get on IQueryable<T>, if the query is finally being returned as IEnumerable, is the query being really filtered on the sql? or is it in memory?

Answer 1

It's still going to execute in the database, don't worry. Basically it's all down to which implementation of Where etc is used. While you're calling methods on IQueryable<T> - via the Queryable extension methods - it will be using expression trees. When you start to fetch from that query, it will be turned into SQL and sent to the database.

On the other hand, if you use any of those methods after you've got it as an IEnumerable<T> (in terms of the compile-time type), that will use the extension methods in Enumerable, and all of the rest of the processing would be done in-process.

As an example, consider this:

var query = db.People
              .Where(x => x.Name.StartsWith("J"))
              .AsEnumerable()
              .Where(x => x.Age > 20);

Here AsEnumerable() just returns its input sequence, but typed as IEnumerable<T>. In this case, the database query would return only people whose name began with J - and then the age filtering would be done at the client instead.

Answer 2

If you return an IEnumerable<T> and then further refine the query, then the further refinement happens in memory. The part that was expressed on an IQueryable<T> will get translated to the appropiate SQL statements (for the LINQ-to-SQL case, obviously).

See Returning IEnumerable<T> vs. IQueryable<T> for a longer and more detailed answer.