Entity Framework, Generic Repository Pattern and strange SQL generation

Question

I've implemented a generic repository for Entity Framework 4. Here's a dumbed down version, where AllAppContainer is the EF4 object context:

public class Repository<T> where T : class
{
    protected AllAppContainer objectContext;
    protected ObjectSet<T> entitySet;

    public Repository()
    {
        objectContext  = new AllAppContainer();
        entitySet = objectContext.CreateObjectSet<T>();
    }

    public int QueryCount(Func<T, bool> predicate)
    {
        int queryCount = entitySet.Count(predicate);
        return queryCount;
    }
}

The one method is QueryCount(), which I want to act as a select Count(*) ... where line of SQL (not returning the actual records).

Straight-forward? You'd think... First, let's do a non-Repository version of the same thing, performing a count on Item entities:

AllAppContainer allAppContainer = new AllAppContainer();
int nonRepCount = allAppContainer.Items.Count(item => item.Id > 0);

SQL Server Profiler says the generated SQL is:

SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    COUNT(1) AS [A1]
    FROM [dbo].[Items] AS [Extent1]
    WHERE [Extent1].[Id] > 0
)  AS [GroupBy1]

Woo-hoo! Score!

Now let's call the same using my Repository QueryCount:

Repository<Item> repository = new Repository<Item>();
int repCount = repository.QueryCount(item => item.Id > 0);

Here's the generated SQL:

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[SmallField] AS [SmallField]
FROM [dbo].[Items] AS [Extent1]

Yep, EF is returning the full set of data, then calling Count() on it in-memory.

For fun I tried changing the relevant line in Repository QueryCount to:

int queryCount = new AllAppContainer().CreateObjectSet<T>().Count(predicate);

and the non-repository line to:

int nonRepCount = allAppContainer1.CreateObjectSet<Item>().Count(item => item.Id > 0);

but the generated SQL for each is the same as before.

Now why would all this repository-returns-all-matching-records-then-counts be happening, when it doesn't for non-repository? And is there any way to do what I want via my generic repository i.e. count at db. I can't take the in-memory count performance hit.

Answer 1

you need to use Expression<Func<TSource, bool>> predicate for your Count otherwise the framework uses the Enumerable.Count<TSource> Method (IEnumerable<TSource>, Func<TSource, Boolean>) which gets the whole collection from DB to be able to call for each item, so your method should be:

public int QueryCount(Expression<Func<T, Boolean>> predicate)
{
    int queryCount = entitySet.Count(predicate);
    return queryCount;
}