EF Core 3.1, how to use raw sql on a database with PostgreSQL (npgsql) or any other than SQL Server

Question

For the code

public object GetRawSqlResult(string request)
{
    object result = ctx.Database.ExecuteSqlCommand(request);
    return result;
}

I get this error for ExecuteSqlCommand:

CS1061: 'DatabaseFacade' does not contain a definition for 'ExecuteSqlCommand' and no accessible extension method 'ExecuteSqlCommand' accepting a first argument of type 'DatabaseFacade' could be found (are you missing a using directive or an assembly type reference?)

There is a Database property in the Context class, but it does not give access to a direct SQL raw query (i.e., Context.Database).

The Microsoft help, Raw SQL Queries, does not tell how to do it without using a specific Context class.

I want a pure SQL command, and I do not want to pass through an entity. In hands, I only have the class name and want to verify the table exists into the database. I do not have any instance of it. There should be a way to just run a command against the database.

Just for additional information (there is no "ExecuteSqlCommand..."):

@SvyatoslavRyumkin, I would like it. Ususally if there is an async version there is also a synchronous version. Here, there is none (either sync or async / I added intellisense information at the end of my question)??? It sounds like that method and some others are additional extension method that are not included in the PostgreSQL Nuget package, but I'm not sure. — Eric Ouellet, Jun 03 '22 at 13:12
@EricOuellet what you linked to isn't the documentation. It's not related to Microsoft or the EF team at all. It's a site created by a library vendor that has several SEO-optimized sites named similar to popular frameworks. — Panagiotis Kanavos, Jun 03 '22 at 13:17
The [actual documentation](https://learn.microsoft.com/en-us/ef/core/querying/raw-sql) is always at learn.microsoft.com. In EF Core 3.1 the raw SQL commands are [ExecuteSqlRaw](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationaldatabasefacadeextensions.executesqlraw?view=efcore-3.1) and [ExecuteSqlInterpolated](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationaldatabasefacadeextensions.executesqlinterpolated?view=efcore-3.1). — Panagiotis Kanavos, Jun 03 '22 at 13:23
[ExecuteSqlCommand](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationaldatabasefacadeextensions.executesqlcommand?view=efcore-3.1) is marked obsolete which is why it doesn't appear in the Intellisense popup. All those methods are DbContext extensions and have *nothing* to do with SQL Server. The methods that expect parameters expect a DbParameter-derived object, not specifically SqlParameter. You shouldn't have any problem using `NpgsqlParameter` — Panagiotis Kanavos, Jun 03 '22 at 13:26
@PanagiotisKanavos, OK, I removed it and replace it withe Microsoft documentation but I still can't find ho to do it? — Eric Ouellet, Jun 03 '22 at 13:27
I already answered that. Don't use the obsolete ExecuteSqlCommand and use NpgsqlParameter if you really need to specify a parameter this way. In many you'll be able to pass the actual values as extra parameters — Panagiotis Kanavos, Jun 03 '22 at 13:27
@PanagiotisKanavos, Fine but I still do not have access to any "ExecuteSql..." anything command ??? You can see the last image in my question, it shows intellisense. — Eric Ouellet, Jun 03 '22 at 13:29
@PanagiotisKanavos, you are right I was missing a "using" statement : using Microsoft.EntityFrameworkCore; . Do you mind to creat an answer for it? Do you think it is a duplicate question? .. IT works fine now: object result = ctx.Database.ExecuteSqlRaw(""); — Eric Ouellet, Jun 03 '22 at 13:31

score 0 · Accepted Answer · edited Jun 30 '22 at 11:52

The actual documentation is always at learn.microsoft.com. In Entity Framework Core 3.1, the raw SQL commands are ExecuteSqlRaw and ExecuteSqlInterpolated. ExecuteSqlCommand is marked obsolete which is why it doesn't appear in the Intellisense popup. All those methods are DbContext extensions and have nothing to do with SQL Server. The methods that expect parameters expect a DbParameter-derived object, not specifically SqlParameter. You shouldn't have any problem using NpgsqlParameter.

In many cases you'll be able to pass parameter values as extra parameters though. For example:

using Microsoft.EntityFrameworkCore;
...

var id = 8;
var alwaysMinusOne = ctx.Database.ExecuteSqlRaw(
    @"SELECT * FROM ""Blogs"" WHERE ""Id"" = {0}",
    id);

or

var id = 8;
var alwaysMinusOne = ctx.Database.ExecuteSqlInterpolated(
                         $@"SELECT * FROM ""Blogs"" WHERE ""Id"" = {id}");

Thank you very much. It is a better answer than expected. My problem was a missing using statement (using Microsoft.EntityFrameworkCore;) WITH a wrong usage of ExecuteSqlCommand instead of ExecuteSqlRaw. Thanks! — Eric Ouellet, Jun 03 '22 at 13:48

EF Core 3.1, how to use raw sql on a database with PostgreSQL (npgsql) or any other than SQL Server

1 Answers1