Entity Framework repostory pattern GetAll() too slow

Question

I'm using repository layer. My problem here is GetAll() method is too slow when join a table with large records. It is taking 40 seconds to run a simple query.

IGenericRepository:

public interface IGenericRepository<TEntity>
{
    TEntity FindBy(Expression<Func<TEntity, bool>> predicate);
    IEnumerable<TEntity> GetAll();
    TEntity GetById(int id);
    TEntity Insert(TEntity entity);
    TEntity Update(TEntity entity);
    void Delete(object id);
    void Save();
}

GenericRepository:

public class GenericRepository<TEntity> : IGenericRepository<TEntity> where TEntity : class
{
    private MyStoreContext _dbContext;
    protected DbSet<TEntity> DbSet;

    public GenericRepository()
    {
        _dbContext = new MyStoreContext ();
        DbSet = _dbContext.Set<TEntity>();
    }

    public TEntity FindBy(Expression<Func<TEntity, bool>> predicate)
    {
        return DbSet.Where(predicate).SingleOrDefault();
    }

    public IEnumerable<TEntity> GetAll()
    {
        return DbSet.AsNoTracking();
    }

    public TEntity GetById(int id)
    {
        return DbSet.Find(id);
    }

    public TEntity Insert(TEntity entity)
    {
        DbSet.Add(entity);
        Save();
        return entity;
    }

    public TEntity Update(TEntity obj)
    {
        DbSet.Attach(obj);
        _dbContext.Entry(obj).State = EntityState.Modified;

        Save();
        return obj;
    }

    public void Delete(object id)
    {
        TEntity entityToDelete = DbSet.Find(id);
        Delete(entityToDelete);
    }

    public void Delete(TEntity entityToDelete)
    {
        if (_dbContext.Entry(entityToDelete).State == EntityState.Detached)
        {
            DbSet.Attach(entityToDelete);
        }

        DbSet.Remove(entityToDelete);
        Save();
    }

    public void Save()
    {
        try
        {
            _dbContext.SaveChanges();
        }
        catch (DbEntityValidationException dbEx)
        {
            foreach (var validationErrors in dbEx.EntityValidationErrors)
            {
                foreach (var validationError in validationErrors.ValidationErrors)
                {
                    System.Console.WriteLine("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage); // you just put the log to know the errors
                }
            }
        }
    }

    protected virtual void Dispose(bool disposing)
    {
        if (disposing)
        {
            if (_dbContext != null)
            {
                _dbContext.Dispose();
                _dbContext = null;
            }
        }
    }
}

Linq:

var conceptosDetalle = from pf in _parFactfRepository.GetAll()
                               join inve in _inveRepository.GetAll() on pf.CVE_ART equals inve.CVE_ART
                               where inve.STATUS == "A" && pf.CVE_DOC == cveDoc
                               orderby inve.CTRL_ALM, inve.CVE_ART
                               select new MyViewModel()
                               {
                                   CTRL = inve.CTRL_ALM,
                                   CVE_ART = inve.CVE_ART,
                                   UNID = "PIEZA",
                                   CANT = pf.CANT,
                                   DESCR = inve.DESCR,
                                   PREC = pf.PREC,
                                   DESC1 = pf.DESC1,
                                   TOTIMP4 = pf.TOTIMP4
                               };

The query returns 10 records. parFactfRepository contains 992590 rows, inveRepository contains 41908 rows.

What i'm doing wrong?

Answer 1

That's because you're mixing and matching repository-based queries and LINQ queries. Rather than doing a true join, you're fetching all the rows for each table and then joining them in-memory.

The easiest way to fix this is probably to just return IQueryable<TEntity> rather than IEnumerable<TEntity> from your GetAll method. Using IEnumerable<TEntity> forces the query to evaluate. If you're going to return IEnumerable<TEntity> your data should be fully-baked, i.e. no further alterations to the query are necessary (including joins).

That said, this is yet one more failing of trying to use the repository pattern with EF. If you aren't very careful, you end up introducing logical errors like this that aren't obvious as to why they are happening. Entity Framework already implements the repository pattern; that is what a DbSet is. If you want an abstraction over that, introduce a service layer. With that, you'd simply have a method like:

public IEnumerable<MyViewModel> GetConceptosDetalle()
{
    ...
}

And that method would contain this entire query (using EF directly, rather than your completely unnecessary repository layer). That way, your application simply calls a method that returns the data it needs, and that service layer contains all the logic. That's true abstraction. With a repository, you're bleeding logic all over your codebase.

Note: I made it return MyViewModel simply for ease of explanation, but in reality, you should return some sort of DTO, which you could then map to your view model. It would be bad idea to leak view business logic into a service layer.