CODEWITHSUNDEEP
c#sqlentity-framework-coreef-core-2.1
J86
J86

Reputation: 15237

Execute RAW SQL on DbContext in EF Core 2.1

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?

Upvotes: 9

Views: 26462

Answers (4)

BRap
BRap

Reputation: 625

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

Upvotes: 0

Ivan Stoev
Ivan Stoev

Reputation: 205539

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

Upvotes: 8

Thom Kiesewetter
Thom Kiesewetter

Reputation: 7304

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

Upvotes: 12

er-sho
er-sho

Reputation: 9771

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");

Upvotes: 5

Related Questions