Description:
I have three tables like this:
CREATE TABLE `eftest`.`left` (
`ID` INT NOT NULL AUTO_INCREMENT,
`Item` varchar(45) DEFAULT NULL,
PRIMARY KEY (`ID`))
CREATE TABLE `eftest`.`middle` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`LeftId` INT NULL ,
PRIMARY KEY (`ID`) ,
INDEX `FK_MIDDLE_LEFT_idx` (`LeftId` ASC) ,
CONSTRAINT `FK_MIDDLE_LEFT`
FOREIGN KEY (`LeftId` )
REFERENCES `eftest`.`left` (`ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION);
CREATE TABLE `eftest`.`right` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`MiddleId` INT NOT NULL ,
PRIMARY KEY (`ID`) ,
INDEX `FK_RIGHT_MIDDLE_idx` (`MiddleId` ASC) ,
CONSTRAINT `FK_RIGHT_MIDDLE`
FOREIGN KEY (`MiddleId` )
REFERENCES `eftest`.`middle` (`ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
So a `right` points to a `middle`, and a `middle` points to a `left`.
In my setup, a `left` is always referenced by exactly a single `middle`, so I have a one-to-one relationship here.
I import these tables to Entity Framework Model Designer in Visual Studio 2013 by "Update Model from Database". I have chosen "Pluralize or singuralize generated object names" but not "Include foreign key columns in the model" (don't know if this really matters).
I changed "middles" navigation property on "left" from "* (Many)" to "1 (One)". I also renamed the navigation property to "middle".
This doesn't work:
using (var db = new eftestEntities()) {
db.rights.Where(r => r.ID > 0).Select(r => new { lid = r.middle.left.ID, r.middle.left.Item }).ToArray();
}
This query is generated in EF 6.1 together with MySql Connector .Net 6.8.3:
SELECT
`Apply1`.`ID`,
`Apply1`.`LeftId`,
`Apply1`.`Item`
FROM (SELECT
`Filter1`.`ID`,
`Filter1`.`MiddleId`,
`Filter1`.`ID1`,
`Filter1`.`LeftId`,
(SELECT
`Extent3`.`ID`
FROM `left` AS `Extent3` LEFT OUTER JOIN `middle` AS `Extent4` ON (`Extent4`.`LeftId` IS NOT NULL) AND (`Extent3`.`ID` = `Extent4`.`LeftId`) INNER JOIN `middle` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`ID`
WHERE `Extent1`.`MiddleId` = `Extent5`.`ID`) AS `ID2`,
(SELECT
`Extent3`.`Item`
FROM `left` AS `Extent3` LEFT OUTER JOIN `middle` AS `Extent4` ON (`Extent4`.`LeftId` IS NOT NULL) AND (`Extent3`.`ID` = `Extent4`.`LeftId`) INNER JOIN `middle` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`ID`
WHERE `Extent1`.`MiddleId` = `Extent5`.`ID`) AS `Item`,
(SELECT
`Extent4`.`ID` AS `ID1`
FROM `left` AS `Extent3` LEFT OUTER JOIN `middle` AS `Extent4` ON (`Extent4`.`LeftId` IS NOT NULL) AND (`Extent3`.`ID` = `Extent4`.`LeftId`) INNER JOIN `middle` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`ID`
WHERE `Extent1`.`MiddleId` = `Extent5`.`ID`) AS `ID3`,
(SELECT
`Extent4`.`LeftId`
FROM `left` AS `Extent3` LEFT OUTER JOIN `middle` AS `Extent4` ON (`Extent4`.`LeftId` IS NOT NULL) AND (`Extent3`.`ID` = `Extent4`.`LeftId`) INNER JOIN `middle` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`ID`
WHERE `Extent1`.`MiddleId` = `Extent5`.`ID`) AS `LEFTID1`,
(SELECT
`Extent5`.`ID` AS `ID2`
FROM `left` AS `Extent3` LEFT OUTER JOIN `middle` AS `Extent4` ON (`Extent4`.`LeftId` IS NOT NULL) AND (`Extent3`.`ID` = `Extent4`.`LeftId`) INNER JOIN `middle` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`ID`
WHERE `Extent1`.`MiddleId` = `Extent5`.`ID`) AS `ID4`,
(SELECT
`Extent5`.`LeftId` AS `LEFTID1`
FROM `left` AS `Extent3` LEFT OUTER JOIN `middle` AS `Extent4` ON (`Extent4`.`LeftId` IS NOT NULL) AND (`Extent3`.`ID` = `Extent4`.`LeftId`) INNER JOIN `middle` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`ID`
WHERE `Extent1`.`MiddleId` = `Extent5`.`ID`) AS `LEFTID2`
FROM (SELECT
`Extent1`.`ID`,
`Extent1`.`MiddleId`,
`Extent2`.`ID` AS `ID1`,
`Extent2`.`LeftId`
FROM `right` AS `Extent1` INNER JOIN `middle` AS `Extent2` ON `Extent1`.`MiddleId` = `Extent2`.`ID`
WHERE `Extent1`.`ID` > 0) AS `Filter1`) AS `Apply1`
with the error message: "Unknown column 'Extent1.MiddleId' in 'where clause'"
The error occurs because `Extent1` is declared inside a subquery, and can thus not be used outside that subquery. It doesn't matter if I use ObjectContext or DbContext. It seems stupid to have all those subqueries that fetches one column each, and most of them aren't even used later.
Note that if I omit ".Where(r => r.ID > 0)", it crashes with this error instead when trying to build the query:
An unhandled exception of type 'System.NullReferenceException' occurred in EntityFramework.dll
at MySql.Data.Entity.SelectStatement.AddDefaultColumns(Scope scope)
at MySql.Data.Entity.SelectStatement.Wrap(Scope scope)
at MySql.Data.Entity.SelectGenerator.Visit(DbApplyExpression expression)
at System.Data.Entity.Core.Common.CommandTrees.DbApplyExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
at MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type)
at MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type)
at MySql.Data.Entity.SelectGenerator.Visit(DbProjectExpression expression)
at System.Data.Entity.Core.Common.CommandTrees.DbProjectExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
at MySql.Data.Entity.SelectGenerator.GenerateSQL(DbCommandTree tree)
at MySql.Data.MySqlClient.MySqlProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree)
at System.Data.Entity.Core.Common.DbProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext)
...
EF 5 does a much better job. It creates this query:
SELECT
`Extent1`.`ID`,
`Extent2`.`LeftId`,
`Join2`.`Item`
FROM `right` AS `Extent1` INNER JOIN `middle` AS `Extent2` ON `Extent1`.`MiddleId` = `Extent2`.`ID` LEFT OUTER JOIN (SELECT
`Extent3`.`ID`,
`Extent3`.`Item`,
`Extent4`.`ID` AS `ID1`,
`Extent4`.`LeftId`
FROM `left` AS `Extent3` LEFT OUTER JOIN `middle` AS `Extent4` ON (`Extent3`.`ID` = `Extent4`.`LeftId`) AND (`Extent4`.`LeftId` IS NOT NULL)) AS `Join2` ON `Join2`.`ID1` = `Extent1`.`MiddleId`
WHERE `Extent1`.`ID` > 0
I have also seen some other strange results when upgrading to EF 6 when using "Include".
I have a lot of rows in the datatbase, and run a query like db.rights.Include("middle.left").Where(...).ToArray().
When I now try to fetch the objects' middle.left, for about the first 10 or 20 rows, I get the correct `left` objects, but for the rest of the rows, middle.left is null when it actually isn't null. If I omit the Include, it works as expected, but takes of course much longer to execute. Here the generated SQL seems to be correct but the results aren't read correctly into the entity objects. That's very annoying since you don't discover the bug until you have enough data. It's harder to create a test case on this...
How to repeat:
Follow steps above
Description: I have three tables like this: CREATE TABLE `eftest`.`left` ( `ID` INT NOT NULL AUTO_INCREMENT, `Item` varchar(45) DEFAULT NULL, PRIMARY KEY (`ID`)) CREATE TABLE `eftest`.`middle` ( `ID` INT NOT NULL AUTO_INCREMENT , `LeftId` INT NULL , PRIMARY KEY (`ID`) , INDEX `FK_MIDDLE_LEFT_idx` (`LeftId` ASC) , CONSTRAINT `FK_MIDDLE_LEFT` FOREIGN KEY (`LeftId` ) REFERENCES `eftest`.`left` (`ID` ) ON DELETE NO ACTION ON UPDATE NO ACTION); CREATE TABLE `eftest`.`right` ( `ID` INT NOT NULL AUTO_INCREMENT , `MiddleId` INT NOT NULL , PRIMARY KEY (`ID`) , INDEX `FK_RIGHT_MIDDLE_idx` (`MiddleId` ASC) , CONSTRAINT `FK_RIGHT_MIDDLE` FOREIGN KEY (`MiddleId` ) REFERENCES `eftest`.`middle` (`ID` ) ON DELETE NO ACTION ON UPDATE NO ACTION ); So a `right` points to a `middle`, and a `middle` points to a `left`. In my setup, a `left` is always referenced by exactly a single `middle`, so I have a one-to-one relationship here. I import these tables to Entity Framework Model Designer in Visual Studio 2013 by "Update Model from Database". I have chosen "Pluralize or singuralize generated object names" but not "Include foreign key columns in the model" (don't know if this really matters). I changed "middles" navigation property on "left" from "* (Many)" to "1 (One)". I also renamed the navigation property to "middle". This doesn't work: using (var db = new eftestEntities()) { db.rights.Where(r => r.ID > 0).Select(r => new { lid = r.middle.left.ID, r.middle.left.Item }).ToArray(); } This query is generated in EF 6.1 together with MySql Connector .Net 6.8.3: SELECT `Apply1`.`ID`, `Apply1`.`LeftId`, `Apply1`.`Item` FROM (SELECT `Filter1`.`ID`, `Filter1`.`MiddleId`, `Filter1`.`ID1`, `Filter1`.`LeftId`, (SELECT `Extent3`.`ID` FROM `left` AS `Extent3` LEFT OUTER JOIN `middle` AS `Extent4` ON (`Extent4`.`LeftId` IS NOT NULL) AND (`Extent3`.`ID` = `Extent4`.`LeftId`) INNER JOIN `middle` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`ID` WHERE `Extent1`.`MiddleId` = `Extent5`.`ID`) AS `ID2`, (SELECT `Extent3`.`Item` FROM `left` AS `Extent3` LEFT OUTER JOIN `middle` AS `Extent4` ON (`Extent4`.`LeftId` IS NOT NULL) AND (`Extent3`.`ID` = `Extent4`.`LeftId`) INNER JOIN `middle` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`ID` WHERE `Extent1`.`MiddleId` = `Extent5`.`ID`) AS `Item`, (SELECT `Extent4`.`ID` AS `ID1` FROM `left` AS `Extent3` LEFT OUTER JOIN `middle` AS `Extent4` ON (`Extent4`.`LeftId` IS NOT NULL) AND (`Extent3`.`ID` = `Extent4`.`LeftId`) INNER JOIN `middle` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`ID` WHERE `Extent1`.`MiddleId` = `Extent5`.`ID`) AS `ID3`, (SELECT `Extent4`.`LeftId` FROM `left` AS `Extent3` LEFT OUTER JOIN `middle` AS `Extent4` ON (`Extent4`.`LeftId` IS NOT NULL) AND (`Extent3`.`ID` = `Extent4`.`LeftId`) INNER JOIN `middle` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`ID` WHERE `Extent1`.`MiddleId` = `Extent5`.`ID`) AS `LEFTID1`, (SELECT `Extent5`.`ID` AS `ID2` FROM `left` AS `Extent3` LEFT OUTER JOIN `middle` AS `Extent4` ON (`Extent4`.`LeftId` IS NOT NULL) AND (`Extent3`.`ID` = `Extent4`.`LeftId`) INNER JOIN `middle` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`ID` WHERE `Extent1`.`MiddleId` = `Extent5`.`ID`) AS `ID4`, (SELECT `Extent5`.`LeftId` AS `LEFTID1` FROM `left` AS `Extent3` LEFT OUTER JOIN `middle` AS `Extent4` ON (`Extent4`.`LeftId` IS NOT NULL) AND (`Extent3`.`ID` = `Extent4`.`LeftId`) INNER JOIN `middle` AS `Extent5` ON `Extent5`.`ID` = `Extent4`.`ID` WHERE `Extent1`.`MiddleId` = `Extent5`.`ID`) AS `LEFTID2` FROM (SELECT `Extent1`.`ID`, `Extent1`.`MiddleId`, `Extent2`.`ID` AS `ID1`, `Extent2`.`LeftId` FROM `right` AS `Extent1` INNER JOIN `middle` AS `Extent2` ON `Extent1`.`MiddleId` = `Extent2`.`ID` WHERE `Extent1`.`ID` > 0) AS `Filter1`) AS `Apply1` with the error message: "Unknown column 'Extent1.MiddleId' in 'where clause'" The error occurs because `Extent1` is declared inside a subquery, and can thus not be used outside that subquery. It doesn't matter if I use ObjectContext or DbContext. It seems stupid to have all those subqueries that fetches one column each, and most of them aren't even used later. Note that if I omit ".Where(r => r.ID > 0)", it crashes with this error instead when trying to build the query: An unhandled exception of type 'System.NullReferenceException' occurred in EntityFramework.dll at MySql.Data.Entity.SelectStatement.AddDefaultColumns(Scope scope) at MySql.Data.Entity.SelectStatement.Wrap(Scope scope) at MySql.Data.Entity.SelectGenerator.Visit(DbApplyExpression expression) at System.Data.Entity.Core.Common.CommandTrees.DbApplyExpression.Accept[TResultType](DbExpressionVisitor`1 visitor) at MySql.Data.Entity.SqlGenerator.VisitInputExpression(DbExpression e, String name, TypeUsage type) at MySql.Data.Entity.SelectGenerator.VisitInputExpressionEnsureSelect(DbExpression e, String name, TypeUsage type) at MySql.Data.Entity.SelectGenerator.Visit(DbProjectExpression expression) at System.Data.Entity.Core.Common.CommandTrees.DbProjectExpression.Accept[TResultType](DbExpressionVisitor`1 visitor) at MySql.Data.Entity.SelectGenerator.GenerateSQL(DbCommandTree tree) at MySql.Data.MySqlClient.MySqlProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree) at System.Data.Entity.Core.Common.DbProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree, DbInterceptionContext interceptionContext) ... EF 5 does a much better job. It creates this query: SELECT `Extent1`.`ID`, `Extent2`.`LeftId`, `Join2`.`Item` FROM `right` AS `Extent1` INNER JOIN `middle` AS `Extent2` ON `Extent1`.`MiddleId` = `Extent2`.`ID` LEFT OUTER JOIN (SELECT `Extent3`.`ID`, `Extent3`.`Item`, `Extent4`.`ID` AS `ID1`, `Extent4`.`LeftId` FROM `left` AS `Extent3` LEFT OUTER JOIN `middle` AS `Extent4` ON (`Extent3`.`ID` = `Extent4`.`LeftId`) AND (`Extent4`.`LeftId` IS NOT NULL)) AS `Join2` ON `Join2`.`ID1` = `Extent1`.`MiddleId` WHERE `Extent1`.`ID` > 0 I have also seen some other strange results when upgrading to EF 6 when using "Include". I have a lot of rows in the datatbase, and run a query like db.rights.Include("middle.left").Where(...).ToArray(). When I now try to fetch the objects' middle.left, for about the first 10 or 20 rows, I get the correct `left` objects, but for the rest of the rows, middle.left is null when it actually isn't null. If I omit the Include, it works as expected, but takes of course much longer to execute. Here the generated SQL seems to be correct but the results aren't read correctly into the entity objects. That's very annoying since you don't discover the bug until you have enough data. It's harder to create a test case on this... How to repeat: Follow steps above