Description:
Using code first w/ EF6.1.3 & MySql 5.7, if the model contains a 3-level hierarchy
and the mapping is Table-per-type,
queries return entities with a wrong type, or throw an exception.
e.g.
in Seed():
context.Cats.AddOrUpdate(c => c.Name,
new Cat { Name = "Chloe", AnimalProperty1 = "female cat",
CatProperty1 = "Cat3" });
context.Animals.AddOrUpdate(a => a.Name,
new Cat { Name = "Lucy", AnimalProperty1 = "female cat",
CatProperty1 = "Cat4" },
etc.
foreach (Animal a in context.Animals) gives
Animal: 2 - HouseCat - Sophie - female cat - Cat2 - HouseCat2
Animal: 3 - Dog - Chloe - female cat -
Animal: 4 - Dog - Lucy - female cat -
Animal: 1 - Dog - Max - male dog - Dog1
Animal: 5 - Dog - Buddy - male dog - Dog5
context.Animals.First(a => a.Name.StartsWith("B")):
First animal name B: 5 - Dog - Buddy - male dog - Dog5
but
context.Animals.First(a => a.Name.StartsWith("C")):
First animal name C: 3 - Dog - Chloe - female cat -
context.Animals.First(a => a.Name.StartsWith("L")):
First animal name L: 4 - Dog - Lucy - female cat -
In the example, Chloe and Lucy are clearly cats but the returned entities are of type Dog with empty Dog-specific properties.
As a consequence, context.Cats.First(a => a.Name.StartsWith("L")) throws
System.NotSupportedException: All objects in the EntitySet 'Context.Animals' must have unique primary keys. However, an instance of type 'MySqlInheritanceBug.Entities.HouseCat' and an instance of type 'MySqlInheritanceBug.Entities.Dog' both have the same primary key value, 'EntitySet=Animals;Id=4'.
and so on.
The database tables, their data and the generated SQL all look good.
There is no such problem with TPH but it is not always an option.
How to repeat:
Create a code first model including the following hierarchy:
abstract class Grandparent {...}
class Parent1 : Grandparent {...}
class Parent2 : Grandparent {...}
class Child11: Parent1 {...}
(Grandparent=Animal, Parent1=Cat, Parent2=Dog, Child11=HouseCat in the example)
Use Table-per-type representation
Seed each type with data
Query the DbSets (using foreach, .First(), etc.)
Description: Using code first w/ EF6.1.3 & MySql 5.7, if the model contains a 3-level hierarchy and the mapping is Table-per-type, queries return entities with a wrong type, or throw an exception. e.g. in Seed(): context.Cats.AddOrUpdate(c => c.Name, new Cat { Name = "Chloe", AnimalProperty1 = "female cat", CatProperty1 = "Cat3" }); context.Animals.AddOrUpdate(a => a.Name, new Cat { Name = "Lucy", AnimalProperty1 = "female cat", CatProperty1 = "Cat4" }, etc. foreach (Animal a in context.Animals) gives Animal: 2 - HouseCat - Sophie - female cat - Cat2 - HouseCat2 Animal: 3 - Dog - Chloe - female cat - Animal: 4 - Dog - Lucy - female cat - Animal: 1 - Dog - Max - male dog - Dog1 Animal: 5 - Dog - Buddy - male dog - Dog5 context.Animals.First(a => a.Name.StartsWith("B")): First animal name B: 5 - Dog - Buddy - male dog - Dog5 but context.Animals.First(a => a.Name.StartsWith("C")): First animal name C: 3 - Dog - Chloe - female cat - context.Animals.First(a => a.Name.StartsWith("L")): First animal name L: 4 - Dog - Lucy - female cat - In the example, Chloe and Lucy are clearly cats but the returned entities are of type Dog with empty Dog-specific properties. As a consequence, context.Cats.First(a => a.Name.StartsWith("L")) throws System.NotSupportedException: All objects in the EntitySet 'Context.Animals' must have unique primary keys. However, an instance of type 'MySqlInheritanceBug.Entities.HouseCat' and an instance of type 'MySqlInheritanceBug.Entities.Dog' both have the same primary key value, 'EntitySet=Animals;Id=4'. and so on. The database tables, their data and the generated SQL all look good. There is no such problem with TPH but it is not always an option. How to repeat: Create a code first model including the following hierarchy: abstract class Grandparent {...} class Parent1 : Grandparent {...} class Parent2 : Grandparent {...} class Child11: Parent1 {...} (Grandparent=Animal, Parent1=Cat, Parent2=Dog, Child11=HouseCat in the example) Use Table-per-type representation Seed each type with data Query the DbSets (using foreach, .First(), etc.)