【Entity Framework Core】Raw SQL Queries 2
Tags: csharp, entityframeworkcore
Published: 2022-01-30 03:33:48

## 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](https://dev.to/masanori_msl/entity-framework-core-raw-sql-queries-5h4n)

### 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
```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
```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
```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](https://devblogs.microsoft.com/dotnet/announcing-entity-framework-core-efcore-5-0-rc1/)

## Add multiple where() methods

I can add multiple where() methods.

#### SearchBooks.cs
```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.

```sql
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"
```