Why is IEnumerable.Count() capped at 200?

Question

Is there a limit to the rows that IEnumerable.Count() (or IQueryable.Count()) using LINQ to SQL? For whatever reason, if my query returns more than 200 records, I only get 200 from IEnumerable.Count(). I've even tried using IEnumerable.LongCount() (even though the number of results shouldn't be high enough to need it). I've also verified that calling COUNT on the database returns more than 200 records.

I've checked MSDN and tried Googling it, but to no avail.

Am I going crazy, or is there something that I'm missing somewhere? I suppose this isn't really a big deal (as the program is inserting the right number of records), but it'd be nice to be able to log the number of records transferred.

Could we see some sample code?

public IEnumerable<Irms_tx_modify_profile_ban> ExtractNewAdmits()
    {
        var results = from a in _dc.Applications
                      select (Irms_tx_modify_profile_ban)new RMSProfile
                      {
                          //assign column names to property names
                      };

        //Don't know why, but Bad Things happen if you don't put the
        //OfType call here.
        IEnumerable<Irms_tx_modify_profile_ban> narrowed_results = results.OfType<Irms_tx_modify_profile_ban>();
        Program.log.InfoFormat("Read {0} records from Banner.", narrowed_results.Count());
        return narrowed_results;
      }

The reason for the comment about bad things happening is due to the issues brought up in this thread. What I did just find out is that if I call Count on narrowed_results (IEnumerable), it returns the right amount. If I call it on results (IQueryable), it returns just 200. I'll go ahead and solve Skeet's answer (since he mentioned the difference between IQueryable and IEnumerable), but if anyone is able to explain why this is happening, I'd like to hear it.

Answer 1

I've not heard of anything like that, and it does sound very odd.

The most obvious thing to check is what query is being sent to the database. Also, it matters a great deal whether you're calling Enumerable.Count() (i.e. on an IEnumerable<T>) or Queryable.Count() (i.e. on an IQueryable<T>). The former will be iterating through the actual rows in .NET code to retrieve the count; the latter will put the count into the query.

Could we see some sample code?

EDIT: Okay, so having seen the code:

• When you didn't call OfType, it was executing the count at the SQL level. That should have been visible in the SQL logged, and should be reproducible with any other SQL tool.

• I suspect you didn't really have to call OfType. You could have called AsEnumerable, or just declared results as IEnumerable<Irms_tx_modify_profile_ban>. The important thing is that the type of the variable decides the extension method to use - and thus where the count is executed.

It's worth noting that your current solution is really inefficient - it's fetching all the data, and counting it but ignoring everything but the count. It would be much better to get the count onto the server side - and while I know that doesn't work at the moment, I'm sure with a bit of analysis we can make it work :)