TheLazy.Dev
Portfolio GitHub

Blog

entityframeworkcore |aspnetcore |csharp

【Entity Framework Core】Raw SQL Queries

masanori_msl

Masui Masanori

Posted on October 10, 2020

【Entity Framework Core】Raw SQL Queries

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
💖 💪 🙅 🚩
masanori_msl
Masui Masanori

Posted on October 10, 2020

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

【Entity Framework Core】Raw SQL Queries
entityframeworkcore 【Entity Framework Core】Raw SQL Queries

October 10, 2020