DEV Community

Masui Masanori
Masui Masanori

Posted on

【Entity Framework Core】Raw SQL Queries

#entityframeworkcore #aspnetcore #csharp

Intro

To improve performance, I sometimes use Raw SQL Queries.

In ASP.NET Framework(Entity Framework 6), I can do this.

using (var context = new BloggingContext()) { context.Database.ExecuteSqlCommand( "UPDATE dbo.Blogs SET Name = 'Another Name' WHERE BlogId = 1"); }
Enter fullscreen mode Exit fullscreen mode

But in ASP.NET Core, I can't use "ExecuteSqlCommand".

So in this time, I try it.

Environments

  • .NET ver.5.0.100-rc.1.20452.10
  • Microsoft.EntityFrameworkCore ver.5.0.0-rc.1.20451.13
  • Npgsql.EntityFrameworkCore.PostgreSQL ver.5.0.0-rc1
  • NLog.Web.AspNetCore ver.4.9.3

Execute Raw SQL Queries

I only can execute Raw SQL Queries through "DbSet".

... var blogs = context.Blogs .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser {0}", user) .ToList();
Enter fullscreen mode Exit fullscreen mode

But how about projection class?

For example, I want to get data by this SQL query.

SELECT c."Id", c."Name" AS "CompanyName", b."Id" AS "BookId", b."Name" AS "BookName", b."PublishDate", b."GenreId", b."Price" FROM "Companies" c INNER JOIN LATERAL(SELECT * FROM "Books" innerb WHERE innerb."CompanyId" = c."Id" ORDER BY innerb."Id" DESC LIMIT 1) b ON c."Id" = b."CompanyId"
Enter fullscreen mode Exit fullscreen mode

And I want to set into this class.

SearchedCompany.cs 

using System; using System.ComponentModel.DataAnnotations; namespace BookStoreSample.Books { public class SearchedCompany { [Key] public int CompanyId { get; set; } public string CompanyName { get; set; } = ""; public int BookId { get; set; } public string BookName { get; set; } = ""; public DateTime? PublishDate { get; set; } public int GenreId { get; set; } public decimal? Price { get; set; } } }
Enter fullscreen mode Exit fullscreen mode

I thought classes in "DbSet<T>" should match a table in the database.
But I can also use projection classes like "SearchedCompany".

BookStoreContext.cs 

using BookStoreSample.Books; using Microsoft.EntityFrameworkCore; namespace BookStoreSample.Models { public class BookStoreContext: DbContext { ... public DbSet<SearchedCompany> SearchedCompanies => Set<SearchedCompany>(); } }
Enter fullscreen mode Exit fullscreen mode

So I can execute SQL query like below.

BookSearchSample.cs 

... public async Task<List<SearchedCompany>> SearchCompaniesAsync() { var sql = "SELECT c.\"Id\", c.\"Name\" AS \"CompanyName\", " + "b.\"Id\" AS \"BookId\", b.\"Name\" AS \"BookName\", " + "b.\"PublishDate\", b.\"GenreId\", b.\"Price\" FROM \"Companies\" c " + "INNER JOIN LATERAL(SELECT * FROM \"Books\" innerb " + "WHERE innerb.\"CompanyId\" = c.\"Id\" ORDER BY innerb.\"Id\" DESC LIMIT 1) b " + "ON c.\"Id\" = b.\"CompanyId\" "; return await _context.SearchedCompanies.FromSqlRaw(sql) .ToListAsync(); } ...
Enter fullscreen mode Exit fullscreen mode

One important thing is the class must have "[Key]" or a property what is named "Id".

Skip Raw SQL Queries?

If I remove "FromSqlRaw" from the sample, can I get empty result?

... public async Task<List<SearchedCompany>> SearchCompaniesAsync() { // Don't do this return await _context.SearchedCompanies .ToListAsync(); } ...
Enter fullscreen mode Exit fullscreen mode

The answer is getting an exception.

2020-10-10 07:04:17.1997|13|ERROR|Microsoft.AspNetCore.Server.Kestrel|Connection id "0HM3CJT1I07AV", Request id "0HM3CJT1I07AV:00000002": An unhandled exception was thrown by the application. Npgsql.PostgresException (0x80004005): 42P01: relation "SearchedCompanies" does not exist at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext() ...
Enter fullscreen mode Exit fullscreen mode

Except their names, I still don't know how can I distinguish them.

// OK var companies = await _context.Companies .ToListAsync(); // Exception var searchedCompanies = await _context.SearchedCompanies .ToListAsync();
Enter fullscreen mode Exit fullscreen mode

Where clauses

Of cource I can add where clauses into "sql" as string.
But it can cause SQL injection.

Because "FromSqlRaw" also can use Linq, so if the where clauses aren't very complecated, I think I shall use Linq to add them.

BookSearchSample.cs 

... public async Task<List<SearchedCompany>> SearchCompaniesAsync() { var sql = "SELECT c.\"Id\", c.\"Name\" AS \"CompanyName\", " + "b.\"Id\" AS \"BookId\", b.\"Name\" AS \"BookName\", " + "b.\"PublishDate\", b.\"GenreId\", b.\"Price\" FROM \"Companies\" c " + "INNER JOIN LATERAL(SELECT * FROM \"Books\" innerb " + "WHERE innerb.\"CompanyId\" = c.\"Id\" ORDER BY innerb.\"Id\" DESC LIMIT 1) b " + "ON c.\"Id\" = b.\"CompanyId\" "; return await _context.SearchedCompanies.FromSqlRaw(sql) .Where(c => c.CompanyId == 1) .ToListAsync(); } ...
Enter fullscreen mode Exit fullscreen mode

According to logs, ".Where(c => c.CompanyId == 1)" was also converted into SQL queries.

Generated SQL queries 

SELECT s."Id", s."BookId", s."BookName", s."CompanyName", s."GenreId", s."Price", s."PublishDate" FROM ( SELECT c."Id", c."Name" AS "CompanyName", b."Id" AS "BookId", b."Name" AS "BookName", b."PublishDate", b."GenreId", b."Price" FROM "Companies" c INNER JOIN LATERAL(SELECT * FROM "Books" innerb WHERE innerb."CompanyId" = c."Id" ORDER BY innerb."Id" DESC LIMIT 1) b ON c."Id" = b."CompanyId" ) AS s WHERE s."Id" = 1
Enter fullscreen mode Exit fullscreen mode

Top comments (0)