Execute Raw SQL Queries using FromSqlRaw() method in Entity Framework Core

Entity Framework Core FromSqlRaw() method is used to Execute Raw SQL Queries including Parameterized Queries. This method returns an entity object. We use this method when we can’t generate queries through LINQ or when EF Core generates inefficient queries.

This tutorial is a part of Entity Framework Core series.

• 1. Introduction to Entity Framework Core
• 2. Installation of Entity Framework Core
• 3. Database-First approach in Entity Framework Core
• 4. DbContext Class in Entity Framework Core
• 5. Code-First Approach in Entity Framework Core
• 6. Migrations in Entity Framework Core
• 7. Insert Records in Entity Framework Core
• 8. Read Records in Entity Framework Core
• 9. Update Records in Entity Framework Core
• 10. Delete Records in Entity Framework Core
• 11. Conventions in Entity Framework Core
• 12. Configurations in Entity Framework Core
• 13. Fluent API in Entity Framework Core
• 14. Configure One-to-Many relationship using Fluent API in Entity Framework Core
• 15. Configure One-to-One relationship using Fluent API in Entity Framework Core
• 16. Configure Many-to-Many relationship using Fluent API in Entity Framework Core
• 17. Execute Raw SQL Queries using FromSqlRaw() method in Entity Framework Core
• 18. Execute SQL Stored Procedures using FromSqlRaw() & ExecuteSqlRawAsync() methods in Entity Framework Core
• 19. Xaero – Entity Framework Core Advanced Project
• 20. Value Converters and Comparers in Entity Framework Core

Example 1: Execute Raw SQL Query with “FromSqlRaw()” method

Consider a Employee entity:

public class Employee
{
    public int Id { get; set; }
    public string Department { get; set; }
    public string Name { get; set; }
    public string Designation { get; set; }
}

To get details of all employees that are in Development department we can Execute SQL Query by using FromSqlRaw() method like this:

var emp = context.Employee.FromSqlRaw("Select * from Employee where Department = 'Admin'").ToList();

Here Select * from Employee where Department = ‘Admin’ is the raw sql query.

Example 2: Execute Parameterized Queries with “FromSqlRaw” method

The following code shows how to execute Parameterized Query with FromSqlRaw method. It will give all the employees that have name as Tony.

string name = "Tony";
var emp = context.Employee.FromSqlRaw($"Select * from Employee where Name = '{name}'").ToList();

Using LINQ Operators with “FromSqlRaw” method

We can also use LINQ Operators after the result from FromSqlRaw() method.

The below code contains the .OrderBy() LINQ Operator that gives the result in ascending order of employee name.

var emp = context.Employee.FromSqlRaw("Select * from Employee").OrderBy(x => x.Name).ToList();

Including related data

The Include method can be used to include related data. Here we are including related Project records where Project is another entity.

var emp = context.Employee
                 .FromSqlRaw($"SELECT * FROM Employee")
                 .Include(b => b.Project)
                 .ToList();

The 2 entities are given below.

public class Employee
{
    public int Id { get; set; }
    public string Department { get; set; }
    public string Name { get; set; }
    public string Designation { get; set; }

    public Project Project { get; set; }
}

public class Project
{
    public int Id { get; set; }
    public string Name { get; set; }

    public ICollection<Employee> Employee { get; set; }
}

Scalar (non-entity) & Non-Querying SQL

SqlQuery method allows you to easily query for scalar, non-entity types. See the below example.

var ids = context.Database.SqlQuery<int>($"SELECT [Id] FROM [Employee]").ToList();

We can use ExecuteSql method in scenarios where SQL does not return any data. Example:

using (var context = new BloggingContext())
{
    var rowsModified = context.Database.ExecuteSql($"UPDATE [Employee] SET [Department] = 'Development'");
}