Linq Select Statement slow when getting COUNT

Question

I am trying to get a total record count from the below Method using EntityFramework and Linq. It is slow to return a count.

public static int totalTracking(int id)
{
   using (var ctx = new GPEntities())
   {
      var tr = ctx.Tracking
                   .Where(c => c.clientID == Config.ClientID)
                   .Where(c => c.custID == id)
                   .Where(c => c.oOrderNum.HasValue)
                  .ToList();
      return tr.Count();
   }        
}

Answer 1

You can simplify the query significantly:

using (var ctx = new GPEntities())
{
    return ctx.Tracking
        .Where(c => c.clientID == Config.ClientID)
        .Where(c => c.custID == id)
        .Where(c => c.oOrderNum.HasValue)
        .Count();
}

When you call ToList this will trigger materialization, so query will be issued to the database and all the columns will be retrieved. The actual count would happen on the client.

If you do just Count, without ToList it will issue query when you call Count and server will return just one number, instead of a table.

This is not that critical for performance, but I think the code would look a little nice without that many Where:

using (var ctx = new GPEntities())
{
    return ctx.Tracking
        .Where(c => 
            c.clientID == Config.ClientID &&
            c.custID == id &&
            c.oOrderNum.HasValue)
        .Count();
}

or even

using (var ctx = new GPEntities())
{
    return ctx.Tracking
        .Count(c => 
            c.clientID == Config.ClientID &&
            c.custID == id &&
            c.oOrderNum.HasValue);
}

Answer 2

You can grossly simplify things and just use the Count Extension method

Have you tried:

public static int totalTracking(int id)
{
    using (var ctx = new GPEntities())
    {
        return ctx.Tracking.Count(
                  c => c.clientID == Config.ClientID &&
                       c.custID == id &&
                       c.oOrderNum.HasValue);
    }        
}

Answer 3

Remove the call .ToList(). It forces the query to pull the entire results across to the client.

By removing this, and just calling .Count() directly on the results of tr (without ToList()), the Count() becomes part of the query itself, and executed remotely, which will dramatically simplify this:

public static int totalTracking(int id)
{
    using (var ctx = new GPEntities())
    {
        var tr = ctx.Tracking
            .Where(c => c.clientID == Config.ClientID)
            .Where(c => c.custID == id)
            .Where(c => c.oOrderNum.HasValue);

        // This can be added above, or left here - the end result is the same
        return tr.Count();
    }        
}