Difference in generated SQL

The following c# code:

Func<Customer, bool> predicate1 = s => s.Name == "Roger";
dbContext.Customers.Where(predicate1);

generates this sql query:

select col1,col2 from customers

Note in the sql query above, no where clause.

However, in this c# code:

dbContext.Customers.Where(s => s.Name == "Roger");

it generates:

select col1,col2 from customers where name = 'Rogers'

Why is the difference? And is there a way to pass a predicate like above and still generate sql query with a where clause?

Jon Skeet
people
quotationmark

The difference is that in the first case you're calling Enumerable.Where with a delegate (Func<Customer, bool>). The LINQ provider itself doesn't see that at all - Enumerable.Where will just iterate over the whole of the dbContext.Customers table, and apply the predicate in-process.

In the second case you're calling Queryable.Where with an expression tree. The LINQ provider can analyze this "code as data" and translate it into SQL.

This will do what you want:

Expression<Func<Customer, bool>> predicate1 = s => s.Name == "Roger";
dbContext.Customers.Where(predicate1);

people

See more on this question at Stackoverflow