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 FromSqlRaw() method 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 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.

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.

Xaero – Entity Framework Core Advanced Project is my latest project where I have created a full Movie Database based ASP.NET Core App in Entity Framework Core. In this project you will find lots and lots of reusable high quality codes.

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; }
}

SHARE THIS ARTICLE

  • linkedin
  • reddit
yogihosting

ABOUT THE AUTHOR

I hope you enjoyed reading this tutorial. If it helped you then consider buying a cup of coffee for me. This will help me in writing more such good tutorials for the readers. Thank you. Buy Me A Coffee donate