实体框架不将Where子句作为WHERE子句发送到SQL Server [英] Entity Framework not sending Where clauses as WHERE clauses to SQL Server

问题描述

我有一个具有站点的简单数据库，并且每个站点都有一堆帖子。

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

我正在尝试获取一个站点的所有公共帖子某些站点（我有一个名为site的变量，它已经是EF带来的实例）

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)

第一件事很明显：

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

这给我带来了我想要的东西，但是在SQL Server Profiler中，WHERE仅在过滤公共字段，而不是网站。实际上，在SQL Server中运行，探查器捕获的查询确实带回了所有站点的所有帖子（并且稍后显然在ASP.Net端对其进行了过滤。）

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

然后我尝试了

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

相同的结果。

我在做什么

实体框架是否总是在客户端进行过滤？

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

谢谢！

Daniel
p>

Thanks!
Daniel

推荐答案

您需要了解LINQ to Entities和LINQ to Objects之间的区别。在使用实体框架时，保持这一点非常重要。

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.

当您对ObjectContext发出查询时，您正在LINQ to Entities中工作。这将返回一个IQueryable。只要使用IQueryable类型的变量，就可以使用LINQ API进一步构成查询，并在最终枚举结果时将其转换为SQL。

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.

但是你说：

（我有一个名为site的变量，它已经是EF带来的一个实例）

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

这里您要查询对象的属性，因此您正在使用LINQ to Objects，而不是LINQ to Entities。这意味着您的查询具有不同的提供程序，并且不会转换为SQL。

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.

关于第二个查询：

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

EF不允许您对L2E中的实例进行身份比较。您必须改为比较密钥。尝试：

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;

顺便说一句，我更改了 post.Public == true 到 post.Public 。我认为它更干净。

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

这篇关于实体框架不将Where子句作为WHERE子句发送到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！