EF Core navigation properties to both a collection and a specific item in the collection

Question

I have a main EF entity class which has a navigation property to a collection of another EF entity class in an one-to-many relationship. This second entity has a DateCreated property and a navigation reference back to the main class in a one-to-one relationshipenter code here.
For performance reasons I want to add another navigation property in the main class to the the most recent related entry in the class collection but am hitting lots of issues around foreign keys.

public class MainClass
{
   public virtual List<OtherClass> OtherClasses { get; set;}
   public virtual OtherClass LatestOtherClass { get; set; }
}

public class OtherClass
{
   public virtual MainClass { get; set;}
   public virtual DateTime DateCreated { get; set;}
}

The error I am getting is 'Unable to determine the relationship represented by navigation of 'MainClass.LatestOtherClass' of type 'OtherClass'. It is suggesting I manually configure the relationship but I have been unable to find a way to successfully do this so far without causing DbContext errors or breaking the relationship in the opposite direction.

Is this possible and if so how do I configure this?

This is against a Postgresql db if that is of any consequence.

Answer 1

I think below code will help to fix your problem.

public class MainClass
{
    public int Id { get; set; } // Primary Key for EF

    public virtual List<OtherClass> OtherClasses { get; set; }

    public int? LatestOtherClassId { get; set; } // Foreign Key to OtherClass
    public virtual OtherClass LatestOtherClass { get; set; }
}

public class OtherClass
{
    public int Id { get; set; } // Primary Key for EF

    public int MainClassId { get; set; } // Foreign Key to MainClass
    public virtual MainClass MainClass { get; set; }

    public DateTime DateCreated { get; set; }
}

public class MyDbContext : DbContext
{
    public DbSet<MainClass> MainClasses { get; set; }
    public DbSet<OtherClass> OtherClasses { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Configure one-to-many relationship between MainClass and OtherClass
        modelBuilder.Entity<MainClass>()
            .HasMany(m => m.OtherClasses)
            .WithOne(o => o.MainClass)
            .HasForeignKey(o => o.MainClassId);

        // Configure self-referencing LatestOtherClass as an optional relationship
        modelBuilder.Entity<MainClass>()
            .HasOne(m => m.LatestOtherClass)
            .WithMany() // No navigation back to MainClass from this side
            .HasForeignKey(m => m.LatestOtherClassId)
            .IsRequired(false);

        base.OnModelCreating(modelBuilder);
    }
}