Entity Framework Base Repository

Question

I'm trying to create a base repository for use with Entity Framework 4.0 and having some trouble. In this code below, why is it not possible to do this in one line?

public IEnumerable<T> GetAll<T>(Expression<Func<T, bool>> filter)
{
    IEnumerable<T> allCustomers = this.GetAll<T>();
    IEnumerable<T> result = allCustomers.Where(filter.Compile());

    return result;
}

Won't this result in 2 SQL statements: one without a where clause that retrieves all rows, and one with a where clause that only retrieves the rows that match the predicate?

How can this be done with a single SQL statement? I can't get it to compile if I try to cast the filter.Compile() to Func<Customer, bool>.

Answer 1

Try this:

this.GetAll<T>().Where(filter);

If you want to add additional conditions and execute them on database side (using SQL), GetAll() should return IQueryable. IQueryable version of where takes Expression, so there is no need to call Compile(). EF will take expression and translate it to SQL.

Using IEnumerable version of Where executes query and retrieves all rows in the table before applying filter.