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
}

Tedda

Is efQuery IEnumerable or IQueryable? Also if you could post your actual code that would help. – Benito

efQuery is IQueryable, it is a query that hasn't been executed against the database yet. I've added the code. – Tedda

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.

Mischief

Answer 2

Assuming here that efQuery is IQueryable:

ToList() actually executes a query. If changes to data in the datastore, between calls to ToList() and .Count(), result in a different resultset, calling ToList() will repopulate the list. ToList().Count and .Count() should then match until the data in the store changes the resultset again.

Mononuclear

The data doesn't change between calls. I can reproduce this issue everytime i run the code. Both calls produce different results. Even Resharper tells me I should use the .Count() method instead of forcing the .ToList() and using the .Count property... – Tedda

Count() returns 0 because the list isn't populated yet. You have to execute the query with ToList() before the query is actually executed. If you add a .Count() (without ToList()) after the ToList() line, the count will be correct. – Mononuclear

No, Count() should force a query on a IQueryable - #890881 – Tedda

I'm 99% certain that the results of the accepted answer on that question would produce 0. I'll have to try it and report back what I find. – Mononuclear