CODEWITHSUNDEEP
c#linqlinq-to-sqllinq-to-entities
Jay
Jay

Reputation: 45

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?

Upvotes: 2

Views: 59

Answers (2)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236328

Why is the difference?

In first case you are loading all data from database and then in-memory filtering with Enumerable.Where(Func<T, bool> predicate) occurs.

In second case you are calling Queryable.Where(Expression<Func<T,bool>> predicate) which is converted to SQL and executed on server side (lambda expression gets converted implicitly to Expression<Func<T,bool>>).

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

Yes, you should pass expression instead of delegate:

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

Just remember - Linq to Entities cannot translate plain C# method into SQL (and delegate is a simple method, just anonymous). Linq to Entities requires expression tree, which it can analyze and build SQL query based on data in tree.

Upvotes: 2

Jon Skeet
Jon Skeet

Reputation: 1503439

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);

Upvotes: 5

Related Questions