EF Core many to many of same object get error

Question

My situation is that I have a system which contain invoices and for each invoice I can create a receipt.

The relationship is many to many because I can produce a few receipts for each invoice, and also when I create the receipt I can relate it to few invoices.

I have the following two classes: Document which represent the invoice or receipt, and DocumentOffset which associates between the documents.

public class Document
{
    public Document()
    {
    }

    public int ID { get; set; }
    public string Reference { get; set; }
    [Required]
    public int? DocumentKind { get; set; }
    [Required]
    public long DocumentNum { get; set; }
    [Required]
    public DateTime? CreateDate { get; set; }
    [Required]
    public int? EntityID { get; set; }
    public double TaxDeduction { get; set; }
    public int Amount { get; set; }

    public double SumBeforeDiscount
    {
        get
        {
            return Document2List.Sum(x => x.Sum);
        }
    }

    public double DiscountPercent { get; set; }

    public double DiscountValue 
    { 
        get
        {
            return SumBeforeDiscount * DiscountPercent / 100;
        }
    }

    public double SumBeforeTax 
    {
        get
        {
            return SumBeforeDiscount - DiscountValue;
        }
    }

    public int TaxPercent { get; set; } = 17;
    public double TaxValue 
    {
        get
        {
            return SumBeforeTax * TaxPercent / 100;
        }
    }

    public double Sum 
    {
        get
        {
            if(DocumentKind == (int)Enums.DocumentKind.eDocumentKind.Reciept || DocumentKind == (int)Enums.DocumentKind.eDocumentKind.SupplierReciept)
            {
                return Document3List.Sum(x => x.Sum).Value;
            }

            return SumBeforeTax + TaxValue;
        }
    }

    public double Paid 
    {
        get
        {
            return Document3List.Where(x => x.Sum.HasValue).Sum(x => x.Sum.Value);
        }
    }

    public double Balance 
    {
        get
        {
            return Sum - Paid;
        }
    }

    [Required]
    public string Details { get; set; }
    [Required]
    public DateTime? Expire { get; set; }
    public string VehicleID { get; set; }

    public List<Document2> Document2List { get; set; } = new List<Document2>(); // Document items
    public List<Document3> Document3List { get; set; } = new List<Document3>(); // Document payments when invoice and receipt produced in the same document
    public Entity Entity { get; set; }

    public ICollection<DocumentOffset> DocumentOffsets { get; set; } //this property
}

and

public class DocumentOffset
{
    public int DocumentID { get; set; } //FK
    public Document Document { get; set; }
    public int RelatedDocumentID { get; set; } //FK
    public Document RelatedDocument { get; set; }
    public double Sum { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
        modelBuilder.Entity<MissionCardWorker>().HasKey(x => new { x.MissionCardId, x.WorkerId });
        modelBuilder.Entity<MissionCardItems>().HasKey(x => new { x.MissionCardId, x.ItemId });
        modelBuilder.Entity<MissionCardMission>().HasKey(x => new { x.MissionCardId, x.MissionId });

        modelBuilder.Entity<DocumentOffset>()
            .HasOne(x => x.Document)
            .WithMany(x => x.DocumentOffsets)
            .HasForeignKey(x => x.RelatedDocumentID);

        modelBuilder.Entity<DocumentOffset>()
            .HasOne(x => x.RelatedDocument)
            .WithMany(x => x.DocumentOffsets)
            .HasForeignKey(x => x.DocumentID);
 }

When I try to add a migration I get the following error :

Cannot create a relationship between 'Document.DocumentOffsets' and 'DocumentOffset.RelatedDocument', because there already is a relationship between 'Document.DocumentOffsets' and 'DocumentOffset.Document'. Navigation properties can only participate in a single relationship.

What should I do resolve that?

Thanks :)

Answer 1

Usually, for a many-to-many join you'd follow a pattern like;

public class Parent{
   public int Id { get; set; }
   public virtual ICollection<Join> Join { get; set; }
}
public class Join{
   public int ParentId { get; set; }
   public int ChildId { get; set; }
   public virtual Parent Parent { get; set; }
   public virtual Child Child { get; set; }

}
public class Child{
   public int Id { get; set; }
   public virtual ICollection<Join> Join { get; set; }
}

modelBuilder.Entity<Join>()
            .HasOne(x => x.Parent)
            .WithMany(x => x.Join)
            .HasForeignKey(x => x.ParentId);

modelBuilder.Entity<Join>()
            .HasOne(x => x.Child)
            .WithMany(x => x.Join)
            .HasForeignKey(x => x.ChildId);

But in your case you're trying to merge Parent and Child into the same type. You still need the same number of foreign keys and Navigation properties, but of course they'll all need to be unique.

public class Document{
   public int Id { get; set; }
   public virtual ICollection<DocumentOffset> Parents { get; set; }
   public virtual ICollection<DocumentOffset> Children { get; set; }
}
public class DocumentOffset{
   public int ParentId { get; set; }
   public int ChildId { get; set; }
   public virtual Document Parent { get; set; }
   public virtual Document Child { get; set; }
}

modelBuilder.Entity<DocumentOffset>()
            .HasOne(x => x.Parent)
            .WithMany(x => x.Children)
            .HasForeignKey(x => x.ParentId);

modelBuilder.Entity<DocumentOffset>()
            .HasOne(x => x.Child)
            .WithMany(x => x.Parents)
            .HasForeignKey(x => x.ChildId);