EF Core Use .Include with Parent-Child-relation in same table

Question

I have a table [dbo].[Product] like

ProductID | Description | OtherProductVariantID

where OtherProductVariantID is a ProductID of the same table and the corresponding Product entity

public class Product
{
        public int ProductID {get; set;}
        public string Description{get; set;}

        public int? OtherProductVariantID {get; set;}
        public virtual Product VariantProduct {get; set;}
        public virtual ICollection<Product> VariantProducts { get; set; }
}

And it is configured via Fluent-API like this:

modelBuilder.Entity<Product>(entity =>
{
        entity.HasOne(d => d.VariantProduct)
                .WithMany(p => p.VariantProducts)
                .HasForeignKey(d => d.OtherProductVariant)
                .HasConstraintName("FK_Product_OtherProductVariant");
});

[In human language]

What I wanted is to load a Product entity with

var product = Context.Product
        .Include(p => p.VariantProduct)
        .FirstOrDefault(p => p.ProductID == 12345);

so that my VariantProduct is available for further processing, but it is always null.

What did I do wrong or is this somehow not possible?

Thanks in advance!

Answer 1

the solution to your problem:

    modelBuilder.Entity<Product>(entity =>
    {
        entity.HasOne(d => d.VariantProduct)
                .WithMany(p => p.VariantProducts)
                .HasForeignKey(d => d.OtherProductVariantID )
                .HasPrincipalKey(x=> x.ProductId);
    });

this code worked well for me!