How can I map one-to-many (Folder/File)-to-(RoleBindings with Discriminator in one table) using .NET 5 EF and Fluent API?
How can I apply OnDelete() method to have one table RoleBindings where the end result would look like:
Id|Role|Discriminator|FolderId|FileId
and preserve my rule that if I delete RoleBindings nothing happens, but if the Folder/File is deleted, it should delete relations-RoleBindings?
Without Fluent API OnDelete() method the project relationship and behavior is generated badly. Only way I made it work is by defining .OnDelete(DeleteBehavior.Cascade) on Folder/File and .OnDelete(DeleteBehavior.NoAction) on RoleBindings separate tables.
Classes:
public abstract class RoleBindings
{
public Guid Id { get; set; } = Guid.NewGuid();
public Role Role { get; set; }
}
public class FolderRoleBindings : RoleBindings
{
public Guid FolderId { get; set; }
public virtual Folder Folder { get; }
}
public class FileRoleBindings : RoleBindings
{
public Guid FileId { get; set; }
public virtual File File { get; set; }
}
public abstract class File : IFile
{
public Guid Id { get; set; }
public virtual ICollection<FileRoleBindings> Roles { get; set; }
}
public abstract class Folder : IFolder
{
public Guid Id { get; set; }
public virtual ICollection<FolderRoleBindings> Roles { get; set; }
}
Working version when I generate to separate tables:
public class MyDbContext : DbContext
{
public DbSet<Folder> Folders { get; set; }
public DbSet<File> Files { get; set; }
// public DbSet<RoleBindings> RoleBindings { get; set; }
public DbSet<FolderRoleBindings> FolderRoleBindings { get; set; }
public DbSet<FileRoleBindings> FileRoleBindings { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Folder>(e =>
{
e.HasMany(prop => prop.Roles)
.WithOne(ar => ar.Folder)
.HasForeignKey(prop => prop.FolderId)
.OnDelete(DeleteBehavior.NoAction);
});
modelBuilder.Entity<File>(e =>
{
e.HasMany(prop => prop.Roles)
.WithOne(ar => ar.File)
.HasForeignKey(prop => prop.FileId)
.OnDelete(DeleteBehavior.NoAction);
});
modelBuilder.Entity<FileRoleBindings>(e =>
{
e.ToTable(name: "FileRoleBindings", schema: "dbo");
e.HasKey(key => key.Id);
e.HasOne(prop => prop.File)
.WithMany(r => r.Roles)
.OnDelete(DeleteBehavior.Cascade);
});
modelBuilder.Entity<FolderRoleBindings>(e =>
{
e.ToTable(name: "FolderRoleBindings", schema: "dbo");
e.HasKey(key => key.Id);
e.HasOne(prop => prop.Folder)
.WithMany(r => r.Roles)
.OnDelete(DeleteBehavior.Cascade);
});
}
}
}
How does one specify to have this kind of relationshiop in one table? I couldn't figure out how to apply OnDelete() on this creation:
modelBuilder.Entity<RoleBindings>(e =>
{
e.ToTable(name: "RoleBindings", schema: "dbo");
e.HasKey(key => key.Id);
e.HasDiscriminator()
.HasValue<FileRoleBindings>("File")
.HasValue<FolderRoleBindings>("Folder");
});
Edit1:
When I'm trying to store everything in one table (previous code modelBuilder.Entity()) I get error
CREATE TABLE [dbo].[RoleBindings] (
[Id] uniqueidentifier NOT NULL,
[Role] int NOT NULL,
[Discriminator] nvarchar(max) NOT NULL,
[FolderId] uniqueidentifier NULL,
[FileId] uniqueidentifier NULL,
CONSTRAINT [PK_RoleBindings] PRIMARY KEY ([Id]),
CONSTRAINT [FK_RoleBindings_Folders_FolderId] FOREIGN KEY ([FolderId]) REFERENCES [hierarchy].[Folders] ([Id]) ON DELETE CASCADE,
CONSTRAINT [FK_RoleBindings_Files_FileId] FOREIGN KEY ([FileId]) REFERENCES [reports].[Files] ([Id]) ON DELETE CASCADE
);
Microsoft.Data.SqlClient.SqlException (0x80131904): Introducing FOREIGN KEY constraint 'FK_RoleBindings_Files_FileId' on table 'RoleBindings' 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.
When it's table per type I have no issues. Is there anything I can define in modelBuilder.Entity relationship to have this work with table per hierarchy? If I define relationship inside entities File/Folder then the cascade doesn't transfer over and it prevents deletion of File/Folder if RoleBindings exist for the principal entity.