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

Question

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

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?

pantonis

View more on Stack Overflow

Gibbon · Accepted Answer · 2/18/2019 1:17:17 PM

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)

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

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related