为什么EF为简单查询生成子查询? [英] Why EF generating a sub-query for a simle query?

问题描述

EF有一个简单的Linq:

There is a simple Linq to EF:

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

它以这种形式生成SQL:

It generates SQL in this form:

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;

它生成此查询

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

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

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.

我需要一种通过linq在sql下生成的解决方案

I need a solution to generate below sql by linq

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

推荐答案

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

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.

使用带有System.Data连接器的MSSQL数据库查找此精确方案，您将看到格式正确的SQL.这是MySqlConnector内部的投影问题.如果您要修复它，那么请自己进行编辑.

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.

以下是如何在本地编辑MySqlConnector/net的副本:如何自定义MySql Connector/net?

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

这篇关于为什么EF为简单查询生成子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！