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?

Weismannism

You would use methods of context.Database like context.Database.ExecuteSqlCommand() or context.Database.SqlQuery(). Which to use depends on what exactly you want to do. What would be the return value of your statement? – Deliladelilah

If the command was executed successfully, then a true is returned. Otherwise, a false. – Weismannism

What do you mean with "successful"? Can you give an example. – Deliladelilah

hmm in MySQL workbench, if I run a query (it doesn't matter if it is a select or an insert) I get a green check mark telling me it was successful and x rows were affected. So I guess I'm looking for the equivalent of the green tick? – Weismannism

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

Gobang

can I run something like DROP DATABASE IF EXISTS {databaseName}; with the .ExecuteSqlCommand? – Weismannism

@Weismannism I think you can. Any scalar DDL, T-SQL, SP, batch etc. command. Just you can't get result from SP/function/batch containing/return SELECT … – Gobang

Answer 2

You can use context.Database.ExecuteSqlCommand() to execute sql.

If you want to use SqlCommand you can get the connection by

var cnn = (SqlConnection) context.Database.GetDbConnection();
cnn.Open();
using (var cmd = new SqlCommand(sql, cnn))
using (var rdr = cmd.ExecuteReader(CommandBehavior.SingleResult))

Cyd

ExecuteSqlCommand will return the number of affected records. It will throw an exception when the statement cannot be executed. – Deliladelilah

Answer 3

You can use Database.SqlQuery() directly to context.

Here i create a generic function that can execute your query directly with target to Database rather than entity.

public static List<T> Execute<T>(MyDbContext context, string query) where T : class
{
    var result = context.Database
                        .SqlQuery<T>(query)
                        .ToList();

    return result;
}

You can use above function like

var blogs = Execute<Blog>(context, "SELECT * FROM dbo.Blogs");

Housewifery

Any sample for efcore? – Augend

@OlorunfemiAjibulu, I didn't found any solution right now. but this link may be what you want. – Housewifery

Answer 4

I adapted the function from @er-sho, it is basicaly the same. Just a couple of diferences:

• the static metod is an static extesion of DbContext class, so we an call it on multiple diferent implementations of DbContext
• the ToList is async, so the call to the DB can be asinchronus
• SqlQuery function in EF Core 8 uses FormattableString instead of string

So the extension method looks like this:

    public static async Task<List<T>> ExecuteAsync<T>(this DbContext context, FormattableString query) where T : class {
        return await context.Database.SqlQuery<T>(query).ToListAsync();
    }

And I use it like this:

public class TestingController : UmbracoApiController {
    private readonly TestDbContext _testDbContext;

    public TestingController(TestDbContext testDbContext) {
        _testDbContext = testDbContext;
    }

    public async Task<IActionResult> TestSelectToDBAsync() {
        var test = await _testDbContext.ExecuteAsync<TestTblEnityModel>(
            FormattableStringFactory.Create("SELECT * FROM [TestTbl]")
        );
        return Ok(test);
    }
}

I understand that this answer is not for the same version of EF, but I leave it here becouse maby someone can help himself with the solution...

Belsky