Entity Framework is really slow Getting a single item
It takes about 30 seconds to get a single element from a table with 6000 records. Obviously this is unacceptable and I can't figure out why. My stack is .NET 4.5, EF 6 and Web API 2. Is there something incredibly 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 how it called. It returns a single item
var x = _unitOfWork.Repository.GetByFilter(i => i.WinId == id, null);
source to share
The reason this is slow is because you are using linq-to-objects in your proposal Where
, that is, you execute the predicate on the client (C #) instead of the server (SQL), C # gets 6000 database records, and then filtering them in memory.
This can be seen because your parameter filter
is of type Func
, which means that you are using linq-to-objects through the IEnumerable.Where Extension .
Instead, you want to use the IQueryable.Where extension , which takes a type parameter Expression
. This uses the Entity Framework query provider and uses linq-to-ef instead.
Update your method signature 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 fooobar.com/questions/12481 / ...
source to share