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

For 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 direct SQL raw query (ie: Context.Database.).

The Microsoft help:

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

CodePudding user response：

The actual documentation is always at docs.microsoft.com. In EF 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);

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