It is normal that we use Entity Framework in such a way that we do not have to manually write queries. That is, through the Entity Framework we execute a set of functions which end up generating the query and running it against the database. However, what happens when the query we want to execute is very specific, and EF Core is not able to generate it? One of the options we have is that we pass the query to EF Core so that it sends it to the database and executes it.

Suppose we want to Truncate table, that is, delete all its records. A direct way to do this is to pass a query to EF Core so that it executes it:

using (var context = new ApplicationDbContext())
{
    context.Database.ExecuteSqlCommand("Truncate table dbo.Students");
}

As simple as that, using the ExecuteSqlCommand function we can execute an arbitrary query. In case you want to read records from the database using your own query, you can use the FromSQL function. For example, suppose we want to select half of the records in the Students table, we only want the Id and the Birthdate, but we want them sorted by Birthdate in descending order. In addition, we want to ignore any query at the model level that we have. We can do this like this:

using (var context = new ApplicationDbContext())
{
   var students = context.Students.FromSql("SELECT TOP 50 PERCENT Id, Birthdate
   FROM Students ORDER BY Birthdate DESC").IgnoreQueryFilters()
   .Select(x => new { x.Id, x.Birthdate }).ToList();
}

In case you want to pass parameters to your queries, you can use traditional parameters, or you can also use String Interpolation. First we will talk about the traditional parameters. Suppose we want to make a simple reading of our Students table, filtering by Id. The correct way to do it is by using parameters. Like this:

var Id = 3;

using (var context = new ApplicationDbContext())
{
   var parameter = new SqlParameter("@Id", Id);
   var student = context.Students.FromSql("SELECT * from Students where Id = @Id", new SqlParameter[] { parameter }).FirstOrDefault();
}

As you can see, what we do is that we create a SqlParameter variable and place it in an array when we pass it as an argument to the FromSql function. The reason why we do it in this way is to avoid a SQL injection attack, where a malicious user could execute arbitrary queries on our database, it could even erase our entire database with this attack. Hence the importance of using parameters.

Another way to use parameters to protect us from the SQL injection attack is using String Interpolation, this we will see in the next entry.