I am using a code-first approach with Entity Framework Core and Fluent API and I am trying to create a column for a table that has a generated value based on other columns of this table. This table also contains foreign keys, which I would like to represent differently than its Id (e.g. a column called name).

This is the generic code of my situation currently.

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; }
    public int AuthorId { get; set; }
    public Author Author { get; set; }
    public string Display { get; set; }
}

public class Author
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

    public virtual ICollection<Book> Books { get; set; }
}

And the OnModelCreating in my DbContext looks like this:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    
    // BOOK Definition
    modelBuilder.Entity<Book>(entity =>
    {
        // Primary key
        entity.HasKey(p => p.Id);

        // Foreign key
        entity.HasOne(v => v.Author)
            .WithMany(v => v.Books)
            .HasForeignKey(v => v.AuthorId);

        // Properties
        entity.Property(p => p.Id).ValueGeneratedOnAdd();
        
        entity.Properties(p => p.Display)
            .HasComputedColumnSql("SOME SQL QUERY HERE?");
        
        // Other property definitions
    });
    
    // AUTHOR Definition
    modelBuilder.Entity<Author>(entity =>
    {
        // Primary key
        entity.HasKey(p => p.Id);

        // Properties
        entity.Property(p => p.Id).ValueGeneratedOnAdd();
        
        // Other property definitions
    });
}

From this website and others I understand that you can use SQL queries in HasComputedColumnSql. However, I am not sure how I would approach creating this kind of query for my use case.

TLDR;

How can I use a foreign key in a SQL query for HasComputedColumnSql, to set the computed value of a column to a column value from a referenced table (referenced by the foreign key).