I've sat through countless code reviews where a junior dev proudly presents a Generic Repository that returns IEnumerable<T> or List<T> for every method. They tell me, "I've completely decoupled the business logic from Entity Framework! The service layer has no idea how the data is fetched." On the surface, it looks like clean architecture. In reality, it's a performance time bomb.

The "Complete Abstraction" Trap

The misconception is that a repository's job is to hide the data access technology so thoroughly that the caller never sees a hint of the database. If you return a List<T>, you think you've achieved purity. But let's look at what happens in the actual execution when you try to filter that list.

// The "Pure" Repository Method
public List<Product> GetAllProducts() 
{
    return _context.Products.ToList(); // Data is pulled into memory HERE
}

// The Service Layer Call
var expensiveProducts = _repository.GetAllProducts()
                                   .Where(p => p.Price > 1000)
                                   .ToList();

In this scenario, GetAllProducts() executes SELECT * FROM Products. If your table has two million rows, you just shoved two million objects into your RAM, only to discard 99% of them in the next line. You've effectively turned your database into a very expensive file system.

Leaking IQueryable for Performance

To fix this, we have to accept a "leaky abstraction." We need to allow the service layer to define what it wants without telling it how to get it. By returning IQueryable<T>, we aren't executing the query yet; we are building a query expression that EF Core can eventually translate into a single, efficient SQL statement.

However, some architects argue that IQueryable leaks too much of EF into the service layer. The middle ground—and the one I usually recommend—is to use Expression<Func<T, bool>>. This allows you to pass a lambda expression into the repository, which EF then converts to a WHERE clause in SQL, while the repository method itself can still return a concrete result.

Implementing the Expression-Based Filter

Let's build a repository that handles this correctly. Notice how the Find method doesn't take a value, but an Expression. This is the secret sauce that keeps the filtering on the database server.

public interface IGenericRepository<T> where T : class
{
    T GetById(int id);
    IEnumerable<T> Find(Expression<Func<T, bool>> predicate);
    void Add(T entity);
    void Remove(T entity);
}

public class GenericRepository<T> : IGenericRepository<T> where T : class
{
    protected readonly DbContext _context;
    protected readonly DbSet<T> _dbSet;

    public GenericRepository(DbContext context)
    {
        _context = context;
        _dbSet = context.Set<T>();
    }

    public T GetById(int id) => _dbSet.Find(id);

    public IEnumerable<T> Find(Expression<Func<T, bool>> predicate)
    {
        // The predicate is passed directly into the EF Where clause
        // The SQL is generated and executed on the server
        return _dbSet.Where(predicate).ToList();
    }

    public void Add(T entity) => _dbSet.Add(entity);
    public void Remove(T entity) => _dbSet.Remove(entity);
}

Now, when you call _repo.Find(p => p.Price > 1000), EF Core generates SELECT ... FROM Products WHERE Price > 1000. Your app's memory stays lean, and the database does what it was designed to do: filter data.

📋 Practical Task

Exercise: Building the Order Management Repository

You are tasked with implementing a generic repository system for an e-commerce backend. You need to ensure that filtering happens at the database level to avoid memory overflows.

Requirements:

• Create an Order class with properties: int Id, DateTime OrderDate, decimal TotalAmount, and string Status.
• Implement a GenericRepository<T> class that inherits from an IGenericRepository<T> interface.
• The repository must include a Find method that accepts an Expression<Func<T, bool>> and returns an IEnumerable<T>.
• In your Main method (or a test class), instantiate the repository for the Order type and use the Find method to retrieve only orders that have a Status of "Shipped" and a TotalAmount greater than 500.

Note: You can use a mock DbContext or an In-Memory database provider to verify your logic. The key is the correct use of the Expression delegate in the repository method.