Storing a collection with multiple same items

Question

I am using EF to store data into a SQL Server database.

There are 2 types of data:

1. Image
2. Symbol

Each symbol contains a collection of images

public virtual ICollection<Image> Images { get; set; }

and each image contains a collection of symbols:

public virtual ICollection<Symbol> Symbols { get; set; }

EF creates a relation-table for the many-to-many relationship.

If a new symbol is created (uploaded) the program looks into the images table if these images are already uploaded.

If yes, the will be added to the images list of the new symbol.

Now there is a symbol with 3 images (all the same!) and it will be added to the Symbols table.

But in the relations-table is created ONLY ONE entry!?

So the next time this Symbol is read out of the database it just contains only 1 image...

What am I doing wrong?

Or what would be the right way of doing it?

Table structures:

[Table("Images")]
public class SqlSymbolImgInfo
{
    [Key]
    public int Id { get; set; }

    public String Path { get; set; }
    public long Size { get; set; }
    public String Md5 { get; set; }
    public int X { get; set; }
    public int Y { get; set; }

    public virtual ICollection<SqlSymbol> Symbols { get; set; }

    public override int GetHashCode()
    {
        return System.IO.Path.GetExtension(Path ?? "x.png").GetHashCode() + Size.GetHashCode() + (Md5 ?? "").GetHashCode();
    }
}

[Table("Symbols")]
public class SqlSymbol
{
    [Key]
    public int Id { get; set; }

    public String Description { get; set; }
    public int Type { get; set; }

    public virtual ICollection<SqlSymbolImgInfo> Images { get; set; }
}

Creating the relation:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<System.Data.Entity.ModelConfiguration.Conventions.PluralizingTableNameConvention>();

        modelBuilder.Entity<SqlSymbol>().HasMany(s => s.Images).WithMany(i => i.Symbols);

        base.OnModelCreating(modelBuilder);
    }

Adding the symbols:

1. Add the images if they not exist

2. Get the added images back

3. Add the symbol

   public void AddSymbol(Symbol sym)
   {
       try
       {
           using (var db = new Db())
           {
               // check if images already exists or add them..
               foreach (var img in sym.Images)
               {
                   var exImg = db.FindImage(img);
                   if (exImg == null)
                   {
                       var newImg = db.Images.Add(img.ConvertToSqlSymbolImgInfo());
                       db.SaveChanges();
                       img.Id = newImg.Id; // get Id of new image
                   }
                   else
                   {
                       img.Id = exImg.Id; // get Id of existing image
                   }
               }
   
               var sqlSym = sym.ConvertToSqlSymbol();
               // get the existing entries, otherwise the same image will be added again and again...
               sqlSym.Images = sym.Images.Select(i => db.Images.Find(i.Id)).ToList();
   
               db.Symbols.Add(sqlSym);
               db.SaveChanges();
               sym.Id = sqlSym;
           }
       }
       catch { logIt("error while adding a symbol."); }
   }
   

For explanation:

I need to use wrapper classes for the entity framework cause of the Symbol and Image classes are in a shared library. And I dont want to include EF in all other projects even if I do not use it there..

Some additional infos:

Symbol with 3 same images:

Collection after adding and "searching" the image:

After adding this Symbol to the database, take a look at the tables:

Images table:

and the relations table:

!! the marked line should be there 3 times ?!

Its the Symbol with Id 50, which has 3 Images with the Id 108 .. NOT just one!

Here is a simplified project to test it with just a few lines of code:

test project

Answer 1

As you wrote in your comment, the many-to-many relation table has PK defined on both FK keys, so it's impossible to put there same combination twice. I would first consider if this situation should really be possible in your business model. If it should, then one way you can go is manually declaring your intermediate table and giving it artificial PK (just autoincremented int for example).

Then in EF you will have two one-to-many relationships (from Symbol and from Image to Intermediate table which you can call whatever - like ImagesInSymbol or so). Using these collections will be less convenient but will work. And as a bonus you will be able to set another properties on this relation later (like DateCreated, Status etc).

For more details see this question.