I’m developing a web site application where clients may upload files. Some of the files may be attachments for some documents, and some are not. I’m using SQL Server as the database.

I decided to go with the following scheme: one table for storing information about uploaded files with parent id and parenttype for relationship one (doc) to many (files).

I don’t need automatic navigation from file to parent document.

This is my code:

public class FilesFromClients
{
    public int id { get; set; } //primary key 

    // some other properties
    public int ParentId { get; set; }
    public ParentType pType { get; set; }   // discriminator
}

public enum ParentType : int { NOTSET = 0, SOME=1, HRMAGREEMENT=2 };

File may have parent (and parenttype) (so it is an attachment to some document) or may be unrelated to any parent, so parentid is 0 and pType = ParentType.NotSet (or 0).

Therefore, I can’t use these properties for unique key

public class HrmFiles : FilesFromClients {}  //for discriminator lately

// plan to add more type of docs here 

public class NewHrmAgreement 
{
    public int Id { get; set; } // primary key 

    // other properties
    ICollection<HrmFiles> Files { get; set; }
}

public virtual DbSet<FileFromClients> tblFiles;
public virtual DbSet<HrmFiles> hrmFiles;

// OnModelCreating 
modelBuilder.Entity<FileFromClient>(e =>
  {
      e.HasDiscriminator<ParentType>(c => c.PType)
           .HasValue<cFileFromClient>(ParentType.NOTSET)
           .HasValue<cUploadsNoParents>(ParentType.SOME)
           .HasValue<HrmFiles>(ParentType.HRMAGREEMENT);    
  });

modelBuilder.Entity<NewHrmAgreement>(e =>
   {
      e.HasMany(a => a.Files)
       .WithOne()
       .HasForeignKey(f => f.ParentId);
   });

Migration adds an additional foreign key

migrationBuilder.AddForeignKey(
    name: "FK_FilesFromClients_NewHrmAgreements_ParentId",
    table: "FilesFromClients",
    column: "ParentId",
    principalTable: "NewHrmAgreements",
    principalColumn: "ID");      

As far as I plan to add more docs (and discriminator values), I think that this FK is unnecessary or wrong.

If I am trying to update the database, I’m getting

Introducing FOREIGN KEY constraint ‘FK_FilesFromClients_NewHrmAgreements_ParentId’ on table ‘FilesFromClients’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint or index. See previous errors.

I tried different values in OnDelete()

e.HasMany(a => a.Files) 
 .WithOne()
 .HasForeignKey(f => f.ParentId)
 .OnDelete(DeleteBehavior.ClientNoAction);

but just getting different errors when tried to update database
like

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “FK_FilesFromClients_NewHrmAgreements_ParentId”. The conflict occurred in database “autoabcNGTest”, table “dbo.NewHrmAgreements”, column ‘ID’

If I manually remove this FK from the migration, everything seems to work as expected. EF Core adds parenttype to queries and creates correct records in db and deletes them.

Now I’m choosing between either remove mapping for NewHrmAgreement.Files and do all related staff manually, or just manually removing the FK from migration.

Am I missing something or doing something wrong?

Thanks in advance.

ish