Entity Framework is Really Slow Getting a Single Item

Question

Getting a single item from a table containing 6,000 records is taking about 30 seconds. Obviously, this is not acceptable and I can't figure out why. My stack is .NET 4.5, EF 6, and Web API 2. Is anything glaringly wrong with what I've done?

// DbSet
internal DbSet<TEntity> _dbSet;

// Ctor
public GenericRepository(TContext context)
        {
            _context = context;
            _context.Configuration.ProxyCreationEnabled = false;
            _dbSet = _context.Set<TEntity>();
        }

// Really slow method
public TEntity GetByFilter(Func<TEntity,bool> filter, params Expression<Func<TEntity, object>>[] includes)
        {
            IQueryable<TEntity> query = _dbSet;
            if (includes != null)
            {
                foreach (var include in includes)
                    query = query.Include(include);
            }

            var entity = query.Where(filter).FirstOrDefault();

            return entity;
        }

// Here's how it's called.  It returns a single item
var x = _unitOfWork.Repository.GetByFilter(i => i.WinId == id, null);

Answer 1

The reason why this is slow is you are using linq-to-objects in your Where clause, meaning you're executing the predicate on the client (C#) instead of the server (SQL), the C# is receiving 6000 database records and then filtering it in memory.

You can see this because your filter parameter is of type Func, which implies you're using linq-to-objects by way of the IEnumerable.Where extension.

Instead, what you want to use is the IQueryable.Where extension that takes a parameter of type Expression. This makes use of the Entity Framework query provider, and thus uses linq-to-ef instead.

Update your method signature to be as follows:

public TEntity GetByFilter(
    Expression<Func<TEntity,bool>> filter, 
    params Expression<Func<TEntity, object>>[] includes)

This is illustrated further in the following stackoverflow answer https://stackoverflow.com/a/793584/507793