Allow duplicates in Many-to-Many?

Question

I have Item table which have manyTomany to itself:

public virtual ICollection<Item> Related { get; set; }

modelBuilder.Entity<Item>().HasMany(x => x.Related ).WithMany();

Data:

Item_Id   Item_Id1
8         2
8         3
8         4

How can I allow duplicate , meaning allow another 8-2 for example, when I try to insert another "duplicate" data , it shows only 1.

I'm curious? Why do you want this? What would duplicate rows mean? Can you just add a `int Count` field and create a manual intermediate table? — usr, Apr 14 '13 at 11:42
to create third column int-count , I need to make a new table also , this could be another possible option , if object X have Y Y Z related to him , the database will save only Y Z related to him , that's why I needed "duplicate" , I don't know if duplicate is the right word here — Zakos, Apr 14 '13 at 13:20

score 2 · Answer 1 · answered Jun 12 '14 at 17:19

I think that the origin of the problem is to misunderstand what an entity is. An entity is unique, and has a unique Id, so for example, a company could have a collection of employed people, that are unique, so you cannot have two instances of the same person in the same collection. If you have a shopping cart, you have to think to the items in the collection as 'order lines' and not as the items itself. Any order line has its own Id and a reference to an entity, that is the object you are purchasing.

score 1 · Answer 2 · edited May 23 '17 at 11:51

1

I didn't check this specifically, so you may need to work out the details -
but (I think) the only way to do it is to manually define the index table (e.g. Item2Item).

Check this post I made for additional info EF code-first many-to-many with additional data That is on how to create a custom table for many-to-many - with adding additional fields.

Now that you have a custom table, you need to change it a bit - you need to remove the composite keys from the .HasKey. Instead use your own pk, e.g. identity as for any other table.

i.e. you can define a unique PK, identity like for any other table - and have that as your key. That should let you have duplicate keys in your fk columns).

Check this post as it seems logically close (though not the same)
Many to many (join table) relationship with the same entity with codefirst or fluent API?

edited May 23 '17 at 11:51

Community

1
1

answered Apr 13 '13 at 18:46

NSGaga-mostly-inactive

14,052
3
41
51

r u sure its the only way? If I know that I have have max 5 related-items, How about this this : add 5 properties of Virutal Item to Item class? – Zakos Apr 13 '13 at 19:07
well, I could be missing something - I underlined because I'm pretty sure - but I may have misunderstood something (and this is a very specific solution) - so I'll remove the 'bold' from that:). Anyways, your pk is a composite key of your item(first) and item(second) - if you have two like that - your pk constraint will 'complain' about it. And since that table is made for you behind the scenes - it's pretty standard - and there is no way to customize that. That's why I said, you pretty much 'have to' make it via a custom table. The rest is not 100% how it might pan out,given self-referencing – NSGaga-mostly-inactive Apr 13 '13 at 19:12
And if I understood you right - I don't think you can use `Item` fields to add that 'additional data' - as it's about the 'relationship'. – NSGaga-mostly-inactive Apr 13 '13 at 19:15
I meant , if i remove public virtual ICollection Related { get; set; } , and make Virtual Item ITem1 {ge;t set;} 5 times , can this work as a workaround? ( how I make code-section in comment? ) – Zakos Apr 13 '13 at 19:24
Well you could add 'extra relationship' which is not m-2-m but 1-to-m or 1 to 1 (while that is tricky and almost always goes as 1-m anyways) - i.e. another self-fk. To serve as some sort of 'default' or something. E.g. this post of mine (not related probably but just to demostrate) http://stackoverflow.com/questions/15751798/entity-framework-one-to-many-with-default. But then I'm not sure what you want really - you should post a working model with as many details. – NSGaga-mostly-inactive Apr 13 '13 at 19:29
What I gave you is a 'cleaner' solution IMO - than adding 5 items (even if it serves the purpose, that I'm failing to see from here). Also the more you 'play' with code first like that almost certain is to get into problems. – NSGaga-mostly-inactive Apr 13 '13 at 19:30
Yea, I see , I'm on it ,I hope it will go smooth , thanks for time & answers, I'll update answer when ready – Zakos Apr 14 '13 at 00:25

score 0 · Accepted Answer · edited May 23 '17 at 12:15

Well this gave me the final solution--> EF Code First, how can I achieve two foreign keys from one table to other table? , NSGaga thanks for the direction

public class Item : IValidatableObject
{
    [Key]
    public int ID { get; set; }
    public virtual ICollection<ItemRelation> Related{ get; set; }
    public string Name { get; set; }

    public IEnumerable<ValidationResult> Validate(ValidationContext validationContext)
    {
        if (....)
        {
            yield return new ValidationResult("Name not valid", new[] { "Name" });
        }

    }
}

public class ItemRelation
{
   [Key]
    public int ID { get; set; }

    public int ItemAID { get; set; }
    public virtual Item ItemA { get; set; }

    public int ItemBID { get; set; }
    public virtual Item ItemB { get; set; }

}



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

        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

        modelBuilder.Entity<ItemRelation>().HasRequired(c => c.ItemA)
                            .WithMany(m => m.Related)
                            .HasForeignKey(c => c.ItemAID)
                            .WillCascadeOnDelete();

        modelBuilder.Entity<ItemRelation>().HasRequired(c => c.ItemB)
                           .WithMany()
                           .HasForeignKey(c => c.ItemBID)
                           .WillCascadeOnDelete(false);


    }

    public DbSet<Item> Items { get; set; }
    public DbSet<ItemRelation> ItemsRelations { get; set; }

Data:

Id   ItemA_Id   ItemB_Id
1    8         2
2    8         3
3    8         5
4    8         5
5    8         5
6    1         6
7    5         4
8    2         9

Allow duplicates in Many-to-Many?

3 Answers3