Execute RAW SQL on DbContext in EF Core 2.1

Question

I have researched this and always found examples like this:

var blogs = context.Blogs
    .FromSql("SELECT * FROM dbo.Blogs")
    .ToList();

The problem is, I don't want to run my raw SQL on the Blogs table. Basically, I want to implement an interface like this:

bool ExecuteNonSafeSql(
    string connectionString,
    string sql);

Is there a way to do that with DbContext?

Answer 1

At the time of writing (EF Core 2.1), there is no way to execute arbitrary sequence returning SQL command.

Only entity types and Query Types are supported via FromSql.

So the closest solution is to define query type (a class holding the query result) and use FromSql, but it's not generic - the query types must be registered in the DbContext via fluent API and the method should receive a generic argument specifying that type, e.g

class ResultType
{
   // ...
}

then

modelBuilder.Query<ResultType>();

and finally

db.Query<ResultType>().FromSql(...)

Note that Database.ExecuteSqlCommand can execute arbitrary SQL, but can't be used to return sequence (IEnumerable<T>, IQueryable<T>).