EntityFramework counting of query results vs counting list

Question

Should efQuery.ToList().Count and efQuery.Count() produce the same value?

How is it possible that efQuery.ToList().Count and efQuery.Count() don't produce the same value?

//GetQuery() returns a default IDbSet which is used in EntityFramework

using (var ds = _provider.DataSource())
{
    //return GetQuery(ds, filters).Count(); //returns 0???
    return GetQuery(ds, filters).ToList().Count; //returns 605 which is correct based on filters
}

Answer 1

Just ran into this myself. In my case the issue is that the query has a .Select() clause that causes further relationships to be established which end up filtering the query further as the relationship inner join's constrain the result.

It appears that .Count() doesn't process the .Select() part of the query.

So I have:

// projection created
var ordersData = orders.Select( ord => new OrderData() {
           OrderId = ord.OrderId,
           ... more simple 1 - 1 order maps

           // Related values that cause relations in SQL
           TotalItemsCost = ord.OrderLines.Sum(lin => lin.Qty*lin.Price),
           CustomerName = ord.Customer.Name,
};


var count = ordersData.Count();    // 207
var count = ordersData.ToList().Count // 192

When I compare the SQL statements I find that Count() does a very simple SUM on the Orders table which returns all orders, while the second query is a monster of 100+ lines of SQL that has 10 inner joins that are triggered by the .Select() clause (there are a few more related values/aggregations retrieved than shown here).

Basically this seems to indicate that .Count() doesn't take the .Select() clause into account when it does its count, so those same relationships that cause further constraining of the result set are not fired for .Count().

I've been able to make this work by explicitly adding expressions to the .Count() method that pull in some of those aggregated result values which effectively force them into the .Count() query as well:

var count = ordersData.Count( o=> o.TotalItemsCost != -999 &&
                                  o.Customer.Name != "!@#");    // 207

The key is to make sure that any of the fields that are calculated or pull in related data and cause a relationship to fire, are included in the expression which forces Count() to include the required relationships in its query.

I realize this is a total hack and I'm hoping there's a better way, but for the moment this has allowed us at least to get the right value without pulling massive data down with .ToList() first.