Filter database-level results Entity Framework

Question

I have the following question about using the Entity Framework.

I have an abstract class for the class that communicates with the base. Aiming at the performance want instead of bringing all the records of a table to then filter, I want to filter them before bringing.

public abstract class BaseRepository<TEntity> where TEntity : class
{
    protected DbContext context;
    protected DbSet<TEntity> dbSet;

    public BaseRepository(DbContext context)
    {
        this.context = context;
        this.dbSet = context.Set<TEntity>();
    }

    public virtual IEnumerable<TEntity> GetAll()
    {
        return this.dbSet.AsQueryable();
    }
}

public class MovimentoTratamentoRepo : BaseRepository<MovimentoTratamento>
{
    public MovimentoTratamentoRepo() : base(new PegasusOdsContext()) { }

    public IQueryable<MovimentoTratamento> GetMovimentoTratamento()
    {
        return context.Set<MovimentoTratamento>(); 
    }
}

And then call it so:

GetMovimentoTratamento().Where(x=>x.nome == nome).ToList();

or

GetAll().Where(x=>x.nome == nome).ToList();

Is there a difference between the two methods? That’s the best way to do it?

Answer 1

SQL Server Profiler

It is normal to have this type of doubt with EF or any other ORM tool. To facilitate your analysis it would be interesting to use the SQL Profiler. This tool can intercept all queries being sent to the bank.

• What is SQL Server Profiler

https://msdn.microsoft.com/pt-br/library/ms181091(v=sql.120). aspx

• Start SQL Server Profiler

https://msdn.microsoft.com/pt-br/library/ms173799(v=sql.120). aspx

Concept of implementation in the RU

EF will only execute the query after executing the command ToList(), follows link.

• Understanding the implementation cycle in the RU

http://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/advanced-entity-framework-scenarios-for-an-mvc-web-application

Sending filters to the repository

To facilitate the execution of filters you can send a Func<T,bool> to your repository. See the code.

public abstract class BaseRepository<TEntity> where TEntity : class
{
    protected DbContext context;
    protected DbSet<TEntity> dbSet;

    public BaseRepository(DbContext context)
    {
        this.context = context;
        this.dbSet = context.Set<TEntity>();
    }

    public virtual IEnumerable<TEntity> GetAll()
    {
        return this.dbSet.AsQueryable();
    }

    public virtual IEnumerable<TEntity> GetAll(Func<TEntity, bool> filter)
    {
        return this.dbSet.AsQueryable().Where(filter).ToList();
    }
}

public class MovimentoTratamentoRepo : BaseRepository<MovimentoTratamento>
{
    public MovimentoTratamentoRepo() : base(new PegasusOdsContext()) { }

    public IQueryable<MovimentoTratamento> GetMovimentoTratamento()
    {
        return context.Set<MovimentoTratamento>(); 
    }

    public IEnumerable<MovimentoTratamento> GetMovimentoTratamento(Func<MovimentoTratamento, bool> filter)
    {
        return context.Set<MovimentoTratamento>()
                      .Where(filter)
                      .ToList(); 
    }
}