Why EF generating a sub-query for a simle query?

Question

There is a simple Linq to EF:

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

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;

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.

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

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

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.

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