How to get filtered data from many-to-many table?

Question

C++ / C#

Here is the most simplified example. There are 3 models

public class Book
{
    public int Id { get; set; }
    [Required]
    public string Title { get; set; }
    public List<AuthorBook> AuthorsBooks { get; set; } = new List<AuthorBook>();
}

public class Author
{
    public int Id { get; set; }
    [Required]
    public string Name { get; set; }
    public List<AuthorBook> AuthorsBooks { get; set; } = new List<AuthorBook>();
}

public class AuthorBook
{
    public int AuthorId { get; set; }
    public Author Author { get; set; }
    public int BookId { get; set; }
    public Book Book { get; set; }
}

The task is to get all the books of a specific author by authorId. I wrote something like this

public IEnumerable<Book> GetBooksByAuthor(int authorId)
    => context.Books
        .Include(book => book.AuthorsBooks
            .Where(authorBook => authorBook.AuthorId == authorId))
        .ThenInclude(authorBook => authorBook.Person)
        .Where(book => book.AuthorsBooks.Count > 0);

context is a class that inherits from DbContext.
This code displays all books in general, filtering does not work. What is wrong and how to do it?

Answer 1

var query = from b in context.Books
    join au in context.AuthorBooks on b.Id equals au.BookId
    join a in context.Authors on au.AuthorId equals a.Id
    where a.Id == authorId
    select b;

var books = query.ToArray();