EF core migration issue

Question

Hi,
Houston ? I've got a problem with my code to create a migration.
I have two "1..* relations"

-> Summary <-> Alineas

-> Paragraph <-> Alineas
In an attempt to create SOLID & DRY code, I used an 'IAlineaHolder' interface.
One more thing: since "Alineas" are all the same, I'd like to store theme in a single table, an use a single class.
So, I'd like to use a Table Per Hierarchy pattern to store 'Alineas' in the database (SQL Server), with the 'ParentType' (Enum value) as the discriminator.

This is my code sample :

Summary (parent part of the association)

    public class Summary : IdentifierModelBase, IAlineaHolder, IParent, IDraftable, IIllustratable
    {
		//some properties

        /// <summary>
        /// Gets or sets the collection of alineas (sub-paragraphs) associated with the summary.
        /// </summary>
        [Display(Name = "Liste des alinéas")]
        public virtual ICollection<Alinea> Alineas { get; set; }
    }

Paragraph (parent part of the association)

    public class Paragraph : IllustratedTopicSubdivisionBase, IAlineaHolder, IDraftable
    {
        /// <summary>
        /// Gets or sets the collection of alineas (sub-paragraphs) associated with the paragraph.
        /// </summary>
        [Display(Name = "Liste des alinéas")]
        public virtual ICollection<Alinea> Alineas { get; set; }

        // some properties
    }

Alinea (child part of the association)

    public class Alinea : TopicSubdivisionBase
    {
        /// <summary>
        /// Specifies the parent type of an alinea.
        /// </summary>
        public enum AlineaParentType
        {
            /// <summary>
            /// The parent is a paragraph.
            /// </summary>
            [Display(Name = "Paragraphe")]
            Paragraph,

            /// <summary>
            /// The parent is a summary.
            /// </summary>
            [Display(Name = "Résumé")]
            Summary
        }

        /// <summary>
        /// Gets or sets the content of the alinea.
        /// </summary>
        [Required(ErrorMessage = @"Le champ ""{0}"" est requis.")]
        [Display(Name = "Contenu")]
        public string Content { get; set; }

        /// <summary>
        /// Gets or sets the ID of the alinea that replaced this one.
        /// </summary>
        public Guid? ReplacedById { get; set; }

        /// <summary>
        /// Gets or sets the alinea that replaced this one.
        /// </summary>
        [ForeignKey(nameof(ReplacedById))]
        public virtual Alinea? ReplacedBy { get; set; }

        /// <summary>
        /// Gets or sets the ID of the alinea that was replaced by this one.
        /// </summary>
        public Guid? ReplacesId { get; set; }

        /// <summary>
        /// Gets or sets the alinea that was replaced by this one.
        /// </summary>
        [ForeignKey(nameof(ReplacesId))]
        public virtual Alinea? Replaces { get; set; }

        /// <summary>
        /// Gets or sets the parent of the alinea, which can be either a paragraph or a summary.
        /// </summary>
        [Display(Name = "Parent")]
        [ForeignKey(nameof(ParentId))]
        public virtual IAlineaHolder Parent { get; set; }

        /// <summary>
        /// Gets or sets the type of the parent of the alinea.
        /// </summary>
        [Display(Name = "Type de parent")]
        public AlineaParentType ParentType { get; set; }
    }

I tried to create the fluent API code for this, but when I ran the 'add-migration' command, it threw an error message. It seems that it can't create the relation because I used an interface for this property.

public virtual IAlineaHolder Parent { get; set; }

My goal is:

• To not change the inheritance of 'Summary' and 'Paragraph' classes,
• To use a single 'Alinea' class, given that the 'Alinea' for both 'Summary' and 'Paragraph' share the same properties,
• To store 'Alinea' instances in a single table with Parent Id as a foreign key to 'Summary' and another foreign key to 'Paragraph' by using a discriminator (TPH - Table Per Hierarchy) approach
• To ensure SOLID and DRY code principles are followed as much as possible.

If it's not possible to achieve this, I'll explore alternative approaches. I'd like to adhere to best practices to the greatest extent possible.

Can someone help me with this?
thank you in advance.

Please, thank you for being understanding if my English is not perfect. I am a French person trying to improve my English ;-p.

Answer 1

I think that if I implement the base class like this:

    public abstract class AlineaBase : TopicSubdivisionBase, IAlinea
    {
        /// <summary>
        /// Gets or sets the content of the alinea.
        /// </summary>
        [Required(ErrorMessage = @"Le champ ""{0}"" est requis.")]
        [Display(Name = "Contenu")]
        public string Content { get; set; }

        /// <summary>
        /// Gets or sets the ID of the alinea that replaced this one.
        /// </summary>
        public Guid? ReplacedById { get; set; }

        /// <summary>
        /// Gets or sets the alinea that replaced this one.
        /// </summary>
        [ForeignKey(nameof(ReplacedById))]
        public virtual IAlinea? ReplacedBy { get; set; }

        /// <summary>
        /// Gets or sets the ID of the alinea that was replaced by this one.
        /// </summary>
        public Guid? ReplacesId { get; set; }

        /// <summary>
        /// Gets or sets the alinea that was replaced by this one.
        /// </summary>
        [ForeignKey(nameof(ReplacesId))]
        public virtual IAlinea? Replaces { get; set; }
    }
} 

It will be the same problem, since I use the IAlinea interface. I should use AlineaBase in the ReplacedBy and Replaces properties. It's a brain fucking this SOLID stuff...

Answer 2

I resolved my issue by using an alternative solution. It still uses the 'Table Per Hierarchy' pattern to store 'Alineas' in the database (SQL Server), with the 'ParentType' (Enum value) as the discriminator. But I had no other choice than to use 2 different fields for foreign keys. So here is my code now:

1. Alinea structure:

    public interface IAlinea
    {
        string Content { get; set; }
        AlineaBase? ReplacedBy { get; set; }
        Guid? ReplacedById { get; set; }
        AlineaBase? Replaces { get; set; }
        Guid? ReplacesId { get; set; }
    }


    [Table("Alinea")]
    public abstract class AlineaBase : TopicBase, IDateDisplay, IOrdered, IAlinea
    {
        /// <summary>
        /// Specifies the parent type of an alinea.
        /// </summary>
        public enum AlineaParentType
        {
            /// <summary>
            /// The parent is a paragraph.
            /// </summary>
            [Display(Name = "Paragraphe")]
            Paragraph,

            /// <summary>
            /// The parent is a summary.
            /// </summary>
            [Display(Name = "Résumé")]
            Summary
        }

        /// <summary>
        /// Gets or sets the content of the alinea.
        /// </summary>
        [Required(ErrorMessage = @"Le champ ""{0}"" est requis.")]
        [Display(Name = "Contenu")]
        public string Content { get; set; }

        /// <summary>
        /// Gets or sets the ID of the alinea that replaced this one.
        /// </summary>
        public Guid? ReplacedById { get; set; }

        /// <summary>
        /// Gets or sets the alinea that replaced this one.
        /// </summary>
        public virtual AlineaBase? ReplacedBy { get; set; }

        /// <summary>
        /// Gets or sets the ID of the alinea that was replaced by this one.
        /// </summary>
        public Guid? ReplacesId { get; set; }

        /// <summary>
        /// Gets or sets the alinea that was replaced by this one.
        /// </summary>
        public virtual AlineaBase? Replaces { get; set; }

        /// <summary>
        /// Gets or sets the end date of the contextual display period for the alinea, if applicable.
        /// </summary>
        [Display(Name = "Fin d'affichage contextuel")]
        public int? DisplayEndDate { get; set; }

        /// <summary>
        /// Gets or sets the start date of the contextual display period for the alinea, if applicable.
        /// </summary>
        [Display(Name = "Début d'affichage contextuel")]
        public int? DisplayStartDate { get; set; }

        /// <summary>
        /// Gets or sets the order in which the subdivision should be displayed within its parent.
        /// </summary>
        [Required(ErrorMessage = @"Le champ ""{0}"" est requis.")]
        [Display(Name = "N° d'ordre d'affichage")]
        [Range(1, int.MaxValue, ErrorMessage = @"Le champ ""{0}"" doit être un nombre entier supérieur à 0.")]
        public int Order { get; set; }


        public AlineaParentType ParentType { get; set; }
        [NotMapped]
        public virtual object Parent { get; set; }
    }

2. Alinea derived classes:

    public class SummaryAlinea : AlineaBase
    {
        public Guid SummaryId { get; set; }

        /// <summary>
        /// Gets or sets the parent summary of the alinea.
        /// </summary>
        [Display(Name = "Parent")]
        public virtual Summary Parent { get; set; }
    }


    public class ParagraphAlinea : AlineaBase
    {
        public Guid ParagraphId { get; set; }

        /// <summary>
        /// Gets or sets the parent paragraph of the alinea.
        /// </summary>
        [Display(Name = "Parent")]
        public virtual Paragraph Parent { get; set; }
    }

3. Alinea Parents:

    public interface IAlineasContainer<T> where T : IAlinea
    {
        /// <summary>
        /// Gets or sets the collection of Alineas.
        /// </summary>
        ICollection<T> Alineas { get; set; }
    }


    public class Summary : IdentifierModelBase, IAlineasContainer<SummaryAlinea>, IParent, IDraftable, IIllustratable
    {
        /// <summary>
        /// Gets or sets the image associated with the summary.
        /// </summary>
        [Display(Name = "Image associée")]
        public virtual ImageAssociation<IIllustratable>? Image { get; set; }

        /// <summary>
        /// Gets or sets the identifier of the parent topic associated with the summary.
        /// </summary>
        [Required(ErrorMessage = @"Le champ ""{0}"" est requis.")]
        [Display(Name = "Identifient du Sujet")]
        public Guid ParentId { get; set; }

        /// <summary>
        /// Gets or sets a value indicating whether the summary is in draft mode or not.
        /// </summary>
        [Display(Name = "Ebauche ?")]
        public bool IsDraft { get; set; } = true;

        /// <summary>
        /// Gets or sets the parent topic associated with the summary.
        /// </summary>
        [Display(Name = "Sujet")]
        public virtual Topic Parent { get; set; }

        /// <summary>
        /// Gets or sets the collection of alineas (sub-paragraphs) associated with the summary.
        /// </summary>
        [Display(Name = "Liste des alinéas")]
        public virtual ICollection<SummaryAlinea> Alineas { get; set; }
    }


    public class Paragraph : IllustratedTopicSubdivisionBase, IAlineasContainer<ParagraphAlinea>, IDraftable
    {
        /// <summary>
        /// Gets or sets a value indicating whether the paragraph is in draft mode or not.
        /// </summary>
        [Display(Name = "Brouillon ?")]
        public bool IsDraft { get; set; } = true;

        /// <summary>
        /// Gets or sets the parent article of the paragraph.
        /// </summary>
        [Display(Name = "Article")]
        [ForeignKey(nameof(ParentId))]
        public virtual Article Parent { get; set; }

        /// <summary>
        /// Gets or sets the collection of alineas (sub-paragraphs) associated with the paragraph.
        /// </summary>
        [Display(Name = "Liste des alinéas")]
        public virtual ICollection<ParagraphAlinea> Alineas { get; set; }
    }

4. Fluent API code:

            // Configuration pour l'entité AlineaBase et ses classes dérivées (SummaryAlinea et ParagraphAlinea)
            modelBuilder.Entity<AlineaBase>()
                .HasDiscriminator(x => x.ParentType)
                .HasValue<SummaryAlinea>(AlineaParentType.Summary)
                .HasValue<ParagraphAlinea>(AlineaParentType.Paragraph);

            modelBuilder.Entity<Summary>()
                .HasMany(s => s.Alineas)
                .WithOne(sa => sa.Parent)
                .HasForeignKey(sa => sa.SummaryId)
                .OnDelete(DeleteBehavior.Restrict);

            modelBuilder.Entity<SummaryAlinea>()
                .Property(sa => sa.SummaryId)
                .HasColumnName("SummaryId");

            modelBuilder.Entity<Paragraph>()
                .HasMany(p => p.Alineas)
                .WithOne(pa => pa.Parent)
                .HasForeignKey(pa => pa.ParagraphId)
                .OnDelete(DeleteBehavior.Restrict);

            modelBuilder.Entity<ParagraphAlinea>()
                .Property(pa => pa.ParagraphId)
                .HasColumnName("ParagraphId");

Answer 3

I resolved my issue by using an alternative solution. It still uses the 'Table Per Hierarchy' pattern to store 'Alineas' in the database (SQL Server), with the 'ParentType' (Enum value) as the discriminator. But I had no other choice than to use 2 different fields for foreign keys. So here is my code now:  
  
1. Alinea structure:

```C#
    public interface IAlinea
    {
        string Content { get; set; }
        AlineaBase? ReplacedBy { get; set; }
        Guid? ReplacedById { get; set; }
        AlineaBase? Replaces { get; set; }
        Guid? ReplacesId { get; set; }
    }


    [Table("Alinea")]
    public abstract class AlineaBase : TopicBase, IDateDisplay, IOrdered, IAlinea
    {
        /// <summary>
        /// Specifies the parent type of an alinea.
        /// </summary>
        public enum AlineaParentType
        {
            /// <summary>
            /// The parent is a paragraph.
            /// </summary>
            [Display(Name = "Paragraphe")]
            Paragraph,

            /// <summary>
            /// The parent is a summary.
            /// </summary>
            [Display(Name = "Résumé")]
            Summary
        }

        /// <summary>
        /// Gets or sets the content of the alinea.
        /// </summary>
        [Required(ErrorMessage = @"Le champ ""{0}"" est requis.")]
        [Display(Name = "Contenu")]
        public string Content { get; set; }

        /// <summary>
        /// Gets or sets the ID of the alinea that replaced this one.
        /// </summary>
        public Guid? ReplacedById { get; set; }

        /// <summary>
        /// Gets or sets the alinea that replaced this one.
        /// </summary>
        public virtual AlineaBase? ReplacedBy { get; set; }

        /// <summary>
        /// Gets or sets the ID of the alinea that was replaced by this one.
        /// </summary>
        public Guid? ReplacesId { get; set; }

        /// <summary>
        /// Gets or sets the alinea that was replaced by this one.
        /// </summary>
        public virtual AlineaBase? Replaces { get; set; }

        /// <summary>
        /// Gets or sets the end date of the contextual display period for the alinea, if applicable.
        /// </summary>
        [Display(Name = "Fin d'affichage contextuel")]
        public int? DisplayEndDate { get; set; }

        /// <summary>
        /// Gets or sets the start date of the contextual display period for the alinea, if applicable.
        /// </summary>
        [Display(Name = "Début d'affichage contextuel")]
        public int? DisplayStartDate { get; set; }

        /// <summary>
        /// Gets or sets the order in which the subdivision should be displayed within its parent.
        /// </summary>
        [Required(ErrorMessage = @"Le champ ""{0}"" est requis.")]
        [Display(Name = "N° d'ordre d'affichage")]
        [Range(1, int.MaxValue, ErrorMessage = @"Le champ ""{0}"" doit être un nombre entier supérieur à 0.")]
        public int Order { get; set; }


        public AlineaParentType ParentType { get; set; }
        [NotMapped]
        public virtual object Parent { get; set; }
    }

2. Alinea derived classes:

    public class SummaryAlinea : AlineaBase
    {
        public Guid SummaryId { get; set; }

        /// <summary>
        /// Gets or sets the parent summary of the alinea.
        /// </summary>
        [Display(Name = "Parent")]
        public virtual Summary Parent { get; set; }
    }


    public class ParagraphAlinea : AlineaBase
    {
        public Guid ParagraphId { get; set; }

        /// <summary>
        /// Gets or sets the parent paragraph of the alinea.
        /// </summary>
        [Display(Name = "Parent")]
        public virtual Paragraph Parent { get; set; }
    }

3. Alinea Parents:

    public interface IAlineasContainer<T> where T : IAlinea
    {
        /// <summary>
        /// Gets or sets the collection of Alineas.
        /// </summary>
        ICollection<T> Alineas { get; set; }
    }


    public class Summary : IdentifierModelBase, IAlineasContainer<SummaryAlinea>, IParent, IDraftable, IIllustratable
    {
        /// <summary>
        /// Gets or sets the image associated with the summary.
        /// </summary>
        [Display(Name = "Image associée")]
        public virtual ImageAssociation<IIllustratable>? Image { get; set; }

        /// <summary>
        /// Gets or sets the identifier of the parent topic associated with the summary.
        /// </summary>
        [Required(ErrorMessage = @"Le champ ""{0}"" est requis.")]
        [Display(Name = "Identifient du Sujet")]
        public Guid ParentId { get; set; }

        /// <summary>
        /// Gets or sets a value indicating whether the summary is in draft mode or not.
        /// </summary>
        [Display(Name = "Ebauche ?")]
        public bool IsDraft { get; set; } = true;

        /// <summary>
        /// Gets or sets the parent topic associated with the summary.
        /// </summary>
        [Display(Name = "Sujet")]
        public virtual Topic Parent { get; set; }

        /// <summary>
        /// Gets or sets the collection of alineas (sub-paragraphs) associated with the summary.
        /// </summary>
        [Display(Name = "Liste des alinéas")]
        public virtual ICollection<SummaryAlinea> Alineas { get; set; }
    }


    public class Paragraph : IllustratedTopicSubdivisionBase, IAlineasContainer<ParagraphAlinea>, IDraftable
    {
        /// <summary>
        /// Gets or sets a value indicating whether the paragraph is in draft mode or not.
        /// </summary>
        [Display(Name = "Brouillon ?")]
        public bool IsDraft { get; set; } = true;

        /// <summary>
        /// Gets or sets the parent article of the paragraph.
        /// </summary>
        [Display(Name = "Article")]
        [ForeignKey(nameof(ParentId))]
        public virtual Article Parent { get; set; }

        /// <summary>
        /// Gets or sets the collection of alineas (sub-paragraphs) associated with the paragraph.
        /// </summary>
        [Display(Name = "Liste des alinéas")]
        public virtual ICollection<ParagraphAlinea> Alineas { get; set; }
    }

4. Fluent API code:

            // Configuration pour l'entité AlineaBase et ses classes dérivées (SummaryAlinea et ParagraphAlinea)
            modelBuilder.Entity<AlineaBase>()
                .HasDiscriminator(x => x.ParentType)
                .HasValue<SummaryAlinea>(AlineaParentType.Summary)
                .HasValue<ParagraphAlinea>(AlineaParentType.Paragraph);

            modelBuilder.Entity<Summary>()
                .HasMany(s => s.Alineas)
                .WithOne(sa => sa.Parent)
                .HasForeignKey(sa => sa.SummaryId)
                .OnDelete(DeleteBehavior.Restrict);

            modelBuilder.Entity<SummaryAlinea>()
                .Property(sa => sa.SummaryId)
                .HasColumnName("SummaryId");

            modelBuilder.Entity<Paragraph>()
                .HasMany(p => p.Alineas)
                .WithOne(pa => pa.Parent)
                .HasForeignKey(pa => pa.ParagraphId)
                .OnDelete(DeleteBehavior.Restrict);

            modelBuilder.Entity<ParagraphAlinea>()
                .Property(pa => pa.ParagraphId)
                .HasColumnName("ParagraphId");