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
}

Is efQuery IEnumerable or IQueryable? Also if you could post your actual code that would help. — Maess, Jul 03 '13 at 15:26
efQuery is `IQueryable`, it is a query that hasn't been executed against the database yet. I've added the code. — ReFocus, Jul 03 '13 at 15:37

Rick Strahl · Answer 1 · 2013-09-26T08:44:16.187

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.

score 1 · Answer 2 · answered Jul 03 '13 at 15:29

1

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.

answered Jul 03 '13 at 15:29

Dave Swersky

34,502
9
78
118

1

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... – ReFocus Jul 03 '13 at 15:36
1

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. – Dave Swersky Jul 03 '13 at 16:16
4

No, `Count()` should force a query on a `IQueryable` - http://stackoverflow.com/questions/890381/how-to-count-rows-within-entityframework-without-loading-contents – ReFocus Jul 04 '13 at 08:37
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. – Dave Swersky Jul 08 '13 at 12:40

EntityFramework counting of query results vs counting list

2 Answers2