Entity framework - COUNT rather than SELECT

Question

If I call the GetFoo().Count() from a method outside of my DB class, Entity Framework 5 does a rather inefficient SELECT query rather than a COUNT. From reading a few other questions like this, I see that this is expected behaviour.

public IEnumerable<DbItems> GetFoo()
{
    return context.Items.Where(d => d.Foo.equals("bar"));
}

I've therefore added a count method to my DB class, which correctly performs a COUNT query:

public int GetFooCount()
{
    return context.Items.Where(d => d.Foo.equals("bar")).Count();
}

To save me from specifying queries multiple times, I'd like to change this to the following. However this again performs a SELECT, even though it's within the DB class. Why is this - and how can I avoid it?

public int GetFooCount()
{
    return this.GetFoo().Count();
}

Solution 1

since GetFoo() returns an IEnumerable<DbItems>, the query is executed as a SELECT, then Count is applied to the collection of objects and is not projected to the SQL.

One option is returning an IQueryable<DbItems> instead:

public IQueryable<DbItems> GetFoo()
{
    return context.Items.Where(d => d.Foo.equals("bar"));
}

But that may change the behavior of other callers that are expecting the colection to be loaded (with IQueryable it will be lazy-loaded). Particularly methods that add .Where calls that cannot be translated to SQL. Unfortunately you won't know about those at compile time, so thorough testing will be necessary.

I would instead create a new method that returns an IQueryable:

public IQueryable<DbItems> GetFooQuery()
{
    return context.Items.Where(d => d.Foo.equals("bar"));
}

so your existing usages aren't affected. If you wanted to re-use that code you could change GetFoo to:

public IEnumerable<DbItems> GetFoo()
{
    return GetFooQuery().AsEnumerable();
}

Solution 2

In order to understand this behavior you need to understand difference between IEnumerable<T> and IQueryable<T> extensions. First one works with Linq to Objects, which is in-memory queries. This queries are not translated into SQL, because this is simple .NET code. So, if you have some IEnumerable<T> value, and you are executing Count() this invokes Enumerable.Count extension method, which is something like:

public static int Count<TSource>(this IEnumerable<TSource> source)
{   
    int num = 0;
    foreach(var item in source)
        num++;

    return num;
}

But there is completely different story with IQueryable<T> extensions. These methods are translated by underlying LINQ provider (EF in your case) to something other than .NET code. E.g. to SQL. And this translation occurs when you execute query. All query is analyzed, and nice (well, not always nice) SQL is generated. This SQL is executed in database and result is returned to you as result of query execution.

So, your method returns IEnumerable<T> - that means you are using Enumerable.Count() method which should be executed in memory. Thus following query is translated by EF into SQL

context.Items.Where(d => d.Foo.equals("bar")) // translated into SELECT WHERE

executed, and then count of items calculated in-memory with method above. But if you will change return type to IQueryable<T>, then all changes

public IQueryable<DbItems> GetFoo()
{
    return context.Items.Where(d => d.Foo.equals("bar"));
}

Now Queryable<T>.Count() is executed. This means query continues building (well, actually Count() is the operator which forces query execution, but Count() becomes part of this query). And EF translates

context.Items.Where(d => d.Foo.equals("bar")).Count()

into SQL query which is executed on server side.

Comments

[Ross]

Hi! I've tried messing around with .AsQueryable().Count() and .AsEnumerable().Count() and the performance stats I'm getting are the same. Eg. - In MySql Workbench "Select count(*) from apples" ~1ms - In EF linq "dbContext.apples.Count()" ~800ms

[Sergey Berezovskiy]

@Ross I think you should create new question with all required information. E.g. if it's your first query, then EF takes some time to build edm