【Entity Framework Core】Raw SQL Queries 2

#csharp #entityframeworkcore

Intro

When I wrote raw SQL with Entity Framework Core, sometimes I got unexpected results.
In this time, I will try how to avoid them.

【Entity Framework Core】Raw SQL Queries

Environments

.NET 6.0.101
Microsoft.EntityFrameworkCore ver.6.0.1
Microsoft.EntityFrameworkCore.Design ver.6.0.1
Npgsql.EntityFrameworkCore.PostgreSQL ver.6.0.2
NLog.Web.AspNetCore ver.4.14.0

Prevents tables from being generated due to migration

When I put gotten data into a class what has custom properties, I add DbSet< T> into a DbContext class.
But by default, dotnet-ef generates a new table on migration.

SearchedBook.cs

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; } = "";
}

BookshelfContext.cs

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>();
}

To exclude generating the table, I add "ExcludeFromMigrations" in "OnModelCreating".

BookshelfContext.cs

...
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>();
}

Announcing Entity Framework Core (EFCore) 5.0 RC1 - .NET Blog

Add multiple where() methods

I can add multiple where() methods.

SearchBooks.cs

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();
    }
}

According to logs, these methods are merged into one WHERE clause.

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"