EF Core/SQL根据条件连接不同的实体

using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; }
}

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

public class LibraryContext : DbContext
{
    public DbSet<Author> Authors { get; set; }
    public DbSet<Book> Books { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("YourConnectionStringHere");
    }
}

public class Program
{
    public static void Main()
    {
        using (var context = new LibraryContext())
        {
            var query = from book in context.Books
                        join author in context.Authors
                        on book.AuthorId equals author.Id
                        where author.Name.Contains("Rowling")
                        select new { BookTitle = book.Title, AuthorName = author.Name };

            foreach (var item in query)
            {
                Console.WriteLine($"{item.BookTitle} by {item.AuthorName}");
            }
        }
    }
}