Why calling .ToList() on an IEnumerable<Entity> with .Where().Select() filters slower than calling it on the Entity itself?

I really hope I have titled this somewhat correctly as I was struggling to put into words what I am asking but...

I am currently trying to get to grips with IEnumerables.

I have a class in my application like so:

public class MyDataRepo : IDisposable
{
    private MyEntities context;

    public MyDataRepo()
    { context = new MyEntities(); }

    public IEnumerable<MyItem> GetMyItems()
    { return context.MyItems.AsEnumerable(); }

    public void Dispose()
    { context.Dispose(); }
}

I thought then I could use it like so:

using (MyDataRepo repo = new MyDataRepo())
{ 
    List<int> MyItemIDs = repo.GetMyItems().Where(x => x.ItemName == "Item Name")
                                           .OrderBy(x => x.ItemMajorIssueNumber)
                                           .ThenBy(x => x.ItemMinorIssueNumber)
                                           .Select(x => x.ItemID).ToList();
}

But it is so much slower than just doing this:

using (MyEntities context = new MyEntities())
{
    List<int> MyItemIDs = context.MyItems.Where(x => x.ItemName == "Item Name")
                                         .OrderBy(x => x.ItemMajorIssueNumber)
                                         .ThenBy(x => x.ItemMinorIssueNumber)
                                         .Select(x => x.ItemID).ToList();
}

I just wondered why?

From the way I understood it, the IEnumerable doesn't execute anything against the database until .ToList() is called.

To me the seem like they should be the same query but the second is almost instant where as the first takes aroud 4-6 seconds.

I appreciate I may of totally mis understood IEnumerable and it's usuage though.

I would ideally rather just call the IEnumerable<> and apply filters to it then call .ToList() to get results I desire than have to write several methods for all the queries against MyItems I will need.

Solution

Here we have difference with IEnumerable<T> and IQueryable<T> behaviour. When you put

  List<int> MyItemIDs = repo
    .GetMyItems()
    .Where(x => x.ItemName == "Item Name")
    ...

which is in fact

  List<int> MyItemIDs = context
    .MyItems
    .AsEnumerable() <- This is the felony!
    .Where(x => x.ItemName == "Item Name")
    ...

you fetch all the records (say, all 1_000_000 ones) from RDBMS to the workstation by .AsEnumerable() and only then filter them (Where), order them (OrderBy) etc.

On the contrary

 List<int> MyItemIDs = context
   .MyItems
   .Where(x => x.ItemName == "Item Name")
    ...

is IQueryable<T> up to the very end (.ToList()) which means that SQL query created and all filtering (Where), sorting (OrderBy) will be perfomed on server side without unwanted fetch.