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:
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’.
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();
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();
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:
Share this article -