Description:
Wrong Linq to MySQL translation using Visual Studio ASP MVC EF6 with Linq Queries.
Queries running fine if I try to avoid TAKE and SKIP filter methods on related data fetch queries.
http://stackoverflow.com/questions/32790571/asp-mvc-mssql-to-mysql-migration
Confirmed by another user.
How to repeat:
The LINQ Query
------------------
var result = await post.Select(a => new TRDPostViewModel
{
Created = a.Created,
Body = a.Body,
Comments = a.Comments.Select(d => new TRDCommentViewModel
{
Body = d.Body,
Id = d.Id,
}).Where(m => m.Trash == false)
.OrderByDescending(f => f.Created)
.Skip(33) // comment this out the query works
.Take(10) // comment this out the query works
.ToList(),
}).FirstOrDefaultAsync();
The generated SQL Query
-------------------------------
SELECT
`Project3`.`Id`,
`Project3`.`C1`,
`Project3`.`Guid`,
`Project3`.`Created`,
`Project3`.`C3` AS `C2`,
`Project3`.`C2` AS `C3`,
`Project3`.`Body`,
`Project3`.`Created1`,
`Project3`.`Id1`,
`Project3`.`Trash`
FROM (SELECT
`Apply1`.`Id`,
`Apply1`.`Guid`,
`Apply1`.`Created`,
`Apply1`.`C1`,
`Apply1`.`ID1` AS `Id1`,
`Apply1`.`Body`,
`Apply1`.`CREATED1` AS `Created1`,
`Apply1`.`Trash`,
`Apply1`.`C11` AS `C2`,
CASE WHEN (`Apply1`.`ID1` IS NULL) THEN (NULL) ELSE (1) END AS `C3`
FROM (SELECT
`Limit1`.`Id`,
`Limit1`.`Guid`,
`Limit1`.`Created`,
`Limit1`.`C1`,
(SELECT
`Project2`.`Id`
FROM (SELECT
`Extent2`.`Id`,
`Extent2`.`Body`,
`Extent2`.`Created`,
`Extent2`.`Trash`,
1 AS `C1`
FROM `TRDComments` AS `Extent2`
WHERE `Extent1`.`Id` = `Extent2`.`PostId`) AS `Project2`
ORDER BY
`Project2`.`Created` DESC LIMIT 99,101) AS `ID1`,
(SELECT
`Project2`.`Body`
FROM (SELECT
`Extent2`.`Id`,
`Extent2`.`Body`,
`Extent2`.`Created`,
`Extent2`.`Trash`,
1 AS `C1`
FROM `TRDComments` AS `Extent2`
WHERE `Extent1`.`Id` = `Extent2`.`PostId`) AS `Project2`
ORDER BY
`Project2`.`Created` DESC LIMIT 99,101) AS `Body`,
(SELECT
`Project2`.`Created`
FROM (SELECT
`Extent2`.`Id`,
`Extent2`.`Body`,
`Extent2`.`Created`,
`Extent2`.`Trash`,
1 AS `C1`
FROM `TRDComments` AS `Extent2`
WHERE `Extent1`.`Id` = `Extent2`.`PostId`) AS `Project2`
ORDER BY
`Project2`.`Created` DESC LIMIT 99,101) AS `CREATED1`,
(SELECT
`Project2`.`Trash`
FROM (SELECT
`Extent2`.`Id`,
`Extent2`.`Body`,
`Extent2`.`Created`,
`Extent2`.`Trash`,
1 AS `C1`
FROM `TRDComments` AS `Extent2`
WHERE `Extent1`.`Id` = `Extent2`.`PostId`) AS `Project2`
ORDER BY
`Project2`.`Created` DESC LIMIT 99,101) AS `Trash`,
(SELECT
`Project2`.`C1`
FROM (SELECT
`Extent2`.`Id`,
`Extent2`.`Body`,
`Extent2`.`Created`,
`Extent2`.`Trash`,
1 AS `C1`
FROM `TRDComments` AS `Extent2`
WHERE `Extent1`.`Id` = `Extent2`.`PostId`) AS `Project2`
ORDER BY
`Project2`.`Created` DESC LIMIT 99,101) AS `C11`
FROM (SELECT
`Extent1`.`Id`,
`Extent1`.`Guid`,
`Extent1`.`Created`,
1 AS `C1`
FROM `TRDPosts` AS `Extent1` LIMIT 1) AS `Limit1`) AS `Apply1`) AS `Project3`
ORDER BY
`Project3`.`Id` ASC,
`Project3`.`C3` ASC
DDL
--------------
CREATE TABLE `trdposts` (
`Id` bigint(20) NOT NULL AUTO_INCREMENT,
`Guid` varchar(36) CHARACTER SET utf8 DEFAULT NULL,
`Url` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`Body` varchar(2048) CHARACTER SET utf8mb4 DEFAULT NULL,
`title` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`Trash` tinyint(1) NOT NULL,
`Location` geometry DEFAULT NULL,
`Privacy` int(11) NOT NULL,
`PostType` int(11) NOT NULL,
`UserId` varchar(128) CHARACTER SET utf8 NOT NULL,
`Created` datetime NOT NULL,
`Modified` datetime DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `IX_UserId` (`UserId`) USING HASH,
CONSTRAINT `FK_TRDPosts_TRDUsers_UserId` FOREIGN KEY (`UserId`) REFERENCES `trdusers` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `trdcomments` (
`Id` bigint(20) NOT NULL AUTO_INCREMENT,
`body` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`Created` datetime NOT NULL,
`UserId` varchar(128) CHARACTER SET utf8 NOT NULL,
`PostId` bigint(20) NOT NULL,
`Location` geometry DEFAULT NULL,
`Trash` tinyint(1) NOT NULL,
`TRDImage_Id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `IX_UserId` (`UserId`) USING HASH,
KEY `IX_PostId` (`PostId`) USING HASH,
KEY `IX_TRDImage_Id` (`TRDImage_Id`) USING HASH,
CONSTRAINT `FK_TRDComments_TRDImages_TRDImage_Id` FOREIGN KEY (`TRDImage_Id`) REFERENCES `trdimages` (`Id`),
CONSTRAINT `FK_TRDComments_TRDPosts_PostId` FOREIGN KEY (`PostId`) REFERENCES `trdposts` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_TRDComments_TRDUsers_UserId` FOREIGN KEY (`UserId`) REFERENCES `trdusers` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=302 DEFAULT CHARSET=utf8mb4;
Suggested fix:
none yet