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));
unitWork.Cityand theGetFirstorDefaultmethodFunc<T, bool> predicatenegates deferred execution. i THINK you need to replace that withExpression<Func<T, bool>> predicate. just my thoughts from a dream no doubt :-)