Entity Framework Core Not Generating Where Clause When Using LINQ and predicate

Question

Mistakenly labelled duplicate - see answer below

Basic setup - I have an application context and an abstraction built to serve as the DAO:

SomeEntity:

public class SomeEntity
{
    public string MyProp { get; set; }
}

DbContext:

public class ApplicationContext : DbContext
{
    public DbSet<SomeEntity> SomeEntities { get; set; }
    /* Rest of the DbContext doesn't matter. */
}

DAO:

public class DAO
{
    private readonly DbSet<SomeEntity> _dbSet;

    public DAO(ApplicationContext context)
    {
        _dbSet = context.SomeEntities;
    }

    public IEnumerable<SomeEntity> Where(Func<SomeEntity, bool> predicate)
    {
        return _dbSet.Where(predicate);
    }
}

Usage:

Dao dao = new Dao(/* whatever for instantiation */);
var results = dao.Where(e => e.MyProp == "my string");

Expected behavior: I expect EF Core to generate a SQL query like:

SELECT [e].MyProp 
FROM [TABLE_NAME] AS [e]
WHERE [e].MyProp = 'my string'

Actual behavior: EF Core generates the following SQL query:

SELECT [e].MyProp 
FROM [TABLE_NAME] as [e]

It omits the where clause causing the application to pull every record into memory before filtering.

Why?

@GSerg - Yes - it is related! Thank you for the reference, wish I would have found that two days ago! — Kyle L., Oct 15 '20 at 16:47
Stop marking it as something to be closed or duplicate - No other questions pop up when searching for "EF Core Missing Where Clause", this is intended to be able to be searched for rather than it being some obscure question deep inside EF Core. — Kyle L., Oct 15 '20 at 16:55
This subject is not new and has been discussed many times, along with the [floating point math](https://stackoverflow.com/q/588004/11683) and [sql injections](https://stackoverflow.com/q/332365/11683). I personally don't think it deserves a new separate self-answered question, you think otherwise, no problem. You are saying yourself that the other question covers the issue, that would be enough of a reason to agree with the close vote. [...] — GSerg, Oct 15 '20 at 17:11
[...] We [love duplicates](https://stackoverflow.com/help/duplicates) around here. Closing a question as a duplicate is not punishment. The question itself is not deleted and serves very well to increase the variety of keywords using which it is possible to find the original content. — GSerg, Oct 15 '20 at 17:12
The question linked has 0 references to EF or any of its variants and is poorly tagged with anything meaningful. The Selected Answer itself goes into 0 detail about why getting `Where` to return `IQueryable` in .NET is the solution. The second answer is more fitting but I still wouldn't be able to infer what they are talking about were I not already well versed in the problem. This new question (and answer) clearly relates the usage of EF Core and .NET Core's LINQ in a useful way - I would say that marking as a duplicate and sending folks to the "original" question which has the problems — Kyle L., Oct 15 '20 at 17:21
... mentioned is probably non-productive to any and everyone involved in the question asking process. — Kyle L., Oct 15 '20 at 17:21
This problem is not specific to EF Core, it has been around [since Linq2Sql](https://stackoverflow.com/questions/40158438/migrating-linq-to-sql-code-to-net-core#comment89343777_45580126). I picked this particular question because it itself is a duplicate of two other questions, conveniently placed at its top: https://stackoverflow.com/q/793571/11683 and https://stackoverflow.com/q/252785/11683, which nicely brings you to https://stackoverflow.com/a/34606818/11683. — GSerg, Oct 15 '20 at 17:34
Of course it has nothing to do with EF (Core). `Where(Func)` resolves to `Enumerable.Where`, hence will be executed by Linq To Objects regardless of the source queryable/enumerable. — Ivan Stoev, Oct 15 '20 at 23:53

Kyle L. · Accepted Answer · 2020-10-15T16:37:46.790

The problem lies in the fact that you are passing a Func<SomeEntity, bool> to LINQ's Where method as the predicate. When you pass a Func to Where it returns an IEnumerable.

When you tell EF Core to return an IEnumerable what you are telling it is that you are done querying the data set and want to enumerate the results. Therefore, it generates a query without the proper server-side WHERE clause and pulls all your data in at once.

Instead of accepting a Func as a parameter in your DAO class, accept an Expression<Func<SomeEntity, bool>> as when you pass an Expression to LINQ's Where method it will return an IQueryable rather than an IEnumerable. This does two things:

Allows you to add additional operations to the query before execution.
Tells EF Core that you want to filter records in SQL before returning the result set to the application.

So instead of

public IEnumerable<SomeEntity> Where(Func<SomeEntity, bool> predicate)
{
    return _dbSet.Where(predicate);
}

it should be

public IEnumerable<SomeEntity> Where(Expression<Func<SomeEntity, bool>> predicate)
{
    return _dbSet.Where(predicate);
}

Usage:

Dao dao = new Dao(/* whatever for instantiation */);
var results = dao.Where(e => e.MyProp == "my string");

Resultant query:

SELECT [e].MyProp 
FROM [TABLE_NAME] as [e]
WHERE [e].MyProp = 'my string'

Notice how the implementation of the method didn't actually change at all and neither did the usage - This is because certain forms of Func and Expression are interchangeable and using one or the other here in this case results in no compilation errors and so becomes fundamentally the same.

I found this answer through many hours of looking through SO posts and finally figured it out after reading https://stackoverflow.com/a/41962380/2573572. Reposting it here with a question that is more inline with the answer as the original question was not related to the issue.

You could have found the answer much quicker if you have just looked at the signature of the `Queryble.Where` (and other `Queryable`) extension method(s). `Expression>` vs `Func<...>` is the fundamental difference between `Queryable` and `Enumerable` LINQ queries. — Ivan Stoev, Oct 15 '20 at 23:55
For people not readily invested in .NET-isms, the fact that something returns an IQueryable vs a IEnumerable is not enough of a leap for folks to associate that with not building a SQL query. Thanks for your input. — Kyle L., Oct 19 '20 at 13:03

Entity Framework Core Not Generating Where Clause When Using LINQ and predicate

1 Answers1