Linq performance between

Question

I am using Entity Framework and I want to know if there are any difference between the following code. The first and second code seems to get all the items an them filter by Entidad.Nombre == "Empresa", the last code seems to get only the items where Entidad.Nombre == "Empresa". Am I wrong? What is better?

var listFields = from b in unitOfWork.PropiedadRepository.Get()
                         where b.Entidad.Nombre == "Empresa"
                         select b;
var listFields2 = unitOfWork.PropiedadRepository.Get().Where(x => x.Entidad.Nombre == "Empresa");
var listFields3 = unitOfWork.PropiedadRepository.Get(x => x.Entidad.Nombre == "Empresa");

Here is the GenericRepository class. All the repositories inherit of that.

public sealed class GenericRepository<TEntity> where TEntity : class
{
    private readonly ConfigurationDbDataContext _context;
    private readonly DbSet<TEntity> _dbSet;

    public GenericRepository(ConfigurationDbDataContext context)
    {
        _context = context;
        _dbSet = context.Set<TEntity>();
    }

    public IEnumerable<TEntity> Get(
        Expression<Func<TEntity, bool>> filter = null,
        Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null,
        string includeProperties = "")
    {
        IQueryable<TEntity> query = _dbSet;

        if (filter != null)
        {
            query = query.Where(filter);
        }

        query = includeProperties.Split(new[] {','}, StringSplitOptions.RemoveEmptyEntries).Aggregate(query, (current, includeProperty) => current.Include(includeProperty));

        // ReSharper disable once ConvertIfStatementToReturnStatement
        if (orderBy != null)
        {
            return orderBy(query).ToList();
        }
        else
        {
            return query.ToList();
        }
    }

    public TEntity GetById(object id)
    {
        return _dbSet.Find(id);
    }

    public void Insert(TEntity entity)
    {
        _dbSet.Add(entity);
    }

    public void Delete(object id)
    {
        var entityToDelete = _dbSet.Find(id);
        Delete(entityToDelete);
    }

    public void Delete(TEntity entityToDelete)
    {
        if (_context.Entry(entityToDelete).State == EntityState.Detached)
        {
            _dbSet.Attach(entityToDelete);
        }
        _dbSet.Remove(entityToDelete);
    }

    public void Update(TEntity entityToUpdate)
    {
        _dbSet.Attach(entityToUpdate);
        _context.Entry(entityToUpdate).State = EntityState.Modified;
    }
}

Answer 1

You are right.

In the last case the evaluation of the where clause is given to the dataserver (linq to entities) and the client receive only the filtered data.

In the other cases the client receive ALL the data and then filter it (linq to object).

That said: of what performance are we talking ? CPU, network, client side, server side ?

Usually, one prefer to let the server filter so linq to entities, but it really depends on some parameters and goal.

Be aware of the, imho, most surprising difference between linq to entities and linq to object: linq to object is case sensitive. For linq to entities it depends on database/table/column collation.

Also, as reminded by Kaspars, the handling of trailing whitespaces is different.

Answer 2

As others already said, third query will be the fastest as filtering is done on DB level. Only relevant records are transferred to client.

Although main question is already answered, I would like to propose a change in base repository to make it more elastic. Currently you pass all the possible filtering options to the repository Get function and apply them there. This seems to be very limiting and unnecessary.

If at some point you would like to perform additional operations (for example, GroupBy) at DB level, you would be unable to do so unless you add additional parameter to Get function.

I propose to change Get function into GetAll and return IQueryable instead. You could add default filtering and ordering rules there.

public IQueryable<TEntity> GetAll()
{
    return _dbSet
        .Where(x => !x.IsDeleted)
        .OrderBy(x => x.Date);
}

By doing that all your queries would always perform filtering/ordering/aggregating at DB level. Also, this would be more elastic as you could use all the functions that Linq to Entities offers.