Combining SQL Queries with LINQ Queries

We have seen different ways of executing LINQ queries and ways to execute SQL queries from EF Core DBContext in isolation, now we shall see on how to combine SQL Queries with LINQ queries and execute them as a single query.

Let us get the list of Employees from the Employee table using SQL query and use the Include extension method to get the related department details as follows.

       var employeeList = _context.Employee

           .FromSql("SELECT * FROM dbo.Employee")

           .Include(e => e.Department)

           .ToList();

We can also use other filter and order clauses in the combination queries, let us improve the above query to include a WHERE condition and an ORDER BY clause as follows.

       var employeeList = _context.Employee

           .FromSql("SELECT * FROM dbo.Employee")

           .Include(e => e.Department)

           .Where(e => e.Age > 30)

           .OrderBy(e => e.Name)

           .ToList();

If we want the query result to be used for read-only purposes like displaying the data in a report / table then we can also add the NoTracking extension method to the query.

      var employeeList = _context.Employee

          .FromSql("SELECT * FROM dbo.Employee")

          .Include(e => e.Department)

          .Where(e => e.Age > 30)

          .OrderBy(e => e.Name)

          .AsNoTracking()

          .ToList();

There are some limitations in using direct SQL queries in EF Core context.

1.       The Query should return all columns from the table (SELECT *)

2.       Query can operate only on one table we cannot use JOIN in the query, however we can join related tables using Include() method