How to create two one-to-one relations with same entity in Entity Framework Core

Question

I'm getting stuck when using code-first in EF Core 2.2.

I have an entity called: AirSensor which I need to have two one-to-one relations to TemperatureSensors, as the AirSensor is capable of read Temperature and dew point temperature.

Therefore my code is here:

public class AirSensor
{
    public long AirSensorId { get; set; }        
    public TemperatureSensor TemperatureSensor { get; set; }
    public HumiditySensor Humidity { get; set; }   
    public Enthalpy EnthalpyCalc { get; set; }
    public TemperatureSensor DewPointTemperatureSensor { get; set; }

    public long TemperatureSensorId { get; set; }
    public long HumiditySensorId { get; set; }
    public long EnthalpyId { get; set; }
    public long DewPointTemperatureSensorId { get; set; }  
}

And Temperature Entity is:

public class TemperatureSensor
{
    public long TemperatureSensorId { get; set; }
    public string Name { get; set; }
    public float CurrentValue { get; set; }
    public DateTime LastUpdated { get; set; }
    public ICollection<TemperatureMeasurement> Measurements { get; set; }
}

When I add the migration everything is ok, but when updating the database it fails:

Failed executing DbCommand (17ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

ALTER TABLE [AirSensors] ADD CONSTRAINT [FK_AirSensors_TemperatureSensors_DewPointSensorId] FOREIGN KEY ([DewPointSensorId]) REFERENCES [TemperatureSensors] ([TemperatureSensorId]) ON DELETE CASCADE; System.Data.SqlClient.SqlException (0x80131904): Si especifica la restricción FOREIGN KEY 'FK_AirSensors_TemperatureSensors_DewPointSensorId' en la tabla 'AirSensors', podrían producirse ciclos o múltiples rutas en cascada. Especifique ON DELETE NO ACTION o UPDATE NO ACTION, o bien modifique otras restricciones FOREIGN KEY.

However, I don't see the problem here, I just want to have two separate one-to-one relations.

What am I missing?

Many thanks in advance!

Are you doing any custom mapping in your dbcontext? You likely have to explicitly mark your ForeignKeys on the AirSensor class. — Canica, Mar 08 '19 at 18:55
No custom mapping. But the funny thing is that it works with just one relation one to one — cjbs, Mar 08 '19 at 18:59
see this question, particularly the fluent API mapping within `OnModelCreating`: https://stackoverflow.com/questions/5559043/entity-framework-code-first-two-foreign-keys-from-same-table — Canica, Mar 08 '19 at 19:02

score 2 · Answer 1 · answered Mar 09 '19 at 02:32

Configure AirSensor with Fluent API as follows:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
     base.OnModelCreating(modelBuilder);

     modelBuilder.Entity<AirSensor>().HasOne(ars => ars.TemperatureSensor)
            .WithOne().HasForeignKey<AirSensor>(ars => ars.TemperatureSensorId)
            .OnDelete(DeleteBehavior.Restrict);

     modelBuilder.Entity<AirSensor>().HasOne(ars => ars.DewPointTemperatureSensor)
            .WithOne().HasForeignKey<AirSensor>(ars => ars.DewPointTemperatureSensorId)
            .OnDelete(DeleteBehavior.Restrict);
 }

Now it will work as expected!

How to create two one-to-one relations with same entity in Entity Framework Core

1 Answers1