【Entity Framework Core】Raw SQL Queries 2

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"

Comments

[tenebrisAenigma]

Nice content, bro. Thanks a lot.