In this blog am going to demonstrate how to use the SQL query in Linq. Can we achieve this in Linq? Yes! It's possible we can do this using the SqlQuery extension method. Let us see the example.
 
SqlQuery Syntax
 
public virtual DbSqlQuery<TEntity> SqlQuery(string sql, params object[] parameters); 
  • The first parameter is the SQL query
  • The second parameter represents that you can include parameter placeholders in the SQL query string and then supply parameter values as additional arguments. Any parameter values you supply will automatically be converted to a DbParameter and it's an optional parameter
Example
 
First, we will see without the second parameter, for this, I have created a table in the database and configured entity framework in sample application with CRUDEntities context name.
 
 
  1. CRUDEntities cd = new CRUDEntities();  
  2. var linqquery = cd.Employees.ToList();  
  3. var sqlQuery = cd.Employees.SqlQuery("select * from Employee").ToList();  
Result
 
 
 
Now we will see how to pass the parameter, here am going to filter the employee by ID. In the below query, I used the SQL parameter to avoid the SQL injection.
  1. var linqquery1 = cd.Employees.Where(a => a.ID == 1).ToList();  
  2. var sqlQuery1 = cd.Employees.SqlQuery("select * from Employee where ID =@ID"new System.Data.SqlClient.SqlParameter("@ID", 1)).ToList();  
  3. (OR)   
  4. var sqlQuery11 = cd.Employees.SqlQuery("select * from Employee where ID =1").ToList();   
 
 
 I hope it's helpful for you. Have a great day.
Next Recommended Reading
LINQ - Creating Query like in SQL