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

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