C#•3mo ago

Database Design with EF Core: Two Entities share another Entity

I'm building a website to store Procedures, which contains multiple Steps. Both the procedure and the steps have Media entities, a wrapper for files stored on the server. The procedure has a single media (the header picture, one-to-one), but the steps have multiple images (many-to-many). How can I handle the context setup?

1 Reply

Saiyanslayer•3mo ago

 public class ProcedureEntity : EntityWithComments {
    [Required]
    public new int Id { get; set; }
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    [Required]
    public string CreatedBy { get; set; }
    [Required]
    public string Title { get; set; }
    public string Introduction { get; set; } = string.Empty;
    public int? IntroMediaId { get; set; }
    public MediaEntity? Media { get; set; }

    public List<int> StepIds { get; set; } = [];
    [NotMapped]
    public List<StepEntity> Steps { get; set; } = [];

}

 public class ProcedureEntity : EntityWithComments {
    [Required]
    public new int Id { get; set; }
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    [Required]
    public string CreatedBy { get; set; }
    [Required]
    public string Title { get; set; }
    public string Introduction { get; set; } = string.Empty;
    public int? IntroMediaId { get; set; }
    public MediaEntity? Media { get; set; }

    public List<int> StepIds { get; set; } = [];
    [NotMapped]
    public List<StepEntity> Steps { get; set; } = [];

}

 public class StepEntity : EntityWithComments {
    [Required]
    public new int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public string Text { get; set; } = string.Empty;
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    [Required]
    public string CreatedBy { get; set; }

    public int ProcedureId { get; set; }
    [NotMapped]
    public ProcedureEntity Parent { get; set; }

    public List<int> MediaIds { get; set; } = [];
    [NotMapped]
    public List<MediaEntity> Media { get; set; } = []; 

}

 public class StepEntity : EntityWithComments {
    [Required]
    public new int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public string Text { get; set; } = string.Empty;
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    [Required]
    public string CreatedBy { get; set; }

    public int ProcedureId { get; set; }
    [NotMapped]
    public ProcedureEntity Parent { get; set; }

    public List<int> MediaIds { get; set; } = [];
    [NotMapped]
    public List<MediaEntity> Media { get; set; } = []; 

}

public enum MediaType {
    Text, Link, Youtube, Image, Pdf
}

public class MediaEntity {
    [Required]
    public int Id { get; set; }

    [Required] 
    public MediaType Type { get; set; }

    [Required]
    public string Content { get; set; } = string.Empty;

    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;

    [Required]
    public string CreatedBy { get; set; }

}

public enum MediaType {
    Text, Link, Youtube, Image, Pdf
}

public class MediaEntity {
    [Required]
    public int Id { get; set; }

    [Required] 
    public MediaType Type { get; set; }

    [Required]
    public string Content { get; set; } = string.Empty;

    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;

    [Required]
    public string CreatedBy { get; set; }

}

    protected override void OnModelCreating(ModelBuilder builder) {
        base.OnModelCreating(builder);

        builder.Entity<ProcedureEntity>()
            .HasMany(p => p.Steps)
            .WithOne(s => s.Parent)
            .OnDelete(DeleteBehavior.Cascade);

        builder.Entity<StepEntity>()
            .HasOne(s => s.Parent)
            .WithMany(p => p.Steps);

    }

    protected override void OnModelCreating(ModelBuilder builder) {
        base.OnModelCreating(builder);

        builder.Entity<ProcedureEntity>()
            .HasMany(p => p.Steps)
            .WithOne(s => s.Parent)
            .OnDelete(DeleteBehavior.Cascade);

        builder.Entity<StepEntity>()
            .HasOne(s => s.Parent)
            .WithMany(p => p.Steps);

    }

I was considering doing something similar as with the EntityWithComments class:

public abstract class EntityWithComments {
    public int Id { get; set; }
    public List<int> CommentIds { get; set; } = [];
    public virtual List<CommentEntity> Comments { get; set; } = [];
}

public class CommentEntity {
    [Required]
    public int Id { get; set; }
    public string Content { get; set; } = string.Empty;
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    public string CreatedBy { get; set; }
}

public abstract class EntityWithComments {
    public int Id { get; set; }
    public List<int> CommentIds { get; set; } = [];
    public virtual List<CommentEntity> Comments { get; set; } = [];
}

public class CommentEntity {
    [Required]
    public int Id { get; set; }
    public string Content { get; set; } = string.Empty;
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    public string CreatedBy { get; set; }
}

Also as a follow-up: what have I gotten wrong in my entity setup? Have I added anything redundant, missed something or can I make any improvements?

Gaming

Programming

Database Design with EF Core: Two Entities share another Entity