1

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));
5
  • 1
    You should show us your Linq code Commented Nov 28, 2013 at 16:07
  • show the implementation of unitWork.City and the GetFirstorDefault method Commented Nov 28, 2013 at 16:42
  • i may be wrong and sending you down a blind alley. however, i THINK that the use of Func<T, bool> predicate negates deferred execution. i THINK you need to replace that with Expression<Func<T, bool>> predicate. just my thoughts from a dream no doubt :-) Commented Nov 29, 2013 at 11:02
  • curious to know if this helped in any way at all, as i honestly reckon it would have :-) Commented Dec 5, 2013 at 21:21
  • Excuse me, actually I'm following other project. I will reply as soon as possible. Commented Dec 6, 2013 at 14:28

2 Answers 2

3

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:

Sign up to request clarification or add additional context in comments.

Comments

0

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.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.