Execute SQL query on existing entity

using (var context = new MyDBContext()) 
{ 
var posts = context.Posts.SqlQuery("SELECT * FROM dbo.Posts").ToList(); 
}

ToList() is mandatory here, otherwise query will not be executed, make sure you take care of sql injection attack if raw query is used

Execute Stored Procedure on existing entity

using (var context = new MyDBContext()) 
{ 
var posts = context.Posts.SqlQuery("dbo.spGetTopPosts").ToList(); 
}

 

ToList() is mandatory here, otherwise query will not be executed. Above code will execute Stored Procedure ‘spGetTopPosts’

Execute Stored Procedure with parameters on existing entity

using (var context = new MyDBContext()) 
{ 
var postID = 99; 
var posts = context.Posts.SqlQuery("dbo.spGetTopPosts @p0", postID).Single(); 
}

Single() is mandatory here, otherwise query will not be executed. Above code will execute Stored Procedure ‘spGetTopPosts’ with input paramter as postID

Execute SQL query on non-existing entity

using (var context = new MyDBContext()) 
{ 
var postTitles = context.Database.SqlQuery<string>("SELECT Title FROM dbo.Posts").ToList(); 
}

 

Execute SQL query by passing parameters

This is more better raw query as it avoid sql injections

using (var context = new MyDBContext()) 
{ 
var userSuppliedAuthor = new SqlParameter("@author", "Adi");
context.Database.SqlQuery(typeof(Post), "SELECT * FROM dbo.Posts WHERE Author = @author", userSuppliedAuthor);
}

Here the sql statement is executed on Posts table, so typeof(Post) is used. If a join statement is used on two different tables, then need to write an internal class for the returned values of sql statement.

Consider Posts, Category, Posts_Category tables exists. Posts_Category is mapping table of Posts – Id column and Category – Id column. If we want to execute sql join statement use the below code

internal class MappingData
{
public string CategoryTitle { get; set; }
public string PostTitle { get; set; }
public long? MappingId { get; set; }
}

using (var context = new MyDBContext())
{
var userSuppliedId = new SqlParameter("@PostId", PostID);
string sqlQuery = @"select c.Name CategoryTitle, pcm.Id MappingId, p.Title PostTitle from Posts_Categories pcm 
join Categories c on pcm.CategoryId = c.Id
join Posts p on pcm.PostId = p.Id where pcm.PostId =@PostId";
var Results = db.Database.SqlQuery<MappingData>(sqlQuery,userSuppliedId).ToList();
}
Results will be list of Categories of the given Post

Execute update SQL statment on non-existing entity

using (var context = new MyDBContext()) 
{ 

context.Database.ExecuteSqlCommand( 
"UPDATE dbo.Posts SET Title = 'Updated Title' WHERE PostID = 99"); 
}
For better understanding, summary extract of the method ‘SqlQuery’