IQueryable and Count

Question

I have the following:

IQueryable<ViewAccountEntry> viewAccountEntries = db.AccountEntries
    .Where(x => x.DEntryID == 0)
    .Select(x => new ViewAccountEntry()
    {
        AccountEntry = x,
        DAccountEntries = db.AccountEntries
            .Where(y => y.DEntryID == 0
                && y.Amount == -x.Amount
                && y.DateEntry == x.DateEntry)
            .ToList()
    });
    Pages = new PageInfo(viewAccountEntries.Count(), page);
    ViewAccountEntries = viewAccountEntries
        .OrderBy(x => x.AccountEntry.DateEntry)
        .Skip(Pages.ItemsSkipped)
        .Take(Pages.ItemsPerPage)
        .ToList();

Inside the first Select() an object is created containing a second list.

When the .Count() is executed, does it execute the fetch of the second Select? Or does it count intelligently, knowing it does not need to perform either Select?

Answer 1

Count() is turned into the best implementation of Count() that the query engine knows about.

Database-backed query engines like Entity Framework or Linq2SQL will generally use something that causes COUNT(*), COUNT(DISTINCT some_field) or similar to be used in the SQL produced.

Other linq implementations will similarly try to be as clever as they can. For example, linq-to-objects will call the Count getter rather than enumerating through the whole enumeration if it's called on an object that implements ICollection or ICollection<T>.

It is possible that a given query engine when dealing with a given use of Count() will end up having to cycle through an enumeration of items, because it can't figure out anything more efficient. As a rule, the more you keep things on terms of the initial type of Linq (e.g. don't call ToList() unless you need to, or even AsEnumerable() if you can get away with not doing so) the better the engine will do, though there are sometimes exceptions.