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

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.

The FromSqlRaw() method resides in the Microsoft.EntityFrameworkCore namespace.

I have covered creating Relationship between Entities using Fluent APIs, check these tutorials:

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’.

Adding any user-provided values into a raw SQL query, care must be taken to avoid SQL injection attacks. So you should validate such values to make sure they don’t contain invalid characters, always use parameterization which sends the values separate from the SQL text.

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

Share this article -

yogihosting

ABOUT THE AUTHOR

This article has been written by the Technical Staff of YogiHosting. Check out other articles on "ASP.NET Core, jQuery, EF Core, SEO, jQuery, HTML" and more.