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

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

Example 1: Execute Raw SQL Query with .FromSql() 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 all students that are in ‘Standard 10’ you can execute an SQL query using .FromSql() method like this:

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

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

Example 2: Execute Parameterized Queries with .FromSql method

The following code shows how to execute Parameterized query with .FromSql method. It will give all the students that have ‘name as Tony’.

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

Using LINQ Operators with .FromSql method

You can also use LINQ Operators after the result from .FromSql() method.

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

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

DOWNLOAD