【Entity Framework Core】Raw SQL Queries 2

using System.ComponentModel.DataAnnotations;

namespace BookshelfSample.Books.Dto;

public record SearchedBook
{
    [Key]
    public int BookId { get; init; }
    public string BookName { get; init; } = "";
    public string AuthorName { get; init; } = "";
}

using BookshelfSample.Books.Dto;
using BookshelfSample.Models.SeedData;
using Microsoft.EntityFrameworkCore;

namespace BookshelfSample.Models;

public class BookshelfContext: DbContext
{
    public BookshelfContext(DbContextOptions<BookshelfContext> options)
            : base(options) { }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Book>()
            .HasOne(b => b.Author)
            .WithMany(a => a.Books)
            .HasForeignKey(b => b.AuthorId);
        modelBuilder.Entity<Book>()
            .HasOne(b => b.Language)
            .WithMany(L => L.Books)
            .HasForeignKey(b => b.LanguageId);
        modelBuilder.Entity<Language>()
            .HasData(LanguageData.GetAll());
    }
    public DbSet<Author> Authors => Set<Author>();
    public DbSet<Book> Books => Set<Book>();
    public DbSet<Language> Languages => Set<Language>();

    // I don't want to generate "SearchedBook" table.
    public DbSet<SearchedBook> SearchedBooks => Set<SearchedBook>();
}

...
public class BookshelfContext: DbContext
{
...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
...
        modelBuilder.Entity<SearchedBook>().ToTable("searched_book", t => t.ExcludeFromMigrations());
    }
    public DbSet<Author> Authors => Set<Author>();
    public DbSet<Book> Books => Set<Book>();
    public DbSet<Language> Languages => Set<Language>();
    public DbSet<SearchedBook> SearchedBooks => Set<SearchedBook>();
}

using BookshelfSample.Books.Dto;
using BookshelfSample.Models;
using Microsoft.EntityFrameworkCore;

namespace BookshelfSample.Books;

public class SearchBooks: ISearchBooks
{
    private readonly ILogger<SearchBooks> logger;
    private readonly BookshelfContext context;

    public SearchBooks(ILogger<SearchBooks> logger,
        BookshelfContext context)
    {
        this.logger = logger;
        this.context = context;
    }
    public async Task<List<SearchedBook>> GetAsync(SearchBookCriteria criteria)
    {
        var query = this.context.SearchedBooks
            .FromSqlRaw("SELECT b.id AS \"BookId\", b.name AS \"BookName\", a.name AS \"AuthorName\" FROM book b INNER JOIN author AS a ON b.author_id = a.id");

        if(string.IsNullOrEmpty(criteria.Name) == false)
        {
            query = query.Where(b => b.BookName.Contains(criteria.Name));
        }
        if(string.IsNullOrEmpty(criteria.AuthorName) == false)
        {
            query = query.Where(b => b.AuthorName.Contains(criteria.AuthorName));
        }
        return await query
            .OrderBy(b => b.BookId)
            .ToListAsync();
    }
}

SELECT b."BookId", b."AuthorName", b."BookName"
FROM (
    SELECT b.id AS "BookId", b.name AS "BookName", a.name AS "AuthorName" FROM book b INNER JOIN author AS a ON b.author_id = a.id
) AS b
WHERE ((@__criteria_Name_1 = '') OR (strpos(b."BookName", @__criteria_Name_1) > 0)) AND ((@__criteria_AuthorName_2 = '') OR (strpos(b."AuthorName", @__criteria_AuthorName_2) > 0))
ORDER BY b."BookId"