Where clause not included in SQL query

Question

I'm currently creating an application in C# 4.0 with EntityFramework 6.0.

I'm trying to retrieve a list of item from the database but the problem is that the SQL query generated by the EF framework doesn't include the where clause.

So, the entire table/view is loaded in memory and it takes about 10 seconds to get just 2 or 3 items.

Below, the method from my GenericRepostitory :

public IList<TEntity> GetList(Func<TEntity, bool> where, params Expression<Func<TEntity, object>>[] navigationProperties)
{
    using (var dbContextScope = contextScopeFactory.CreateReadOnly())
    {
        IQueryable<TEntity> dbQuery = Context.Set<TEntity>().AsQueryable();

        foreach (Expression<Func<TEntity, object>> navigationProperty in navigationProperties)
            dbQuery = dbQuery.Include<TEntity, object>(navigationProperty);

        var list = dbQuery
            .AsNoTracking()
            .Where(where);

        Context.Database.Log = s => Debug.WriteLine(s);

        return list.ToList<TEntity>();
    }
}

And I call it like this :

var repository = repositoryFactory.Get<Context, Entity>();
var items = repository.GetList(x => x.FakeID <= 10); 

The return result is good but it takes around 10 seconds to be retrieved. And when the debug writes the generated SQL query, the where clause is nowhere

How can I modify my function GetList to include the where clause ?

I hope I was enough clear with these informations and I'm sorry for my english. It's not my native language :/

Anyway, thank your for your help

Answer 1

Change your method signature from

GetList(Func<TEntity, bool> where, ...

to

GetList(Expression<Func<TEntity, bool>> where, ...

You can still call it with a lambda, like you do now. The Where was used as "linq-to-objects", over the full list that was read from the database. With the Expression EF can read that to generate the sql needed.

Answer 2

The type of the where parameter is Func<TEntity, bool>, so

dbQuery.Where(where)

uses the Enumerable.Where extension method, loading the data to memory before filtering. If you want to use the Queryable.Where method (which will be translated to SQL) you need an Expression<Func<TEntity, bool>> parameter

public IList<TEntity> GetList(Expression<Func<TEntity, bool>> where, 
                              params Expression<Func<TEntity, object>>[] navigationProperties)