The main idea of abstracting SQL queries from the framework is to perform implementation irrespective of the data source that we will be using in the application. We might think that creating raw SQL queries must be defeating the very purpose of EF Core's existence. There are a few valid requirements that might need raw SQL instead of leaving Entity to do the work for us.

It could be anything, something that could not be achieved through LINQ queries or performance that was not optimized by Entity-generated queries. The reason could be anything, but, at the end of the day, we all work for an outcome, an optimized outcome with better performance. We might be ready to take extreme measures in not aligning with the framework/APIs, provided the reason is substantiated more than the API usage.

We could perform basic SQL queries or execute stored procedures or functions from EF in raw mode. The framework has a provision in the DBSet through the FromSql method that does the job for us, allowing us to execute raw SQL using EF. It also allows us to create LINQ queries based on SQL queries; because was made possible since it has been exposed from the DBSet type.

Let's extend the blogging system by making the blogs controller execution into raw SQL queries, and we will begin with the Index action using a raw SQL query, displayed as follows:

    public async Task<IActionResult> Index()
    {
      return View(await _context.Blogs.FromSql("Select *
        from dbo.Blog").ToListAsync());
    }

The preceding listed implementation produces the following output in the view, and it doesn't make much difference to the existing rendered view:

We will create a new action just to illustrate the stored procedure (SP) GetLatestBlogs execution using raw SQL query EF. The implementation of an SP using FromSql is listed as follows:

    public async Task<IActionResult> LatestBlogs()
    {
      return View("Index", await _context.Blogs.FromSql("EXEC 
        [dbo].[GetLatestBlogs]").ToListAsync());
    }

The preceding listed implementation produces the following output in the view. All it does is render the SP's outcome within an existing Index view:

Let's try to return a different type other than the one being configured against the DBSet and see how EF is behaving:

    public async Task<IActionResult> Index()
    {
       return View(await _context.Blogs.FromSql("Select * 
         from dbo.Post").ToListAsync());
    }

The preceding query would trigger an invalid operation since the data returned doesn't match the type it is trying to map:

With the preceding displayed error, it is evident that something beyond the type mismatch is happening here since the error doesn't specify anything about the type mismatch.

If we have any requirement to consume anonymous types in the raw SQL queries using FromSql, we definitely won't get them until EF Core 2.0, and the same is available on the following shared link at https://github.com/aspnet/EntityFramework/issues/1862, which says:

The preceding listed responses were from a Entity Framework GitHub repository owner and member, so it's official that we might not get this feature this year.

Let's try to limit the columns returned from the database and see whether we have any impact on the application. The SELECT statement is tweaked with limited columns from the Blog entity:

    public async Task<IActionResult> Index()
    {
      return View(await _context.Blogs.FromSql("Select [Id],[Title],
       [Subtitle],[Description],[Url] from dbo.Blog").ToListAsync());
    }

The framework doesn't support inline queries that don't provide data to all the properties/fields of the data model. It would start throwing errors, as follows, until all the fields were satisfied in the SQL query:

We have seen queries or SPs without any parameters so far, and only a few cases use them in real-time applications. We will explore inline queries that accept parameters and see how we could utilize them safely in the next section.