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

Entity Framework Core has a powerful method known as FromSqlRaw() which is used to Execute Raw SQL Queries including Parameterized Queries. This method returns an entity object.

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

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

Consider a Student entity:

public class Student
{
    public int Id { get; set; }
 
    public string Name { get; set; }
 
    public int Age { get; set; }
 
    public int Standard { get; set; }
 
    public string Address { get; set; }
}

To get details of all students that are in Standard 10 you can execute an SQL query using FromSqlRaw() method like this:

var context = new SchoolContext();
var students = context.Student.FromSqlRaw("Select * from Student where Standard = 10").ToList();

Here your raw query – Select * from Student where Standard = 10 will be executed on the database and will give list of all students that are in ‘standard 10’.

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 students that have name as Tony.

var context = new SchoolContext();

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

Using LINQ Operators with “FromSqlRaw” method

You 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 Student’s name.

var context = new SchoolContext();

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

Including related data

The Include method can be used to include related data.

var stuTeacher = context.Student
                        .FromSqlRaw($"SELECT * FROM Student")
                        .Include(b => b.Teacher)
                        .ToList();

Download the source code:

DOWNLOAD