(C# ASP.NET Core LINQ) Direct Execution of SQL Queries and Stored Procedures

EF Core translates your LINQ expressions into SQL language queries. However, sometimes it is easier, convenient and faster (as determined by testing) to run queries directly on a database. This tutorial explains how to run SELECT, UPDATE, INSERT, DELETE and execute Stored Procedures and obtain the resultset directly.

Categories | About |     |  

Parveen,

Our working set

Let us suppose our model is

// name of the table in database 
[Table("DoctorProfile")]
public class DoctorProfile
{

  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.None)]
  public int ID { get; set; }

  // name of a doctor 
  public String Name { get; set; }
}

In the following discussion we have assumed that

  1. that we have already configured our database
  2. that our DbContext instance is _ctx
  3. that our DbSet<DoctorProfile> is called Doctors

Video Explanation

Please watch the following youtube video:

How to execute a SQL Query?

Let us suppose, for simplicity, that we have to execute a query such as:


// the query to execute 
String strQuery = "SELECT * FROM doctorprofile WHERE ID = 2";

The query is a parameterized query, so follow this code


// assuming sqlite database 
// if database is SQL, then use SqlParameter 

var p_id = new SqliteParameter("@p_ID", 2);

// parameterized query of parameter called p_ID 
String strQuery = "SELECT * FROM doctorprofile WHERE id = @p_ID";

var doctors = _ctx.Doctors
    .FromSqlRaw(strQuery, p_id)
    .ToList();

// now process the list 

How to execute a Stored Procedure?

Let us suppose, for simplicity, that we have to execute a procedure such as:


String strQuery = "EXECUTE dbo.GetDoctorById @param=@p_ID";

The query is a parameterized query, so follow this code


// if database is SQL, then use SqlParameter 

var p_id = new SqlParameter("@p_ID", 2);

// parameterized query of parameter called p_ID 
String strProc = "EXECUTE dbo.GetDoctorById @param=@p_ID";

var doctors = _ctx.Doctors
    .FromSqlRaw(strProc, p_id)
    .ToList();

// now process the list 

// NOTE: if your proc returns 
// the number of rows impacted, then 
// use _ctx.Database... as explained  
// next for INSERT, UPDATE queries 
// to get the number of rows impacted 

How to execute INSERT, UPDATE and DELETE?

The query is on context and Database directly


// the query to be executed 
String strQuery = "INSERT INTO doctorprofile . . . ";

int rowsAffected = _ctx.Database.ExecuteSqlRaw(strQuery);

// the rowsAffected is the number of rows affected 

// NOTE: the query should be parameterized with 
// SqlParameter/SqliteParameter as explained above 


This Blog Post/Article "(C# ASP.NET Core LINQ) Direct Execution of SQL Queries and Stored Procedures" by Parveen is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.

Comments and Discussion