linqentity-frameworkobjectset

Why does hard coding a function expression speed up query by four minutes?


I am using Dane Morgridge's repository code as a parent to my repository class. In the parent class--EFRepository--there is a method that calls an ObjectSet's Where clause passing in a Func method. Upon calling this code and then assigning it to my grid, the process takes 4 minutes. However, if I hard code the call to the ObjectSet's Where it only takes three seconds. Any ideas why? It seems like the compiler is messing it up somehow.

private void button1_Click(object sender, RoutedEventArgs e)
    {                        
        IQueryable<PRODDATA> testP = test.Repository.Find(w => w.PCUST == 49 && w.PDTTK == 20101030); 

        DateTime firstDate = System.DateTime.Now;
//This is where it takes the most time when passing in the expression above. When the espression is hardcoded (see below) it speeds it up considerably.
        radGridView1.ItemsSource = testP;
        DateTime secondDate = System.DateTime.Now;                                   

    }

public class EFRepository<T> : IRepository<T> where T : PRODDATA
{
    public IUnitOfWork UnitOfWork { get; set; }
    private IObjectSet<T> _objectset;
    private IObjectSet<T> ObjectSet
    {
        get
        {
            if (_objectset == null)
            {
                _objectset = UnitOfWork.Context.CreateObjectSet<T>();
            }
            return _objectset;
        }
    }

    public virtual IQueryable<T> All()
    {
        return ObjectSet.AsQueryable();
    }

    public IQueryable<T> Find(Func<T, bool> expression)
    {
//Hardcoding this only takes 2 seconds.
        //return ObjectSet.Where(w => w.PCUST == 49 && w.PDTTK == 20101030).AsQueryable(); 

//passing expression takes 4 minutes.
        return ObjectSet.Where(expression).AsQueryable(); 
    }

    public void Add(T entity)
    {
        ObjectSet.AddObject(entity);
    }

    public void Delete(T entity)
    {
        ObjectSet.DeleteObject(entity);
    }

    public void Save()
    {
        UnitOfWork.Save();
    }
}

Solution

  • Because Find takes a Func<T,bool> instead of an Expression<Func<T,bool>>. Presumably, this query is being sent to a DB engine, since it is operating on an IQueryable<T>. But if the expression is passed as a delegate and not a true expression, the LINQ-to-whatever-DB layer is not able to inspect the expression and turn it into SQL. This is resulting in the entire data set being sent from the db server to the C# code, where the "where" expression is then applied in the CLR instead of in the DB.

    Change the signature of Find from

    public IQueryable<T> Find(Func<T, bool> expression)
    

    to

    public IQueryable<T> Find(Expression<Func<T, bool>> expression)
    

    Then see how it performs.