Entity framework allow duplicates in many-to-many relation

Question

I have 2 object - parents and childs in relationship many-to-many, the issue is that 1 parent can have 2 the same childs, but EF saves this relationship only 1 time.

I found only 2 working solutions:

1. add count column into table and manually fill it
2. not use many-to-many, but split it into one-to-many and many-to-one but I do not like this solutions, because I hope that there could be some more simple solution.

Could you help me please?

EDIT: Example of junction table:

1-1

1-1

1-2

1-3

2-3

Code: model

public class Item
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int id { get; set; }

    public virtual ICollection<Item> childs { get; set; }
    public virtual ICollection<Item> parents { get; set; }
}

DB context

modelBuilder.Entity<Item>().
HasMany(i => i.childs).
WithMany(i2 => i2.parents).
Map(
m =>
{
m.MapLeftKey("parentId");
m.MapRightKey("childId");
m.ToTable("itemRelationship");
});

Answer 1

Relational databases are about sets. A set is a distinct collection of identified entities. Your junction table however, the way you're picturing it, is a bag. This violates the fundamentals of relational theory. As usual, one violation gives rise to other violations. Not having a unique primary key, an identity, is the next one. Not being able to refer to these junction records by foreign keys, should you want that, is another one.

So just don't do it.

I think the mistake here is that you want to express the fact "A has n instances of B" by the number of rows you create. But this number is an attribute of the association.

Let's look at an example: Articles and Words.

You could express the relation between Article and Word by a pure many-to-many association. This association expresses: this Article contains these Words. Watch the demonstrative pronouns (this, these). They imply identity. There will be one instance of each article and one instance of each word in the database.

If you want to store the number of times a word occurs, you have to add this number as an attribute to the association. A fact is never modelled by a number of rows. The single row describes the fact "this Article has n occurrences of that Word". In Entity Framework this means that the association becomes a class in the class model, for instance ArticleWord. The multiplicities of the association are Article 1-n ArticleWord n-1 Word.

Answer 2

One solution would be to first define the relationship table. The primary key of that table will be a combination of the 2 foreign keys that it contains, it will allow that composite key to look like :

1-1,

2-1,

2-2

but not :

1-1

1-1

1-2

2-1

Answer 3

I agree with Gert about the core concept of relational data is sets. To that end I recommend you find another thing to make them unique. Like adding a timestamp or created by date. If you really need to get duplicates in there. But this is again going back to relational data being normalized into sets. Here is a link about DB Normalization

[Key, Column(Order =2), DatabaseGenerated(DatabaseGeneratedOption.None)]
public DateTime DateCreated { get; set; }

[Timestamp]
public byte[] RowVersion { get; set; }

Timestamps are mostly for concurrency but you might be able to add them to a key.

I'm also curious as to what the scenario you have that would need duplicates in a many to many table. Two records with the only two columns being exactly the same, means that the relationship being expressed twice, it doesn't create an additional relationship.

It's like saying my sister is my sister, having it be said that she's my sister again didn't change the fact that she's my sister, it didn't give me two of her, she was still in the collection, she can't be in the collection twice because of database normalization. I can count her twice in my collection but that doesn't change that fact that there is only one of her (in the other table). If there needs to be two of her there should be another record with a different Id.

Answer 4

If you really need to have duplicate records on n:n relationship :

public class Playlist : BaseEntity
{
    public Collection<Track>? Tracks { get; set; }
}
public class Track: BaseEntity
{
    public IEnumerable<Playlist>? Playlists { get; set; }
}
public class PlaylistTrack
{
    public int Id { get; set; }
    public int PlaylistId { get; set; }
    public int TrackId { get; set; }
}
//....

public DbSet<PlaylistTrack> PlaylistTrack { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Playlist>()
        .HasMany(a => a.Tracks)
        .WithMany(a => a.Playlists)
        .UsingEntity<PlaylistTrack>(a =>
            a.Property(e => e.Id).ValueGeneratedOnAdd()
        );
}

to insert records:

var pt = new PlaylistTrack()
{
    PlaylistId = playlist.Id,
    TrackId = track.Id
};
db.PlaylistTrack.Add(pt);
await db.SaveChangesAsync();