DEV Community

Masui Masanori
Masui Masanori

Posted on

【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.

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; } = ""; }
Enter fullscreen mode Exit fullscreen mode

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>(); }
Enter fullscreen mode Exit fullscreen mode

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>(); }
Enter fullscreen mode Exit fullscreen mode

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(); } }
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

Top comments (1)

Collapse
 
tenebris_aenigma profile image
tenebrisAenigma •

Nice content, bro. Thanks a lot.