Entity Framework Core Many to Many with unique associations

Question

I have 2 entities with many-to-many relation through an intermediate table:

public class FileDataType 
{
    public FileDataType()
    {
        MetadataTypes = new HashSet<FileMetadataTypeLink>();
    }
    public int Id { get; set; }
    public string Name { get; set; }

    public ICollection<FileMetadataTypeLink> MetadataTypes { get; set; }
    ...
}

public class FileMetadataTypeLink
{
    public int FileMetadataTypeId { get; set; }
    public FileMetadataType FileMetadataType { get; set; }
    public int FileDataTypeId { get; set; }
    public FileDataType FileDataType { get; set; }
}

public class FileMetadataType
{
    public FileMetadataType()
    {
        FileMetadataTypeLink = new HashSet<FileMetadataTypeLink>();
    }

    public int Id { get; set; }
    public string Name { get; set; }
    ...

}

FileMetadataTypeLink is configured with multi-column primary key, as I would like to have a database restriction, not to create same associations multiple times.

modelBuilder.Entity<FileMetadataTypeLink>(entity =>
        {
            entity.HasKey(x => new  { x.FileDataTypeId, x.FileMetadataTypeId});
            entity.ToTable("FileMetadataTypeLink");

            entity.HasOne(d => d.FileDataType)
                .WithMany(p => p.MetadataTypes)
                .HasForeignKey(d => d.FileDataTypeId)
                .OnDelete(DeleteBehavior.Restrict)
                .HasConstraintName("FK_FileMetadataTypeLink_FileDataType");

            entity.HasOne(d => d.FileMetadataType)
                  .WithMany(p => p.FileMetadataTypeLink)
                  .HasForeignKey(d => d.FileMetadataTypeId)
                  .OnDelete(DeleteBehavior.Restrict)
                  .HasConstraintName("FK_FileMetadataTypeLink_FileMetadataType");
        });
modelBuilder.Entity<FileMetadataType>(entity =>
        {
            entity.HasKey(x => x.Id);
            entity.ToTable("FileMetadataType");
        });

modelBuilder.Entity<FileDataType>(entity =>
        {
            entity.HasKey(x => x.Id);
            entity.ToTable("FileDataType");
        });

When inserting a single FileMetadataTypeLink, to associate existing FileDataType with existing FileMetadata object using Entity Framework core, I receive a SQL exception:

SqlException (0x80131904): Cannot insert explicit value for identity column in table 'FileDataType' when IDENTITY_INSERT is set to OFF.

 var association = new FileMetadataTypeLink()
                {
                    FileDataTypeId = dataTypeId,
                    FileMetadataTypeId = metadataTypeId
                };

   _context.Set<FileMetadataTypeLink>().Add(association );

   _context.SaveChanges();

What am I missing here?

Answer 1

Your code should be as below. You should use FileDataTypeId instead of FileDataTypeId .

var association = new FileMetadataTypeLink()
                {
                    FileDataTypeId = dataTypeId,
                    FileMetadataTypeId = metadataTypeId
                };

   _context.Set<FileMetadataTypeLink>().Add(association );

   _context.SaveChanges();