Description:
When using Table-Per-Type with a base class and multiple implementations, when this is queried via a join, subclass-specific columns like Status from multiple tables (e.g., Cars, Bikes) are given unique aliases during projection, but those aliases are not correctly referenced in downstream expressions.
In the example below, both Cars.Status and Bikes.Status are projected into the subquery with distinct aliases:
`Project1`.`Status`, -- from Bikes
`Project2`.`Status` AS `STATUS1` -- from Cars
However, the CASE-WHEN construction that determines which subclass property to return uses the unaliased column Join2.Status, which refers to the Bikes.Status, even when the current row is from Cars:
CASE
WHEN ((`Join2`.`C1` = 1) AND (`Join2`.`C1` IS NOT NULL)) THEN (`Join2`.`Status`)
...
ELSE (`Join2`.`Status`)
END AS `C3` / `C5`
This results in the Cars.Status value (aliased as STATUS1) being completely ignored.
This has the consequence that subclass-specific values are lost or incorrect in the final result set. For example, when querying a Car, the Status field will incorrectly return NULL or a Bike’s status (if any), rather than the expected value from Cars.Status.
## Reproduction SQL
```SELECT
CASE WHEN ((NOT ((`Join2`.`C1` = 1) AND (`Join2`.`C1` IS NOT NULL))) AND (NOT ((`Join2`.`C11` = 1) AND (`Join2`.`C11` IS NOT NULL)))) THEN ('2X') WHEN ((`Join2`.`C1` = 1) AND (`Join2`.`C1` IS NOT NULL)) THEN ('2X0X') ELSE ('2X1X') END AS `C1`,
`Join2`.`Id`,
`Join2`.`Year`,
`Join2`.`Name`,
`Join2`.`ManufacturerId`,
CASE WHEN ((NOT ((`Join2`.`C1` = 1) AND (`Join2`.`C1` IS NOT NULL))) AND (NOT ((`Join2`.`C11` = 1) AND (`Join2`.`C11` IS NOT NULL)))) THEN (NULL) WHEN ((`Join2`.`C1` = 1) AND (`Join2`.`C1` IS NOT NULL)) THEN (`Join2`.`BikeProperty`) END AS `C2`,
CASE WHEN ((NOT ((`Join2`.`C1` = 1) AND (`Join2`.`C1` IS NOT NULL))) AND (NOT ((`Join2`.`C11` = 1) AND (`Join2`.`C11` IS NOT NULL)))) THEN (NULL) WHEN ((`Join2`.`C1` = 1) AND (`Join2`.`C1` IS NOT NULL)) THEN (`Join2`.`Status`) END AS `C3`,
CASE WHEN ((NOT ((`Join2`.`C1` = 1) AND (`Join2`.`C1` IS NOT NULL))) AND (NOT ((`Join2`.`C11` = 1) AND (`Join2`.`C11` IS NOT NULL)))) THEN (NULL) WHEN ((`Join2`.`C1` = 1) AND (`Join2`.`C1` IS NOT NULL)) THEN (NULL) ELSE (`Join2`.`CarProperty`) END AS `C4`,
CASE WHEN ((NOT ((`Join2`.`C1` = 1) AND (`Join2`.`C1` IS NOT NULL))) AND (NOT ((`Join2`.`C11` = 1) AND (`Join2`.`C11` IS NOT NULL)))) THEN (NULL) WHEN ((`Join2`.`C1` = 1) AND (`Join2`.`C1` IS NOT NULL)) THEN (NULL) ELSE (`Join2`.`Status`) END AS `C5`
FROM `Manufacturer4` AS `Extent1` INNER JOIN (SELECT
`Extent2`.`Id`,
`Extent2`.`Year`,
`Extent2`.`Name`,
`Extent2`.`ManufacturerId`,
`Project1`.`Id` AS `ID1`,
`Project1`.`BikeProperty`,
`Project1`.`Status`,
`Project1`.`C1`,
`Project2`.`Id` AS `ID2`,
`Project2`.`CarProperty`,
`Project2`.`Status` AS `STATUS1`,
`Project2`.`C1` AS `C11`
FROM `Vehicles` AS `Extent2` LEFT OUTER JOIN (SELECT
`Extent3`.`Id`,
`Extent3`.`BikeProperty`,
`Extent3`.`Status`,
1 AS `C1`
FROM `Bikes` AS `Extent3`) AS `Project1` ON `Extent2`.`Id` = `Project1`.`Id` LEFT OUTER JOIN (SELECT
`Extent4`.`Id`,
`Extent4`.`CarProperty`,
`Extent4`.`Status`,
1 AS `C1`
FROM `Cars` AS `Extent4`) AS `Project2` ON `Extent2`.`Id` = `Project2`.`Id`) AS `Join2` ON `Extent1`.`ManufacturerId` = `Join2`.`ManufacturerId````
How to repeat:
Applying this patch adds a conflicting "Status" column to `Car4` and `Bike4`, both which inherits from `Vehicle4`. This reproduces the issue, as the `Car4`/`Bike4` loaded entities from DB will both load their `Status` column value from the same table (Bike4).
```diff --git forkSrcPrefix/EntityFramework/tests/MySql.EntityFramework.CodeFirst.Tests/CodeFirstTests.cs forkDstPrefix/EntityFramework/tests/MySql.EntityFramework.CodeFirst.Tests/CodeFirstTests.cs
index 5796c3e1e68a8c0b809b3edc2a6b093fcd8e6dbf..698f99e4f3b8732342d489bb565275fc4a43728a 100644
--- forkSrcPrefix/EntityFramework/tests/MySql.EntityFramework.CodeFirst.Tests/CodeFirstTests.cs
+++ forkDstPrefix/EntityFramework/tests/MySql.EntityFramework.CodeFirst.Tests/CodeFirstTests.cs
@@ -204,8 +204,8 @@ namespace MySql.Data.EntityFramework.CodeFirst.Tests
context.Database.Delete();
context.Database.Initialize(true);
var manuf = context.Manufacturers.Add(new Manufacturer4 { Name = "ACME" });
- context.Vehicles.Add(new Car4 { Id = 1, Name = "Mustang", Year = 2012, CarProperty = "Car", Manufacturer = manuf });
- context.Vehicles.Add(new Bike4 { Id = 101, Name = "Mountain", Year = 2011, BikeProperty = "Bike", Manufacturer = manuf });
+ context.Vehicles.Add(new Car4 { Id = 1, Name = "Mustang", Year = 2012, Status = "Foo", CarProperty = "Car", Manufacturer = manuf });
+ context.Vehicles.Add(new Bike4 { Id = 101, Name = "Mountain", Year = 2011, Status = "Bar", BikeProperty = "Bike", Manufacturer = manuf });
context.SaveChanges();
vehicles = context.Manufacturers.SelectMany(v => v.Vehicles).ToList();
@@ -224,7 +224,9 @@ namespace MySql.Data.EntityFramework.CodeFirst.Tests
{
var vehiclesfromdb = context.Manufacturers.SelectMany(v => v.Vehicles).ToList();
Assert.That(vehiclesfromdb.OfType<Car4>().Single().CarProperty, Is.EqualTo(vehicles.OfType<Car4>().Single().CarProperty));
+ Assert.That(vehiclesfromdb.OfType<Car4>().Single().Status, Is.EqualTo(vehicles.OfType<Car4>().Single().Status));
Assert.That(vehiclesfromdb.OfType<Bike4>().Single().BikeProperty, Is.EqualTo(vehicles.OfType<Bike4>().Single().BikeProperty));
+ Assert.That(vehiclesfromdb.OfType<Bike4>().Single().Status, Is.EqualTo(vehicles.OfType<Bike4>().Single().Status));
}
}
diff --git forkSrcPrefix/EntityFramework/tests/MySql.EntityFramework.CodeFirst.Tests/Vehicle.cs forkDstPrefix/EntityFramework/tests/MySql.EntityFramework.CodeFirst.Tests/Vehicle.cs
index f95bbbfa13d8239edcfe89b5f313a099e4045bdf..f6b816ef96723c3abc2656c128ed254121895f0c 100644
--- forkSrcPrefix/EntityFramework/tests/MySql.EntityFramework.CodeFirst.Tests/Vehicle.cs
+++ forkDstPrefix/EntityFramework/tests/MySql.EntityFramework.CodeFirst.Tests/Vehicle.cs
@@ -121,11 +121,13 @@ namespace MySql.Data.EntityFramework.CodeFirst.Tests
public class Car4 : Vehicle4
{
public string CarProperty { get; set; }
+ public string Status { get; set; }
}
public class Bike4 : Vehicle4
{
public string BikeProperty { get; set; }
+ public string Status { get; set; }
}
public class Manufacturer4
{
```
Suggested fix:
Applying the following patch:
```diff
--- forkSrcPrefix/EntityFramework/src/Statements/SelectStatement.cs
+++ forkDstPrefix/EntityFramework/src/Statements/SelectStatement.cs
@@ -233,8 +233,6 @@ namespace MySql.Data.EntityFramework
{
newColumn.ColumnAlias = cf.ColumnAlias;
newColumn.PushInput(cf.ActualColumnName);
- if (cf.TableName != null)
- newColumn.PushInput(cf.TableName);
}
else
{
```
appears to resolve the issue. The problem stems from a mismatch in the `PropertyFragments` used to resolve column references during SQL generation.
In `SqlGenerator.Visit(DbPropertyExpression)`, a `ColumnFragment` is constructed using the accessed property name. Its `PropertyFragments` are built by traversing the expression tree—for example, something like `Join3.Join2.Project2.Status`. These fragments are passed to `scope.FindInputFromProperties`, which attempts to locate the matching `InputFragment` in the current `SelectStatement`.
However, the columns in the `SelectStatement` may have been renamed or aliased, and their stored `PropertyFragments` differ. In the case analyzed, the `SelectStatement` contains a column with fragments:
```
["Project2", "Extent4", "Status"]
```
But the expression tree generates:
```
["Join3", "Join2", "Project2", "Status"]
```
This discrepancy causes `ColumnFragment.Equals` to return `false` because `PropertyFragment.Equals` fails—the lists do not match, especially due to the presence of `Extent4`.
The patch resolves this by preventing `TableName` from being pushed into the `PropertyFragments`, resulting in a cleaner fragment like:
```
["Project2", "Status"]
```
This structure is now sufficient to satisfy the equality test in `PropertyFragment.Equals`, allowing the correct column match to be found during SQL generation.
Description: When using Table-Per-Type with a base class and multiple implementations, when this is queried via a join, subclass-specific columns like Status from multiple tables (e.g., Cars, Bikes) are given unique aliases during projection, but those aliases are not correctly referenced in downstream expressions. In the example below, both Cars.Status and Bikes.Status are projected into the subquery with distinct aliases: `Project1`.`Status`, -- from Bikes `Project2`.`Status` AS `STATUS1` -- from Cars However, the CASE-WHEN construction that determines which subclass property to return uses the unaliased column Join2.Status, which refers to the Bikes.Status, even when the current row is from Cars: CASE WHEN ((`Join2`.`C1` = 1) AND (`Join2`.`C1` IS NOT NULL)) THEN (`Join2`.`Status`) ... ELSE (`Join2`.`Status`) END AS `C3` / `C5` This results in the Cars.Status value (aliased as STATUS1) being completely ignored. This has the consequence that subclass-specific values are lost or incorrect in the final result set. For example, when querying a Car, the Status field will incorrectly return NULL or a Bike’s status (if any), rather than the expected value from Cars.Status. ## Reproduction SQL ```SELECT CASE WHEN ((NOT ((`Join2`.`C1` = 1) AND (`Join2`.`C1` IS NOT NULL))) AND (NOT ((`Join2`.`C11` = 1) AND (`Join2`.`C11` IS NOT NULL)))) THEN ('2X') WHEN ((`Join2`.`C1` = 1) AND (`Join2`.`C1` IS NOT NULL)) THEN ('2X0X') ELSE ('2X1X') END AS `C1`, `Join2`.`Id`, `Join2`.`Year`, `Join2`.`Name`, `Join2`.`ManufacturerId`, CASE WHEN ((NOT ((`Join2`.`C1` = 1) AND (`Join2`.`C1` IS NOT NULL))) AND (NOT ((`Join2`.`C11` = 1) AND (`Join2`.`C11` IS NOT NULL)))) THEN (NULL) WHEN ((`Join2`.`C1` = 1) AND (`Join2`.`C1` IS NOT NULL)) THEN (`Join2`.`BikeProperty`) END AS `C2`, CASE WHEN ((NOT ((`Join2`.`C1` = 1) AND (`Join2`.`C1` IS NOT NULL))) AND (NOT ((`Join2`.`C11` = 1) AND (`Join2`.`C11` IS NOT NULL)))) THEN (NULL) WHEN ((`Join2`.`C1` = 1) AND (`Join2`.`C1` IS NOT NULL)) THEN (`Join2`.`Status`) END AS `C3`, CASE WHEN ((NOT ((`Join2`.`C1` = 1) AND (`Join2`.`C1` IS NOT NULL))) AND (NOT ((`Join2`.`C11` = 1) AND (`Join2`.`C11` IS NOT NULL)))) THEN (NULL) WHEN ((`Join2`.`C1` = 1) AND (`Join2`.`C1` IS NOT NULL)) THEN (NULL) ELSE (`Join2`.`CarProperty`) END AS `C4`, CASE WHEN ((NOT ((`Join2`.`C1` = 1) AND (`Join2`.`C1` IS NOT NULL))) AND (NOT ((`Join2`.`C11` = 1) AND (`Join2`.`C11` IS NOT NULL)))) THEN (NULL) WHEN ((`Join2`.`C1` = 1) AND (`Join2`.`C1` IS NOT NULL)) THEN (NULL) ELSE (`Join2`.`Status`) END AS `C5` FROM `Manufacturer4` AS `Extent1` INNER JOIN (SELECT `Extent2`.`Id`, `Extent2`.`Year`, `Extent2`.`Name`, `Extent2`.`ManufacturerId`, `Project1`.`Id` AS `ID1`, `Project1`.`BikeProperty`, `Project1`.`Status`, `Project1`.`C1`, `Project2`.`Id` AS `ID2`, `Project2`.`CarProperty`, `Project2`.`Status` AS `STATUS1`, `Project2`.`C1` AS `C11` FROM `Vehicles` AS `Extent2` LEFT OUTER JOIN (SELECT `Extent3`.`Id`, `Extent3`.`BikeProperty`, `Extent3`.`Status`, 1 AS `C1` FROM `Bikes` AS `Extent3`) AS `Project1` ON `Extent2`.`Id` = `Project1`.`Id` LEFT OUTER JOIN (SELECT `Extent4`.`Id`, `Extent4`.`CarProperty`, `Extent4`.`Status`, 1 AS `C1` FROM `Cars` AS `Extent4`) AS `Project2` ON `Extent2`.`Id` = `Project2`.`Id`) AS `Join2` ON `Extent1`.`ManufacturerId` = `Join2`.`ManufacturerId```` How to repeat: Applying this patch adds a conflicting "Status" column to `Car4` and `Bike4`, both which inherits from `Vehicle4`. This reproduces the issue, as the `Car4`/`Bike4` loaded entities from DB will both load their `Status` column value from the same table (Bike4). ```diff --git forkSrcPrefix/EntityFramework/tests/MySql.EntityFramework.CodeFirst.Tests/CodeFirstTests.cs forkDstPrefix/EntityFramework/tests/MySql.EntityFramework.CodeFirst.Tests/CodeFirstTests.cs index 5796c3e1e68a8c0b809b3edc2a6b093fcd8e6dbf..698f99e4f3b8732342d489bb565275fc4a43728a 100644 --- forkSrcPrefix/EntityFramework/tests/MySql.EntityFramework.CodeFirst.Tests/CodeFirstTests.cs +++ forkDstPrefix/EntityFramework/tests/MySql.EntityFramework.CodeFirst.Tests/CodeFirstTests.cs @@ -204,8 +204,8 @@ namespace MySql.Data.EntityFramework.CodeFirst.Tests context.Database.Delete(); context.Database.Initialize(true); var manuf = context.Manufacturers.Add(new Manufacturer4 { Name = "ACME" }); - context.Vehicles.Add(new Car4 { Id = 1, Name = "Mustang", Year = 2012, CarProperty = "Car", Manufacturer = manuf }); - context.Vehicles.Add(new Bike4 { Id = 101, Name = "Mountain", Year = 2011, BikeProperty = "Bike", Manufacturer = manuf }); + context.Vehicles.Add(new Car4 { Id = 1, Name = "Mustang", Year = 2012, Status = "Foo", CarProperty = "Car", Manufacturer = manuf }); + context.Vehicles.Add(new Bike4 { Id = 101, Name = "Mountain", Year = 2011, Status = "Bar", BikeProperty = "Bike", Manufacturer = manuf }); context.SaveChanges(); vehicles = context.Manufacturers.SelectMany(v => v.Vehicles).ToList(); @@ -224,7 +224,9 @@ namespace MySql.Data.EntityFramework.CodeFirst.Tests { var vehiclesfromdb = context.Manufacturers.SelectMany(v => v.Vehicles).ToList(); Assert.That(vehiclesfromdb.OfType<Car4>().Single().CarProperty, Is.EqualTo(vehicles.OfType<Car4>().Single().CarProperty)); + Assert.That(vehiclesfromdb.OfType<Car4>().Single().Status, Is.EqualTo(vehicles.OfType<Car4>().Single().Status)); Assert.That(vehiclesfromdb.OfType<Bike4>().Single().BikeProperty, Is.EqualTo(vehicles.OfType<Bike4>().Single().BikeProperty)); + Assert.That(vehiclesfromdb.OfType<Bike4>().Single().Status, Is.EqualTo(vehicles.OfType<Bike4>().Single().Status)); } } diff --git forkSrcPrefix/EntityFramework/tests/MySql.EntityFramework.CodeFirst.Tests/Vehicle.cs forkDstPrefix/EntityFramework/tests/MySql.EntityFramework.CodeFirst.Tests/Vehicle.cs index f95bbbfa13d8239edcfe89b5f313a099e4045bdf..f6b816ef96723c3abc2656c128ed254121895f0c 100644 --- forkSrcPrefix/EntityFramework/tests/MySql.EntityFramework.CodeFirst.Tests/Vehicle.cs +++ forkDstPrefix/EntityFramework/tests/MySql.EntityFramework.CodeFirst.Tests/Vehicle.cs @@ -121,11 +121,13 @@ namespace MySql.Data.EntityFramework.CodeFirst.Tests public class Car4 : Vehicle4 { public string CarProperty { get; set; } + public string Status { get; set; } } public class Bike4 : Vehicle4 { public string BikeProperty { get; set; } + public string Status { get; set; } } public class Manufacturer4 { ``` Suggested fix: Applying the following patch: ```diff --- forkSrcPrefix/EntityFramework/src/Statements/SelectStatement.cs +++ forkDstPrefix/EntityFramework/src/Statements/SelectStatement.cs @@ -233,8 +233,6 @@ namespace MySql.Data.EntityFramework { newColumn.ColumnAlias = cf.ColumnAlias; newColumn.PushInput(cf.ActualColumnName); - if (cf.TableName != null) - newColumn.PushInput(cf.TableName); } else { ``` appears to resolve the issue. The problem stems from a mismatch in the `PropertyFragments` used to resolve column references during SQL generation. In `SqlGenerator.Visit(DbPropertyExpression)`, a `ColumnFragment` is constructed using the accessed property name. Its `PropertyFragments` are built by traversing the expression tree—for example, something like `Join3.Join2.Project2.Status`. These fragments are passed to `scope.FindInputFromProperties`, which attempts to locate the matching `InputFragment` in the current `SelectStatement`. However, the columns in the `SelectStatement` may have been renamed or aliased, and their stored `PropertyFragments` differ. In the case analyzed, the `SelectStatement` contains a column with fragments: ``` ["Project2", "Extent4", "Status"] ``` But the expression tree generates: ``` ["Join3", "Join2", "Project2", "Status"] ``` This discrepancy causes `ColumnFragment.Equals` to return `false` because `PropertyFragment.Equals` fails—the lists do not match, especially due to the presence of `Extent4`. The patch resolves this by preventing `TableName` from being pushed into the `PropertyFragments`, resulting in a cleaner fragment like: ``` ["Project2", "Status"] ``` This structure is now sufficient to satisfy the equality test in `PropertyFragment.Equals`, allowing the correct column match to be found during SQL generation.