【Entity Framework Core】Raw SQL Queries

Intro

• 【PostgreSQL】【EntityFramework Core】Debugging SQL 1

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");
}

• Raw SQL Queries - EF6 | Microsoft Docs

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();

• Raw SQL Queries - EF Core | Microsoft Docs

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"

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; }
    }
}

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>();
    }
}

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();
        }
...

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();
    }
...

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()
...

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();

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();
        }
...

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