EntityFramework counting query results versus list counting

Question

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

How is it possible that efQuery.ToList().Count and efQuery.Count() do not give 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 stumbled upon it myself. In my case, the problem is that the query has a .Select () clause, which causes further relationships to be established, which ultimately filter the query further, since the inner connection of the relationship limits the result.

It seems that .Count () is not processing part of the .Select () request.

So, I have a:

 // 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 SQL statements, I find that Count () makes a very simple SUM in the Orders table, which returns all orders, while the second query is a monster of 100 SQL rows that has 10 internal joins that are triggered by the .Select ( ) (there are several other related values ​​/ aggregates obtained than shown here).

Basically, this indicates that .Count () does not accept the .Select () clause during its calculation, therefore the same relationships that further restrict the result set are not triggered for .Count ().

I managed to do this work by explicitly adding expressions to the .Count () method, which extract some of these aggregated result values, which also effectively insert them into the .Count () request:

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

The key is to make sure that any of the fields that have been calculated or associated with the associated data and cause a connection to the fire are included in the expression, which forces Count () to include the necessary relationships in the request.

I understand that this is a complete hack, and I hope that there will be a better way, but at the moment it has allowed us to at least get the correct value without pulling out massive data using .ToList ().

Answer 2

Assuming efQuery is IQueryable :

ToList() executes the request. If changes to the data in the data warehouse, between calls to ToList() and .Count() , lead to a different set of results, a call to ToList() will re-populate the list. ToList().Count and .Count() must match until the data in the store again changes the result set.