Query Return - Generic Repository Entity Framework

Question

Query Return - Generic Repository Entity Framework

Navigation

#1 by (3 votes)

3

I have a generic repository and I realized that it is taking a long time to return a query with the selection of some columns of my database, the Query () method is as follows:

public IList<T> Query(System.Linq.Expressions.Expression<Func<T, bool>> Where)
    {
        return Entity.AsExpandable().Where(Where).AsQueryable<T>().AsNoTracking<T>().ToList();
    }

If you look at the end I still use Take (10), but analyzing the code, has a field called 'source' which shows 17822 records, so regardless of selecting 10 or 1000, it seems to me that it is always bringing all the record, follow image:

My question is if I have some configuration of the entity to always have this behavior or the call of my query is wrong?

Thank you

Léo

sql-server c# entity-framework-6 linq-to-entities

asked by anonymous 26.04.2018 / 13:24

1 answer

How to get N-Points FFT in R? SQL Query export to excel

score 3 · Accepted Answer

Basically your problem is that when you use skip and take after toList() , the EF has already been to bd, brought all those records, and skip and take are happening in memory.

I understand that you need to make your take(10) , out of the repository, so I suggest the following modification in your function:

public IQueryable<T> 
Query(System.Linq.Expressions.Expression<Func<T, bool>> Where)
{
    return Entity.AsExpandable().Where(Where).AsNoTracking<T>();
}

We remove the .AsQueryable<T>() , because AsNoTracking<T>(), já te retorna Queryable (T) '. this already helps you a bit of performance.
We remove ToList() so that EF does not go to the database right now, we're just creating your Query , which will be returned. So you would use Query as follows:

var resultadoDaQuery = Query(seyWhere).Skip(skipRows).Take(pageSize).ToList();

At this point, when we use the ToList() method, the EF goes to the database, bringing only the records, considering its Skip() and Take() .