EF Core filtering out duplicate entities in many-to-many relationship

Question

I'm running into an issue where entity framework core 5 ignores duplicate entries in a many-to-many relationship. I have "Devices" that can have any number of "Sensors" (including multiple of the same Sensor). If I try to add 2 of the same Sensor to a Device, EF only inserts 1. If I add 2 of the same Sensor directly to the DeviceSensorRelation table, EF only shows one of them in the Device.Sensors list.

I ran SQL Server Profiler to check the query being run and it does return all of the entries that EF isn't displaying. Is this a limitation of EF Core, or am I missing a configuration or something? (I'd assume it's noticing the duplicate Sensor.Id as its the primary key, just doesn't really make sense as there's a table between them)

I have the following table structure:

CREATE TABLE [Hardware].[Device]
(
    [Id] [int] IDENTITY(1,1) NOT NULL, --PK
    [Name] [varchar](200) NOT NULL,
    [BuildDate] [date] NOT NULL
)

CREATE TABLE [Hardware].[Sensor]
(
    [Id] [int] IDENTITY(1,1) NOT NULL, --PK
    [Name] [varchar](200) NOT NULL,
    [Cost] [money] NOT NULL
)

CREATE TABLE [Hardware].[DeviceSensorRelation]
(
    [Id] [int] IDENTITY(1,1) NOT NULL, --PK
    [DeviceId] [int] NOT NULL, --FK to Device.Id table
    [SensorId] [int] NOT NULL --FK to Sensor.Id table
)

And these are the EF Core entities:

public class Device
{
    public int Id { get; }
    public string Name { get; private set; }
    public DateTime BuildDate { get; private set; }

    private List sensors = new List();
    public IReadOnlyCollection Sensors => this.sensors;
}

public class Sensor
{
    public int Id { get; }
    public string Name { get; private set; }
    public decimal Cost { get; private set; }

    private List devices = new List();
    public IReadOnlyCollection Devices => this.devices;
}

And this is the code setting up the relationship in the Device entity configuration:

builder.HasMany(x => x.Sensors)
       .WithMany(x => x.Devices)
       .UsingEntity(b =>
       {
           b.ToTable("DeviceSensorRelation", "Hardware");
           b.Property("Id").UseIdentityColumn();
           b.Property("DeviceId");
           b.Property("SensorId");
           b.HasKey("Id");
       })

user_11509101 · Answer

I think EF core is acting correctly, remember that EF core is an abstraction on the db, even if there are 2 records EF core is considering just the "relation". The needed information to state if two entities are related is satisfied by one of the records in the join table, the second does not give more information regarding the question "the entities are linked?".

You want to store and retrieve more information, you want to know how many times a sensor is "related" to a device. In this case i would suggest to use a many to many with payload relation and add an extra column to the join entity where to store the extra information, that can be anything (so is up to you to populate this info): in this case the number of times the relation is in place between a TypeOfDevice and a TypeOfSensor. I referred to your entities with those names to focus on the fact that a simple many to many would be better suited for linking a "concrete" device with a unique serial id (unique) to a "concrete" sensor with a unique serial id.

It would be interesting to see what happens if after fetching the entities you tried to remove the link, would the 2 records be both deleted? But really thinking about it I think a pk on the join table on the deviceId and sensorId columns is a good idea (because of the nature of many to many) so this scenario could never happen.

EF Core filtering out duplicate entities in many-to-many relationship

1 Answers