We have performed flat SQL queries so far. This doesn't involve LINQ queries. If we had a mechanism that could leverage flat queries composed of LINQ to SQL queries, then we could get the best of both worlds. Fortunately, we do have built-in support in Entity Framework, and the flat SQL queries could be composed of LINQ queries, and we will cover them in detail in this section.

Let's comment the current implementation in LatestBlogs() and perform the SP implementation using flat queries composed with LINQ. Ideally, what we are trying to achieve is to get the table data using a LINQ query and compose the result with a LINQ query to perform filtering and ordering from a LINQ query (this might not be a real-world scenario, but it works well for illustration):

    public async Task<IActionResult> LatestBlogs()
    {
      //return View("Index", await _context.Blogs.FromSql("EXEC [dbo].
         [GetLatestBlogs]").ToListAsync());
      var comparisonDateTime = DateTime.Now.AddMonths(-3);
      return View("Index", await _context.Blogs
        .FromSql("Select * from dbo.Blog")
        .Where(x=>x.CreatedAt >= comparisonDateTime)
        .OrderByDescending(x=>x.Id)
        .ToListAsync());
    }

We can see the outcome as expected, and it reflects that of the earlier result set for the latest blogs in the following screenshot:

Let's dig in further and see what is happening internally and how Entity Framework is processing these queries. We might be thinking that EF Core would be retrieving blog entries and does a LINQ query with in-memory data. The following SQL Server Profiler trace will prove us wrong. If we closely watch the highlighted part of the trace, it is evident that Entity Framework is performing a LINQ to an SQL query by translating the LINQ query into the required SQL query and executing it in one shot in the database. Cool, right? The following screenshot would illustrate that the parameterised execution is translated into SQL statement:

We have looked at the mechanism to compose flat SQL queries with LINQ, but we haven't covered the Include() data, which is a powerful feature of Entity Framework. You guessed it right, we could perform the Include() operation on flat SQL queries composed of LINQ queries as well. All we need to do is have the .Include() method as usual before the .ToList() call, which translates the LINQ queries into SQL queries. The following code would consume the .Include() functionality:

    public async Task<IActionResult> LatestBlogs()
    {
      //return View("Index", await _context.Blogs.FromSql("EXEC [dbo].
         [GetLatestBlogs]").ToListAsync());
      var comparisonDateTime = DateTime.Now.AddMonths(-3);
      return View("Index", await _context.Blogs
        .FromSql("Select * from dbo.Blog")
        .Where(x=>x.CreatedAt >= comparisonDateTime)
        .OrderByDescending(x=>x.Id)
        .Include(x => x.Posts)
        .ToListAsync());
    }

We can see the outcome of the query in the following screenshots. The first screenshot shows that the total blogs returned has a count of 2, out of which the first blog has a total of two posts. We can see the blog Id used in the post matching with the Id field of the first blog:

The same is applied to the second post item inside the first blogs Posts array, which ensures that the Include() method worked as expected, returning corresponding posts to the blog item:

As an additional check, we could verify the second blog item and its corresponding single post, which was included as well:

We have seen how the Include() functionality worked with the flat SQL query composed with LINQ; let's see how it has been translated into an SQL query (LINQ to SQL). The following screenshot shows us that there were two SQL statements executed, out of which the first statement was similar to the earlier one that retrieves data from the Blog table:

The second statement does the trick for us, executing the code required to include the post table data with the LINQ query. This is depicted in the following screenshot:

We have seen how to consume flat SQL queries with LINQ to SQL queries in this section, and so far we have seen the execution of flat SQL queries with respect to DBSet or a POCO object. Let's investigate whether the same could be achieved anonymously in the Executing SQL query without a DBSet or POCO section.