Why Entity Framework core does not support raw sql query?

Question

I was using entity framework 6 in my asp.net application and using raw queries like following:

string studentName = ctx.Database.SqlQuery<string>(
         "Select studentname from Student where studentid=1").FirstOrDefault();

Now I am moving my application to asp.net core, but I could not found raw sql query without using DbSet type.

var students = context.Students
                  .FromSql("Select * from Students where Name = 'Bill'")
                  .ToList();

But this does not solve my solutions.

Is there any extension that developed to run sql queries?

Does this answer your question? How to call a stored procedure in EF Core 3.0 via FromSqlRaw — phuzi
The real question is why are you using EF to run raw queries? You gain little by using EF this way. It would be better to use a micro-ORM like Dapper for this and avoid the overhead. Or just use the properl LINQ query, ie context.Students.Where(s=>s.Id=1).Select(s=>s.Name).Take(1) — Panagiotis Kanavos
@phuzi, the documentation says "The SQL query must return data for all properties of the entity type." and it says me can not use raw query. I mean it says me do not use entityframework anymore. — barteloma
@PanagiotisKanavos, I have some complex queries taht related multiple tables. So I was running these queries in legacy versions. So this is a so bad stuation. The developer will not move applications to cere. — barteloma

R. Jansen R. Jansen · Accepted Answer · 2019-11-06T11:21:28

I got the following from the official documentation:

The SqlQuery method on DbSet allows a raw SQL query to be written that will return entity instances. The returned objects will be tracked by the context just as they would be if they were returned by a LINQ query. For example:

using (var context = new BloggingContext())
{
    var blogs = context.Blogs.SqlQuery("SELECT * FROM dbo.Blogs").ToList();
}

Why Entity Framework core does not support raw sql query?

1 Answers