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);

Have you performed a SQL Trace to identify what SQL was submitted to the database server? It could be bad indexing. If you can capture the exact SQL submitted to the database and can run it outside of EF does it exhibit the same slowness? — barrypicker, Aug 20 '14 at 19:12
I had some negative experience with entity framework when it comes to startup time. In one of our applications, it seems that the initial access to the DB may take up to 15-25 seconds (internal initialization of the big models), while repetitive accesses inside the same session are extremely fast. I don't know if your code stays running or is reloaded every time, but this may be the case. — Barak Itkin, Aug 20 '14 at 19:17
@LightningIsMyName Taking that one step farther, we had similar issues but only on the _very_ first access (~5-10s). After that any access by any context/thread was fast. I always assumed it was the abstract classes being generated/defined, but if you're seeing it per-session then it must be some form of state checking with the server when the context is instantiated. — Basic, Aug 20 '14 at 19:20
Off topic, but if you do not pass an argument for a `params` parameter, the compiler will give you an empty array instead, resulting in (potentially) being able to avoid doing a null check. — Matthew, Aug 20 '14 at 19:28

score 9 · Accepted Answer · edited May 23 '17 at 12:21

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

Great answer, I never would have got it. – Big Daddy Aug 20 '14 at 19:17 — Big Daddy, Aug 20 '14 at 19:17

Entity Framework is Really Slow Getting a Single Item

1 Answers1