Entity Framework not sending Where clauses as WHERE clauses to SQL Server

Question

I have a simple DB that has Sites, and each Site has a bunch of Posts.

I'm trying to get all the "Public" Posts of a certain Site (I have a variable called site that is already an instance brought by EF)

The first obvious thing is:

  var posts = from post in site.Posts
              where post.Public == true
              orderby post.PublicationTime descending
              select post;

This brings me what I want, but looking into SQL Server Profiler, the WHERE only is filtering the Public field, not the Site. In fact, running in SQL Server the query that Profiler captures indeed brings back all the posts from all the sites (and this is obviously being filtered in the ASP.Net side later).

Then I tried:

  var posts = from post in db.Posts
              where post.Site == site && post.Public == true
              orderby post.PublicationTime descending
              select post;

Same result.

Am I doing something fundamentally stupid here?
Does Entity Framework ALWAYS filter in the client-side?

Thanks!
Daniel

Site is a Model Entity, with a relationship to Posts, where one Site has many Posts — Daniel Magliola, Feb 11 '10 at 02:56
Does this answer your question? [diffrence between running query with Func and Expression Func](https://stackoverflow.com/questions/51533980/diffrence-between-running-query-with-func-and-expression-func) — GSerg, Oct 15 '20 at 16:43

Craig Stuntz · Answer 1 · 2010-02-11T14:19:19.600

You need to understand the difference between LINQ to Entities and LINQ to Objects. It is incredibly important to keep track of this when working with the Entity Framework.

When you issue a query against an ObjectContext, then you are working in LINQ to Entities. This will return an IQueryable. As long as you are working with a variable of type IQueryable, you can further compose the query with the LINQ API, and when you finally enumerate the result, it will be converted to SQL.

But you say:

(I have a variable called site that is already an instance brought by EF)

Here you are querying a property of an object, so you are working in LINQ to Objects, not LINQ to Entities. This means that your query has a different provider, and will not be converted to SQL.

Regarding your second query:

var posts = from post in db.Posts
            where post.Site == site && post.Public == true
            orderby post.PublicationTime descending
            select post;

The EF doesn't let you do identity comparisons on instances in L2E. You have to compare the key instead. Try:

var posts = from post in db.Posts
            where post.Site.Id == site.Id && post.Public
            orderby post.PublicationTime descending
            select post;

BTW, I changed post.Public == true to post.Public. I think it's cleaner.

score 8 · Answer 2 · answered Jan 31 '17 at 16:21

In case anyone has a problem with this using method syntax:

If you pass a Func<TEntity,Boolean> to the .Where method, the filter function is applied after the query returns from the database. This is because the return value of the .Where method returns an IEnumerable. On the other hand, if you pass an Expression<Func<TEntity,Boolean> to the .Where method, the filter function generates a where clause that is sent to the database. This is because the .Where returns an IQueryable. As long as you stick with IQueryables you are building the query to send to the database. When you return an IEnumerable, everything before the method is used to create the query and everything after the IEnumerable is applied to what is brought back. This only really matters if you are storing your lambda function in a variable. If you pass the lambda directly into the .Where method, you generally don't have an issue. Hope this helps!!

IEnumerable Where: https://msdn.microsoft.com/en-us/library/bb549418(v=vs.110).aspx

IQueryable Where: https://msdn.microsoft.com/en-us/library/bb535040(v=vs.110).aspx

Entity Framework not sending Where clauses as WHERE clauses to SQL Server

2 Answers2

Linked