Having navigations for base type and sub type in the same entity result in "Cannot create a relationship ... because a relationship already exists..." exception #32507
Comments
|
I found one working workaround (which doesn't use explicit FK configuration) but it looks more like a limited hack: public class Zoo
{
public int Id { get; set; }
public IEnumerable<Animal> Animals { get; set; }
// public IEnumerable<Tiger> Tigers { get; set; } <= THIS DOESN'T WORK!
[NotMapped] public IEnumerable<Tiger> Tigers => Animals.OfType<Tiger>();
}But the loading of var zoo = context.Zoos.Include(x => x.Animals.Where(y => y is Tiger)).First();and the loading of nested properties like So I still think that navigation for sub-types ( |
|
Initially, this problem was reported in #26961 but that issue was closed without working solution and nobody wants to reopen it for a month. |
vlardn
changed the title
|
/cc @AndriySvyryd |
|
This is by design and you already found the best workaround. |
|
@AndriySvyryd What do you mean "by design"? Does this mean EF Core can construct SQL query for either base-type or sub-type navigation but it's impossible to do the same for both? The database schema is completely identical in all 3 cases and it's just a matter of building the same SQL queries for navigations in 3rd case: CASE 1 - works: public class Zoo
{
public int Id { get; set; }
public IEnumerable<Animal> Animals { get; set; }
}
var zooWithAnimals = context.Zoos.Include(x => x.Animals).Single()SELECT "t"."Id", "a"."Id", "a"."ZooId"
FROM (
SELECT "z"."Id"
FROM "Zoos" AS "z"
LIMIT 2
) AS "t"
LEFT JOIN "Animals" AS "a" ON "t"."Id" = "a"."ZooId"
ORDER BY "t"."Id"CASE 2 - works: public class Zoo
{
public int Id { get; set; }
public IEnumerable<Tiger> Tigers { get; set; }
}
var zooWithTigers = context.Zoos.Include(x => x.Tigers).Single()SELECT "t"."Id", "t0"."Id", "t0"."Discriminator", "t0"."ZooId"
FROM (
SELECT "z"."Id"
FROM "Zoos" AS "z"
LIMIT 2
) AS "t"
LEFT JOIN (
SELECT "a"."Id", "a"."Discriminator", "a"."ZooId"
FROM "Animals" AS "a"
WHERE "a"."Discriminator" = 'Tiger'
) AS "t0" ON "t"."Id" = "t0"."ZooId"
ORDER BY "t"."Id"CASE 3 - impossible?? public class Zoo
{
public int Id { get; set; }
public IEnumerable<Animal> Animals { get; set; }
public IEnumerable<Tiger> Tigers { get; set; }
}
var zooWithAnimals = context.Zoos.Include(x => x.Animals).Single()
var zooWithTigers = context.Zoos.Include(x => x.Tigers).Single() |
|
Try configuring the Tiger relationship as one-directional: |
This changes the database schema and adds unnecessary shadow property public class Zoo
{
public int Id { get; set; }
public IEnumerable<Animal> Animals { get; set; }
public IEnumerable<Tiger> Tigers { get; set; }
}
public abstract class Animal
{
public int Id { get; set; }
public int ZooId { get; set; }
//public Zoo Zoo { get; set; }
}
public class Tiger : Animal
{
}
modelBuilder.Entity<Zoo>(b =>
{
b.HasMany(e => e.Animals).WithOne(/* e => e.Zoo */).HasForeignKey(e => e.ZooId);
b.HasMany(e => e.Tigers).WithOne(/* e => e.Zoo */).HasForeignKey(e => e.ZooId);
});CREATE TABLE "Animal" (
"Id" INTEGER NOT NULL CONSTRAINT "PK_Animal" PRIMARY KEY AUTOINCREMENT,
"ZooId" INTEGER NOT NULL,
"Discriminator" TEXT NOT NULL,
"ZooId1" INTEGER NULL,
CONSTRAINT "FK_Animal_Zoo_ZooId" FOREIGN KEY ("ZooId") REFERENCES "Zoo" ("Id") ON DELETE CASCADE,
CONSTRAINT "FK_Animal_Zoo_ZooId1" FOREIGN KEY ("ZooId1") REFERENCES "Zoo" ("Id")
);PS. You can check these SQL statements by running the simple console test app mentioned in the description of this issue. |
I see, then, indeed there doesn't appear to be a good workaround. #7623 will enable this scenario too. |
Given this model:
This works:
This works:
This does NOT work:
The simple console test app:
gives an exception:
Tested on EF Core 7.0.13 and 8.0.0
The text was updated successfully, but these errors were encountered: