【Entity Framework Core】Raw SQL Queries
Tags: entityframeworkcore, aspnetcore, csharp
Published: 2020-10-10 00:00:42

## Intro
* [【PostgreSQL】【EntityFramework Core】Debugging SQL 1](https://dev.to/masanori_msl/postgresql-entityframework-core-debugging-sql-1-31d2)

To improve performance, I sometimes use Raw SQL Queries.

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

```csharp
using (var context = new BloggingContext())
{
    context.Database.ExecuteSqlCommand(
        "UPDATE dbo.Blogs SET Name = 'Another Name' WHERE BlogId = 1");
}
```
* [Raw SQL Queries - EF6 | Microsoft Docs](https://docs.microsoft.com/en-us/ef/ef6/querying/raw-sql)

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

```csharp
...
var blogs = context.Blogs
    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser {0}", user)
    .ToList();
```

* [Raw SQL Queries - EF Core | Microsoft Docs](https://docs.microsoft.com/en-us/ef/core/querying/raw-sql)

But how about projection class?

For example, I want to get data by this SQL query.
```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"
```

And I want to set into this class.

#### SearchedCompany.cs
```csharp
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&lt;T>" should match a table in the database.
But I can also use projection classes like "SearchedCompany".

#### BookStoreContext.cs
```csharp
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
```csharp
...
        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?

```csharp
...
    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.

```csharp
// 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
```csharp
...
        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
```sql
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
```