为什么EF为simle查询生成子查询？

There is a simple Linq to EF:

有一个简单的Linq到EF：

var query = from p in _db.Posts
            where p.BlogtId == blogId
            select p;

It generates SQL in this form:

它以这种形式生成SQL：

SELECT
`Extent1`.`PostId`, 
`Extent1`.`BlogId`, 
...
FROM `Posts` AS `Extent1`
 WHERE `Extent1`.`BlogId` = @p__linq__0

But when I add a order by to this query

但是，当我通过此查询添加订单时

var query = from p in _db.Posts
            where p.BlogId == blogId
            orderby p.PublishDate
            select p;

It generates this query

它会生成此查询

SELECT
`Project1`.`PostId`, 
`Project1`.`BlogId`, 
...
FROM (SELECT
`Extent1`.`PostId`, 
`Extent1`.`BlogId`, 
...
FROM `Posts` AS `Extent1`
 WHERE `Extent1`.`BlogId` = @p__linq__0) AS `Project1`
 ORDER BY 
`Project1`.`PublishDate` ASC

Why this generate a sub-query?There is a performance problem for this query in MySQL. MySQL is trying to execute the inner query which pulls back all the records in the database and then tries to sort theme.

为什么会生成子查询？MySQL中的此查询存在性能问题。 MySQL正在尝试执行内部查询，该查询将数据库中的所有记录拉回，然后尝试对主题进行排序。

I need a solution to generate below sql by linq

我需要一个解决方案来生成linq下面的sql

SELECT
`Extent1`.`PostId`, 
...
FROM `Posts` AS `Extent1`
 WHERE `Extent1`.`BlogId` = @p__linq__0
 ORDER BY 
`Extent1`.`PublishDate` ASC

1 个解决方案

#1

This is not an entity-framework issue, despite what your link might tell others. It is in relation to the MySqlConnector/net. I can prove it! Ah, didn't expect that huh.

这不是实体框架问题，尽管您的链接可能告诉其他人。它与MySqlConnector / net有关。我可以证明这一点！啊，没想到那个呵呵。

Hook this exact scenario up using a MSSQL database, with the System.Data connector, and you will see properly formed SQL. This is an issue with projections inside of MySqlConnector. If you want to fix it, then go in and edit it yourself.

使用MSSQL数据库和System.Data连接器将这个确切的场景挂起来，您将看到正确形成的SQL。这是MySqlConnector内部的投影问题。如果你想修复它，那么请自己进行编辑。

Here is how to have a locally edited copy of MySqlConnector/net: How to customize MySql Connector/net?

以下是如何拥有本地编辑的MySqlConnector / net副本：如何自定义MySql Connector / net？

#1