Entity Framework - query execution performance issue

Question

I'm working in a project ASP.NET MVC3 with Entity Framework on SQL Server with performance issues.

Every time I load data from a view/table using EF and Linq. I can see by SQL Server Profiler that all the table/view content is retrieved because the where condition doesen't appear in profiler. Only later is filtered by LINQ i think.

Is it correct? How to load only data I need at first on SQL Server?

This is my example code:

var query = unitWork.City.GetFirstorDefault(item => item.City == cityCode);

Following an extraction of our datalayer with a data call example. Thanks for yours replies.

public class UnitOfWork : IUnitOfWork, IDisposable
    {

        #region CONSTRUCTOR
        public UnitOfWork()
        {
            _context = new  MyApplicationEntities(); //DataContext
            _context.ContextOptions.LazyLoadingEnabled = true;
            _context.ContextOptions.UseLegacyPreserveChangesBehavior = false; 
        }
        #endregion

       // DESC_RECHARGEABLE is a table in DB        
       public IGenericRepository<DESC_RECHARGEABLE> RepRechargeable
        {
            get{return _repRechargeable ?? (_repRechargeable = new GenericRepository<DESC_RECHARGEABLE>(_context));}
        }



}

    public interface IGenericRepository<T> : ICollection<T>
        where T : class
    {
        IEnumerable<T> Query(Func<T, bool> predicate);

void Update(T entity);

T GetFirstorDefault(Func<T, bool> predicate);

IEnumerable<T> GetAll();

T GetByKey(Func<T, bool> predicate);

bool Remove(T entity);

void Add(T entity);

       ObjectSet<T> GetQuery();
    }


public class GenericRepository<T> : IGenericRepository<T>
        where T : class
    {
        private  MyApplicationEntities Currentcontext;
        public ObjectSet<T> entitySet;
        private List<GenericRepository<T>> _list = null;
        private string entityName;

        public GenericRepository( MyApplicationEntities context)
        {
            if (context == null)
                throw new ArgumentNullException("context");
            this.Currentcontext = context;
            this.entitySet = context.CreateObjectSet<T>();
            this.entityName = entitySet.Name;
        }

        #region READ
        public IEnumerable<T> Query(Func<T, bool> predicate)
        {
            return this.entitySet.Where(predicate);
        }


        public T GetFirstorDefault(Func<T, bool> predicate)
        {
            return this.Query(predicate).FirstOrDefault();
        }


        public IEnumerable<T> GetAll()
        {
            return this.entitySet.AsEnumerable();
        }

        public T GetByKey(Func<T, bool> predicate)
        {
            return this.Query(predicate).FirstOrDefault();
        }
        #endregion
}

//Here a client call example,load all DESC_RECHARGEABLE rows for a condition

var tempList = _unitofWork.RepRechargeable.Query(item => item.COMPANY_CODE == companyCode
                                                           && item.DIVISION_CODE == divisionCode && !string.IsNullOrEmpty(item.PROPERTY));

Answer 1

Ok, based on my comment in the OP, I'm going to stick my neck out here.

The use of Func<T, bool> predicate is most likely the root ouf your issue. When you use Func<>, your code is excecuted in local memory, against the target structure (in your case, the query variable).

By subtley changing the method signatures to use Expression<Func<T, bool>> predicate, you'll mitigate this issue and will allow for deferred excecution (i.e. processing on sql server etc).

This is because (Expression<Func<>>) creates an expression tree ahead of making the query, and sends this (IQueryable) to the server. This then returns the filtered list. By contrast, when using Func<>, the entire object graph is requested and is post processed in local memory.

I'm no expert on the mechanics of this, but this should help you figure a solution.

A quick comparison reveals:

• the extension methods for IEnumerable operate with Func<T>;
• the extension methods for IQueryable operate with Expression<Func<T>>;

This means that you will only get the deferred IQueryable by using the Expression<Func<T>>. To round off, here's what your problematic method would now look like:

interface:

T GetFirstorDefault(Expression<Func<T, bool>> predicate);

implementation:

public T GetFirstorDefault(Expression<Func<T, bool>> predicate)
{
    return this.Query(predicate).FirstOrDefault();
}

Obviously, all other methods would follow this change, should it prove successful.

Let me know how this pans out for you.

[edit] - added a little link (no pun intended), to give a broad outline of the differences between the two:

• http://ivanitskyi.blogspot.co.uk/2013/06/linq-func-vs-expression.html

Answer 2

Most likely your unitOfWork.City or .GetFirstOrDefault method implementation is enumerating the DbSet<T> prior to applying the filter. If you show more code, we could pinpoint where this is happening.