Where is the sentence not included in the SQL query

Question

I am currently building an application in C # 4.0 with EntityFramework 6.0.

I am trying to get a list of items from a database, but the problem is that the SQL query generated by the EF structure does not include the where clause.

So, the whole table / view is loaded into memory and takes about 10 seconds to get only 2 or 3 elements.

Below is 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 this way:

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

The return result is good, but it takes about 10 seconds. And when the debugging writes the generated SQL query, the where clause is nowhere

How do I change the GetList function to include a where clause?

I hope I was clear enough with this information, and I regret my English. This is not my native language: /

Anyway, thanks for your help.

Answer 1

Change the method signature from

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

to

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

You can still call it lambda, as it is now. Where used as "linq-to-objects", throughout the list that was read from the database. Using Expression, EF can read what sql is needed to generate.

Answer 2

The parameter type is where Func<TEntity, bool> , therefore

 dbQuery.Where(where) 

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

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