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

Answer 1

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.