Taylor Mitchell Taylor Mitchell - 15 days ago 13
C# Question

Entity Framework DBSet is extremely slow

I'm following a n-layered pattern with a services layer. No repository layer. The tutorial is http://techbrij.com/service-layer-entity-framework-asp-net-mvc-unit-testing . My problem here is the

GetAll()
method here is absurdly slow. It is taking 12 seconds to run a simple paginated query. It seems to be an issue with EFs
DBSet
, being retrieved by using the
_context.Set<T>()
method

My EntityService

public class EntityService<T> : IEntityService<T> where T : BaseEntity
{
protected GraphicContext _context;
protected DbSet<T> _dbset;

public EntityService(GraphicContext context)
{
_context = context;
_dbset = _context.Set<T>();
}


public virtual async Task CreateAsync(T entity)
{
if (entity == null)
{
throw new ArgumentNullException("entity");
}

_dbset.Add(entity);
await _context.SaveChangesAsync();
}

public virtual async Task<T> FindAsync(params object[] keyValues)
{
if (keyValues == null)
{
throw new ArgumentNullException("id");
}

return await _dbset.FindAsync(keyValues);
}

public virtual async Task UpdateAsync(T entity)
{
if (entity == null) throw new ArgumentNullException("entity");
_context.Entry(entity).State = System.Data.Entity.EntityState.Modified;
await _context.SaveChangesAsync();
}

public virtual async Task DeleteAsync(T entity)
{
if (entity == null) throw new ArgumentNullException("entity");
_dbset.Remove(entity);
await _context.SaveChangesAsync();
}

public virtual IEnumerable<T> GetAll()
{
return _dbset.AsEnumerable<T>();
}
}


It is using
DBSet
because after some research
IDBSet
is obsolete and was also slow on us.

The table we are accessing has about 300,000 records, but we are using pagination to help the query and for ease of access for the user. Anyways, To test that it was the call to
_context.Set<T>()
that is being slow I skipped the service and ran my context in the controller to run the exact same query. The query took less than a second.

Does anyone know why this would be this way or have a way to speed this up? I'm thinking I may have to avoid using the set() method. Any other alternatives to this?

Answer

When the result type of GetAll is IEnumerable<T>, all the queries against the result will cause loading the whole table in memory and then querying it via LINQ to Objects.

If you want your queries to be executed at the database (i.e. via LINQ to Entities), remove AsEnumerable() call and change GetAll type to IQueryable<T>:

public virtual IQueryable<T> GetAll()
{
    return _dbset;
}
Comments