Categories
entity-framework generics repository sql

Entity Framework, Generic Repository Pattern and strange SQL generation

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.