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

Options: ReplyQuote


Subject
Written By
Posted
Unkown Column `Extent1`.`id` in 'on clause' / EF6 / Entity Framework 6
September 02, 2016 02:59PM


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.