Execute Raw SQL in EF Core 6.0.6

Question

I need to execute SQL Command in MS SQL, which is already connected via EF Core 6.0.6.

First thing I found is: Execute RAW SQL on DbContext in EF Core 2.1

So I tried this:

db.Database.Query<Question>("COMMAND");

But it says that DatabaseFacade does not contain the Query<T>() method. Same thing with these ones:

db.Query<Question>("COMMAND");

db.Database.ExecuteSqlCommand("COMMAND");

db.Database.SqlQuery("COMMAND");

db.Database.GetDbConnection();

A little explanation:

1. db is an instance of the ApplicationDbContext class which inherits from IdentityDbContext from the Microsoft.AspNetCore.Identity.EntityFrameworkCore package

2. Question is my class, instances of which are stored in the Questions table in my database

So my main question is: Is there a way of executing raw SQL in EF Core 6.0.6?

I would be very grateful for any materials or tips in this matter.

Answer 1

Did you try this?

var questions = db.Questions.FromSqlRaw("SELECT * FROM Questions").ToList();

Make sure that you have using Microsoft.EntityFrameworkCore on top

Or if you have access to ApplicationDbContext via service provider you would try something like this:

var context = serviceScope.ServiceProvider.GetService<ApplicationDbContext>();

context.Database.ExecuteSqlRaw("SQL");

Answer 2

EF Core is getting many new and exciting features in the upcoming version.

EF7 introduced support for returning scalar types using SQL queries.

EF8 introduced support for querying unmapped types with raw SQL queries in EF8.

This is exactly what Dapper offers out of the box, and it's good to see EF Core catching up.

var ordersIn2023 = await context
    .Database
    .SqlQuery<Question>(
        $"SELECT * FROM Questions")
    .ToListAsync();

you need to update your entity framework core to version 8

resource: https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/whatsnew Raw SQL queries for unmapped types