Bug #78610 Fetching Related Data Entities via LINQ / Filtering
Submitted: 28 Sep 2015 20:59 Modified: 29 Sep 2015 6:04
Reporter: marko doering Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.9.7 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: #mysql #net #connector #linq #query

[28 Sep 2015 20:59] marko doering
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
[29 Sep 2015 6:04] Chiranjeevi Battula
Hello marko doering,

Thank you for the bug report.
This is most likely duplicate of Bug #76663, please see Bug #76663

Thanks,
Chiranjeevi.