Problems with Connector 6.9.7 and Find / invalid SQL
Posted by: Andrew Holland
Date: August 23, 2015 02:56PM

Hi,

Pretty new to Entity Framework but not to MySQL but I'm having problems with a simple EF operation - Find - when using MySQL (connector 6.9.7), I'm using Visual Studio 2015, with an MVC application using EF6.

This Find:

News.Find(1)

produces this invalid SQL:

SELECT
`Limit1`.`C2` AS `C1`,
`Limit1`.`C1` AS `C2`,
`Limit1`.`Heading`,
`Limit1`.`MainDetail`,
`Limit1`.`Active`,
`Limit1`.`ExpiryDate`,
`Limit1`.`EmbargoDate`,
`Limit1`.`CreatedOn`,
`Limit1`.`ModifiedOn`,
`Limit1`.`C3`,
`Limit1`.`C4`,
`Limit1`.`C5`,
`Limit1`.`C6`,
`Limit1`.`C7`,
`Limit1`.`C8`,
`Limit1`.`C9`,
`Limit1`.`C10`,
`Limit1`.`C11`
FROM (SELECT
`UnionAll2`.`ContentID` AS `C1`,
`Extent4`.`Heading`,
`Extent4`.`MainDetail`,
`Extent4`.`Active`,
`Extent4`.`ExpiryDate`,
`Extent4`.`EmbargoDate`,
`Extent4`.`CreatedOn`,
`Extent4`.`ModifiedOn`,
CASE WHEN (`UnionAll2`.`C5` = 1) THEN (@gp1) WHEN (`UnionAll2`.`C6` = 1) THEN (@gp2) ELSE (@gp3) END AS `C2`,
CASE WHEN (`UnionAll2`.`C5` = 1) THEN (`UnionAll2`.`StartDate`) WHEN (`UnionAll2`.`C6` = 1) THEN (NULL) END AS `C3`,
CASE WHEN (`UnionAll2`.`C5` = 1) THEN (`UnionAll2`.`StartTime`) WHEN (`UnionAll2`.`C6` = 1) THEN (NULL) END AS `C4`,
CASE WHEN (`UnionAll2`.`C5` = 1) THEN (`UnionAll2`.`EndDate`) WHEN (`UnionAll2`.`C6` = 1) THEN (NULL) END AS `C5`,
CASE WHEN (`UnionAll2`.`C5` = 1) THEN (`UnionAll2`.`EndTime`) WHEN (`UnionAll2`.`C6` = 1) THEN (NULL) END AS `C6`,
CASE WHEN (`UnionAll2`.`C5` = 1) THEN (`UnionAll2`.`ExernalLink`) WHEN (`UnionAll2`.`C6` = 1) THEN (NULL) END AS `C7`,
CASE WHEN (`UnionAll2`.`C5` = 1) THEN (NULL) WHEN (`UnionAll2`.`C6` = 1) THEN (`UnionAll2`.`C1`) END AS `C8`,
CASE WHEN (`UnionAll2`.`C5` = 1) THEN (NULL) WHEN (`UnionAll2`.`C6` = 1) THEN (NULL) ELSE (`UnionAll2`.`C2`) END AS `C9`,
CASE WHEN (`UnionAll2`.`C5` = 1) THEN (NULL) WHEN (`UnionAll2`.`C6` = 1) THEN (NULL) ELSE (`UnionAll2`.`C3`) END AS `C10`,
CASE WHEN (`UnionAll2`.`C5` = 1) THEN (NULL) WHEN (`UnionAll2`.`C6` = 1) THEN (NULL) ELSE (`UnionAll2`.`C4`) END AS `C11`
FROM ((SELECT
FROM ((SELECT
`Extent1`.`ContentID`,
`Extent1`.`StartDate`,
`Extent1`.`StartTime`,
`Extent1`.`EndDate`,
`Extent1`.`EndTime`,
`Extent1`.`ExernalLink`,
NULL AS `C1`,
NULL AS `C2`,
NULL AS `C3`,
NULL AS `C4`,
1 AS `C5`,
0 AS `C6`
FROM `Activities` AS `Extent1`) UNION ALL (SELECT
`Extent2`.`ContentID`,
NULL AS `C1`,
NULL AS `C2`,
NULL AS `C3`,
NULL AS `C4`,
NULL AS `C5`,
`Extent2`.`Summary`,
NULL AS `C6`,
NULL AS `C7`,
NULL AS `C8`,
0 AS `C9`,
1 AS `C10`
FROM `News` AS `Extent2`))) UNION ALL (SELECT
`Extent3`.`ContentID`,
NULL AS `C1`,
NULL AS `C2`,
NULL AS `C3`,
NULL AS `C4`,
NULL AS `C5`,
NULL AS `C6`,
`Extent3`.`StartTime`,
`Extent3`.`EndTime`,
`Extent3`.`daysOfTheWeek`,
0 AS `C7`,
0 AS `C8`
FROM `Shows` AS `Extent3`)) AS `UnionAll2` INNER JOIN `Content` AS `Extent4` ON `UnionAll2`.`ContentID` = `Extent4`.`ContentID`
WHERE `UnionAll2`.`ContentID` = @p0 LIMIT 2) AS `Limit1`

But doing this:

News.First(x => x.ContentID == 1)

Produces valid SQL of:

SELECT
`Limit1`.`C1`,
`Limit1`.`ContentID`,
`Limit1`.`Heading`,
`Limit1`.`MainDetail`,
`Limit1`.`Active`,
`Limit1`.`ExpiryDate`,
`Limit1`.`EmbargoDate`,
`Limit1`.`CreatedOn`,
`Limit1`.`ModifiedOn`,
`Limit1`.`Summary`
FROM (SELECT
`Extent1`.`ContentID`,
`Extent1`.`Summary`,
`Extent2`.`Heading`,
`Extent2`.`MainDetail`,
`Extent2`.`Active`,
`Extent2`.`ExpiryDate`,
`Extent2`.`EmbargoDate`,
`Extent2`.`CreatedOn`,
`Extent2`.`ModifiedOn`,
@gp1 AS `C1`
FROM `News` AS `Extent1` INNER JOIN `Content` AS `Extent2` ON `Extent1`.`ContentID` = `Extent2`.`ContentID`
WHERE 1 = `Extent1`.`ContentID` LIMIT 1) AS `Limit1`

And doing this:

News.ToList().Find(x => x.ContentID == 1)

Results in even better SQL of:

SELECT
@gp1 AS `C1`,
`Extent1`.`ContentID`,
`Extent2`.`Heading`,
`Extent2`.`MainDetail`,
`Extent2`.`Active`,
`Extent2`.`ExpiryDate`,
`Extent2`.`EmbargoDate`,
`Extent2`.`CreatedOn`,
`Extent2`.`ModifiedOn`,
`Extent1`.`Summary`
FROM `News` AS `Extent1` INNER JOIN `Content` AS `Extent2` ON `Extent1`.`ContentID` = `Extent2`.`ContentID`


So I can work around the problem by using either the second or third approach instead but I'd like to know why the Find isn't working, is this a bug in the connector (I'm assuming it must be since the problem does not occur when using Sql Server) and this be fixed please?

Options: ReplyQuote


Subject
Written By
Posted
Problems with Connector 6.9.7 and Find / invalid SQL
August 23, 2015 02:56PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.