EF Core One to One relationship on many tables allows duplicate entries

entity-framework entity-framework-core sql-server

Question

I am using EF Core and I tried to create a one-to-one relationship between three tables (Car, ElectricCar and PetrolCar)

public class Car
{
    public int Id { get; set; }
    public string RegistrationNumber { get; set; }

    public ElectricCar Company { get; set; }
    public PetrolCar Trust { get; set; }
}

public class ElectricCar
{
    public int ElectricCarId { get; set; }
    public double BatteryCapacityWattage{ get; set; }

    public int CarId { get; set; }
    public Car Car { get; set; }
}

public class PetrolCar
{
    public int PetrolCarId { get; set; }
    public double TankCapacity { get; set; }

    public int CarId { get; set; }
    public Car Car { get; set; }
}


public partial class CarDbContext : Microsoft.EntityFrameworkCore.DbContext
{
    public CarDbContext()
    {
    }

    public CarDbContext(DbContextOptions<CarDbContext> options)
        : base(options)
    {
    }

    public DbSet<ElectricCar> ElectricCar { get; set; }
    public DbSet<Car> Car { get; set; }
    public DbSet<PetrolCar> PetrolCar { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlServer("Server=DESKTOP-PC\\SQLLOCAL;Database=OneToOneEFCoreCar;Trusted_Connection=True;");
        }
    }
}

and the code that inserts the data:

CarDbContext context = new CarDbContext();

context.Car.Add(new Car
{
    RegistrationNumber = "EL123",
    Company = new ElectricCar() { BatteryCapacityWattage = 2000 }
});

context.Car.Add(new Car
{
    RegistrationNumber = "PETR123",
    Trust = new PetrolCar() {  TankCapacity = 50 }
});
context.SaveChanges();

That works without any issue and creates the following data enter image description here

When I go to the PetrolCar I insert a new row with CarId = 1 and it accepts it without giving any error although that CarId is used in the ElectricCar table as CarId. Is there any way to restrict this?

1
0
2/16/2019 2:40:26 PM

Popular Answer

If you're entirely set on keeping your object models / data structure the same as it is above then a unique constraint across the two tables isnt really natively achievable.

One possible in code solution (though its not particularly clean, so I would suggest restructuring your data over this, though that seems to be something you would like to avoid) is to override the SaveChanges method.

something along the lines of:

public override SaveChanges()
{
    var petrolCars = ChangeTracker.Entries().Where(e is PetrolCar).ToList();

    foreach(var pCar in petrolCars)
    { 
        if(query the database for electric cars to see if car id exists)
        {
            do some sort of error processing and avoid saving;
        }
    }

    base.SaveChanges();
}

it does mean creating a context class that inherits from the default context, though it adds a lot of flexibility in terms of doing something like this (obviously you would want to handle the other cases too of cars having the same id in the other direction)

0
2/18/2019 1:17:17 PM


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