Unwanted unique constraint in many to many relationship

Question

I'm trying to set up a Tagging tool for images. Basically I have two tables, one for pictures, and one for tags. Both are connected with a many to many setup. I can already add a single tag to a picture, and the same tag to different pictures. However, when I try to add a second tag to an image I get an exception complaining about a unique constraint that I simply don't see.

public class MediaEntity
    {
        public Guid Id { get; set; }
        public string Name { get; set; }    
        public ICollection<TagEntity> Tags { get; set; } 
    } 

public class TagEntity
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
        public ICollection<MediaEntity> MediaEntities { get; set; }
    }

public void updateMedia(MediaEntity model)
    {
        using (var db = new MediaContext(_dbLocation))
        {
            db.Update(model);
            db.SaveChanges();
        }
    }

public class MediaContext : DbContext
    {
        private const string DB_NAME = "PT.db";
        private string _path;

        public DbSet<MediaEntity> MediaTable { get; set; }
        public DbSet<TagEntity> TagTable { get; set; }

        public MediaContext(string path)
        {
            _path = path;
            ChangeTracker.AutoDetectChangesEnabled = false;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder options)
            => options.UseSqlite($"Data Source={Path.Combine(_path, DB_NAME )}");
    }

As far as I can tell my setup should create a normal many-to-many relationship, and it the database I also see pretty much this. EF automatically creates a TagTable, MediaTable, and MediaEntityTagEntityTable. But when I try to add a second tag I get this:

SqliteException: SQLite Error 19: 'UNIQUE constraint failed: MediaEntityTagEntity.MediaEntitiesId, MediaEntityTagEntity.TagsId'.

Data from the table showing I can have the same tag on different pictures:

MediaEntitiesId	TagEntitiesId
1B48E85B-F097-4216-9B7A-0BA34E69CBFF	CF581257-F176-4CDF-BF34-09013DCEAA27
CE33F03F-5C80-492B-88C6-3C40B9BADC6C	CF581257-F176-4CDF-BF34-09013DCEAA27
523178A1-C7F8-4A69-9578-6A599C1BEBD5	0C45C9D1-7576-4C62-A495-F5EF268E9DF8

I don't see where this unique constaint comes in. How can I set up a proper many-to-many relationship?

Answer 1

I suspect the issue you may be running into is with the detached Media and associated Tags you are sending in. You are telling EF to apply an 'Update' to the media, but the DbContext will have no idea about the state of the Tags attached. Assuming some tags may have been newly attached, others are existing relationships. If the Context isn't tracking any of these Tags, it would treat them all as inserts, resulting in index violations (many to many) or duplicate data (many to one / one to many)

When dealing with associations like this, it is generally simpler to define more atomic actions like: AddTag(mediaId, tagId) and RemoveTag(mediaId, tagId)

If you are applying tag changes along with potential media field updates in a single operation I would recommend rather than passing entire entity graphs back and forth, to use a viewModel/DTO for the tag containing a collection of TagIds, from that apply your tag changes against the media server side after determining which tags have been added and removed.

I.e.:

public void updateMedia(MediaViewModel model)
{
    using (var db = new MediaContext(_dbLocation))
    {
        var media = db.Medias.Include(x => x.Tags).Single(x => x.MediaId = model.MedialId);

        // Ideally have a Timestamp/row version number to check...
        if (media.RowVersion != model.RowVersion)
            throw new StaleDataException("The media has been modified since the data was retrieved.");

        // copy media fields across...
        media.Name = model.Name;
        // ... etc.

        var existingTagIds = media.Tags
            .Select(x => x.TagId)
            .ToList();
        var tagIdsToRemove = existingTagIds
            .Except(model.TagIds)
            .ToList();
        var tagIdsToAdd = model.TagIds
            .Except(existingTagIds)
            .ToList();

        if(tagIdsToRemove.Any())
            media.Tags.RemoveRange(media.Tags.Where(x => tagIdsToRemove.Contains(x.TagId));

        if(tagIdsToAdd.Any())
        {
            var tagsToAdd = db.Tags.Where(x => tagIdsToAdd.Contains(x.TagId)).ToList();
            media.Tags.AddRange(tagsToAdd);
        }

        db.SaveChanges();
    }
}

Using this approach the DbContext is never left guessing about the state of the media and associated tags. It helps guard against stale data overwrites and unintentional data tampering (if receiving data from web browsers or other unverifiable sources), and by using view models with the minimum required data, you improve performance by minimzing the amount of data sent over the wire and traps like lazy load hits by serializers.

Answer 2

I always explicitly create the join table. The Primary Key is the combination of the two 1:M FK attributes. I know EF is supposed to map automatically, but since it isn't, you can specify the structure you know you need.