LINQ query performance issue

Question

I have the following LINQ expression in method syntax

IEnumerable<PageElement> elements_test = ObjectContext.PageElements
            .Where(_dateDebutCheck).Where(_dateFinCheck)
            .Where(pe => _activeLanguageCheck(pe, language))
            .Where(pe => _typeCheck(pe, typeElement))
IList<PageElement> list = elements_test.ToList();

private readonly Func<PageElement, bool> _dateDebutCheck = pe => pe.DateDebut.HasValue && pe.DateDebut.Value <= DateTime.Now;
private readonly Func<PageElement, bool> _dateFinCheck = pe => !pe.DateFin.HasValue || pe.DateFin.Value > DateTime.Now;
private readonly Func<PageElement, byte, bool> _activeLanguageCheck = (pe, l) => pe.PageElementLanguages.Where(y => y.Active).Select(y => y.LanguageId).Contains(l);
private readonly Func<PageElement, byte?, bool> _typeCheck = (pe, t) => pe.TypeId == t;

I am finding that when the call to ToList is taking quite a while and am wondering if there is anything that I am doing wrong that is causing the performance drop. I am only running the ToList at this point as a test but it is taking several seconds to return on only about 7000 records. How can I improve this?

Answer 1

It is slow, because you are no longer doing the querying in database, but in memory. It pulls all data from PageElements table and then filters it in memory. The reason for this is that you are using Func<> delegates, which forces LINQ to use Enumerable instead of Queryable. To fix this use Expression<Func<>> instead.

But I would recommend against trying to compose your queries the way you are doing it. It makes queries harder to read and fine-tune. Especially if you expect some kind of SQL to come out of it.

Maybe you could do it through extension methods :

// needs to be in static class
public static IQueryable<PageElement> TypeCheck(this IQueryable<PageElement> q, byte? typeElement){
    return q.Where(pe=>pe.TypeID == t); // also this might not work is typeElement is null
}

Then you can call it as

ObjectContext.PageElement.TypeCheck(typeElement).ToList(); // etc..