Why does EF generate a subquery for a simle request?

Question

There is a simple Linq for 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 an order for this request

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

It generates this request

 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 does this generate a subquery? There is a performance issue for this query in MySQL. MySQL tries to execute an internal query that discards all records in the database, and then tries to sort the topic.

I need a solution to create below sql 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 structure problem, even though your link may tell others. This is due to MySqlConnector / net. I can prove it! Ah, I didn’t expect that.

Connect this exact script using the MSSQL database with the System.Data connector and you will see correctly formed SQL. This is a projection issue inside MySqlConnector. If you want to fix this, then go in and edit it yourself.

Here's how to get a locally edited copy of MySqlConnector / net: How do I configure MySql Connector / net?