How to allow cascade delete for complex object that has two lists of other complex objects?

asp.net-core c# entity-framework-core sql-server

Question

I'm struggling with trying to make it work.

I'd want to have an Dealer who has Two Lists of the same type of Items that are in relation, but I'd want also to be able to cascadly delete them.

public class Dealer
{
    [Key]
    public Guid Id { get; set; }

    public string Name { get; set; }

    public List<Car> OldCars { get; set; } = new List<Car>();

    public List<Car> NewCars { get; set; } = new List<Car>();
}

public class Car
{
    [Key]
    public Guid Id { get; set; }

    public string Model { get; set; }

    public Dealer Dealer { get; set; }
}

What I've been trying:

Attempt #1

modelBuilder.Entity<Car>()
.HasOne(s => s.Dealer)
.WithMany(s => s.OldCars)
.HasForeignKey(x => x.Dealer)
.OnDelete(DeleteBehavior.Cascade);

modelBuilder.Entity<Car>()
.HasOne(s => s.Dealer)
.WithMany(s => s.NewCars)
.HasForeignKey(x => x.Dealer)
.OnDelete(DeleteBehavior.Cascade);

System.InvalidOperationException: ''Dealer' cannot be used as a property on entity type 'Car' because it is configured as a navigation.'

Attempt #2 Meanwhile with:

modelBuilder.Entity<Car>()
.HasOne(s => s.Dealer)
.WithMany(s => s.NewCars)
.HasPrincipalKey(x => x.Id) <--- Principal!
.OnDelete(DeleteBehavior.Cascade);     

It works properly, but shows warning that

warn: Microsoft.EntityFrameworkCore.Model[10605] There are multiple relationships between 'Car' and 'Dealer' without configured foreign key properties causing EF to create shadow properties on 'Car' with names dependent on the discovery order.

Data in db

Unfortunely while attempting to perform Delete (cascade) it throws an exception:

SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_Cars_Dealers_DealerId1". The conflict occurred in database "testDB", table "dbo.Cars", column 'DealerId1'. The statement has been terminated.

Attempt #3

When I add Dealer's Id to Car

public class Car
{
    [Key]
    public Guid Id { get; set; }

    public string Model { get; set; }

    public Dealer Dealer { get; set; }

    public Guid DealerID { get; set; }
}

It yells about

System.Data.SqlClient.SqlException: 'Cannot create, drop, enable, or disable more than one constraint, column, index, or trigger named 'FK_Cars_Dealers_DealerId' in this context. Duplicate names are not allowed. Column names in each table must be unique. Column name 'DealerId' in table 'Cars' is specified more than once.'

#Attempt #3.1

So, after renaming Guid DealerID to e.g Guid DealerID123

it yells

SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Cars_Dealers_DealerID123". The conflict occurred in database "testDB", table "dbo.Dealers", column 'Id'. The statement has been terminated.

Anybody has an idea how to make it work?

1
1
9/9/2019 4:37:49 AM

Popular Answer

Thanks to Ivan Stoev recommendation, I managed to resolve it as follow:

public class Dealer
{
   [Key]
   public Guid Id { get; set; }
   public string Name { get; set; }
   [NotMapped]
   public List<Car> OldCars { get; set; } => NewCars.Where(c => c.IsOld == true);
   public List<Car> NewCars { get; set; } = new List<Car>();
}

public class Car
{
   [Key]
   public Guid Id { get; set; }
   public string Model { get; set; }
   public Dealer Dealer { get; set; }
   public bool IsOld { get; set; }
}

In EF6 ModelBuilder I did as follow:

modelBuilder.Entity<Dealer>()
.HasMany(d => d.NewCars)
.WithRequired()
.WillCascadeOnDelete(true);
0
9/9/2019 4:40:38 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow