Unkown Column `Extent1`.`id` in 'on clause' / EF6 / Entity Framework 6
Posted by:
SuR Data
Date: September 02, 2016 02:59PM
Hi,
I have to change the backend of a project from MS-SQL to MySQL. On MS-SQL-side everthing works fine. On MySQL-side I get the error of the topic.
Setup:
VS 2015 / .NET 4.6
MySQL 5.7.14
Connector 6.9.9
Table-constelation:
A1 (root-table)
A2 (junction-table betwenn A1 and A3 only holding the keys of both)
A3 (collection of A1)
A3MA41 (junction-table between A3 and A41, only holding the keys of both)
A41 (collection of A3)
A3MA42 (junction-table between A3 and A42, only holding the keys of both)
A42 (collection of A3)
The EDMX only sees the tables A1, A3, A41 and A42. The junction-tables are hidden.
A1 is holding A3 as a collection.
A3 is holding A41 and A42 as a collection.
When I try to load them all, I get the mentioned exception:
using (var context = new MyDBContext())
{
var a1 = context.A1.Include(d => d.A3)
.Include(d => d.A3.Select(d2 => d2.A41))
.Include(d => d.A3.Select(d2 => d2.A42))
.FirstOrDefault(w => w.id == 1);
}
When I remove collection A42 from the query, the code will succeed:
using (var context = new MyDBContext())
{
var a1 = context.A1.Include(d => d.A3)
.Include(d => d.A3.Select(d2 => d2.A41))
.FirstOrDefault(w => w.id == 1);
}
EF6 with MySQL seems not to be able to handle more than one collection within a collection. Immediately, when adding a second collection (to be loaded) the code will fail (in any constellation). It seems like the generated SQL-Statement is incompatible with MySQL when it was sent from EF. The interesting part is, that the extracted sql-statement succeed when executed directly on the database. It seems, that EF or Connector internally adds something to the code that makes it fail.
This is the generated SQL-Statement from EF:
SELECT
`Project3`.`id`,
`Project3`.`F1`,
`Project3`.`C14` AS `C1`,
`Project3`.`C2`,
`Project3`.`C3`,
`Project3`.`C4`,
`Project3`.`C5`,
`Project3`.`C6`,
`Project3`.`C7`,
`Project3`.`C1` AS `C8`,
`Project3`.`C8` AS `C9`,
`Project3`.`C9` AS `C10`,
`Project3`.`C10` AS `C11`,
`Project3`.`C11` AS `C12`,
`Project3`.`C12` AS `C13`,
`Project3`.`C13` AS `C14`
FROM (SELECT
`Extent1`.`id`,
`Extent1`.`F1`,
`UnionAll1`.`C1`,
`UnionAll1`.`A1Id` AS `C2`,
`UnionAll1`.`A3Id` AS `C3`,
`UnionAll1`.`A1Id1` AS `C4`,
`UnionAll1`.`Id` AS `C5`,
`UnionAll1`.`F1` AS `C6`,
`UnionAll1`.`Mid` AS `C7`,
`UnionAll1`.`A3Id1` AS `C8`,
`UnionAll1`.`Mid1` AS `C9`,
`UnionAll1`.`F` AS `C10`,
`UnionAll1`.`C2` AS `C11`,
`UnionAll1`.`C3` AS `C12`,
`UnionAll1`.`C4` AS `C13`,
CASE WHEN (`UnionAll1`.`A1Id` IS NULL) THEN (NULL) ELSE (1) END AS `C14`
FROM `A1` AS `Extent1` LEFT OUTER JOIN ((SELECT
CASE WHEN (`Join2`.`A3_Id` IS NULL) THEN (NULL) ELSE (1) END AS `C1`,
`Extent2`.`A1Id`,
`Extent2`.`A3Id`,
`Extent2`.`A1Id` AS `A1Id1`,
`Extent3`.`Id`,
`Extent3`.`F1`,
`Extent3`.`Mid`,
`Join2`.`A3Id` AS `A3Id1`,
`Join2`.`Mid` AS `Mid1`,
`Join2`.`F`,
NULL AS `C2`,
NULL AS `C3`,
NULL AS `C4`
FROM `A2` AS `Extent2` INNER JOIN `A3` AS `Extent3` ON `Extent3`.`Id` = `Extent2`.`A3Id` LEFT OUTER JOIN (SELECT
`Extent4`.`A3_Id`,
`Extent4`.`A41_Id`,
`Extent5`.`A3Id`,
`Extent5`.`Mid`,
`Extent5`.`F`
FROM `A3MA41` AS `Extent4` INNER JOIN `A41` AS `Extent5` ON `Extent5`.`Mid` = `Extent4`.`A41_Id`) AS `Join2` ON `Extent3`.`Id` = `Join2`.`A3_Id`
WHERE 1 = 1) UNION ALL (SELECT
2 AS `C1`,
`Extent6`.`A1Id`,
`Extent6`.`A3Id`,
`Extent6`.`A1Id` AS `A1Id1`,
`Extent7`.`Id`,
`Extent7`.`F1`,
`Extent7`.`Mid`,
NULL AS `C2`,
NULL AS `C3`,
NULL AS `C4`,
`Join5`.`A3Id` AS `A3Id1`,
`Join5`.`Mid` AS `Mid1`,
`Join5`.`F1` AS `F11`
FROM `A2` AS `Extent6` INNER JOIN `A3` AS `Extent7` ON `Extent7`.`Id` = `Extent6`.`A3Id` INNER JOIN (SELECT
`Extent8`.`A3_Id`,
`Extent8`.`A42_Id`,
`Extent9`.`A3Id`,
`Extent9`.`Mid`,
`Extent9`.`F1`
FROM `A3MA42` AS `Extent8` INNER JOIN `A42` AS `Extent9` ON `Extent9`.`Mid` = `Extent8`.`A42_Id`) AS `Join5` ON `Extent7`.`Id` = `Join5`.`A3_Id`
WHERE 1 = 1)) AS `UnionAll1` ON `Extent1`.`id` = `UnionAll1`.`A1Id`) AS `Project3`
ORDER BY
`Project3`.`id` ASC,
`Project3`.`C14` ASC,
`Project3`.`C2` ASC,
`Project3`.`C3` ASC,
`Project3`.`C5` ASC,
`Project3`.`C1` ASC
Hope, somebody can help!
regards