Executing Raw SQL Queries using Entity Framework Core

The DbSet.FromSqlRaw method (DbSet.FromSql prior to Entity Framework Core 3.0) enables you to pass in a SQL command to be executed against the database to return instances of the type represented by the DbSet:

public class Book
{
    public int BookId { get; set; }
    public string Title { get; set; }
    public Author Author { get; set; }
    public int AuthorId{ get; set; }
    public string Isbn { get; set; }
}

...

public class SampleContext : DbContext
{
    public DbSet<Book> Books { get; set; }
}
...

using (var context = new SampleContext())
{
    var books = context.Books.FromSqlRaw("SELECT BookId, Title, AuthorId, Isbn FROM Books").ToList();
}

The DbSet must be included in the model (i.e. it can not be configured as Ignored). All columns in the target table that map to properties on the entity must be included in the SQL statement. The column names must match those that the properties are mapped to. Property names are not taken into account when the results are hydrated into instances of the entity.

If any columns are missing, or are returned with names not mapped to properties, an InvalidOperationException will be raised with the message:

'The required column '[name of first missing column]' was not present in the results of a 'FromSqlRaw' operation.'

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 a 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 a 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();