Entity Framework – Raw Sql Queries and Stored Procedure execution

Entity Framework – Raw Sql Queries and Stored Procedure execution
11 votes, 4.36 avg. rating (87% score)

Introduction

SQL Code snippet how to run raw SQL query and Stored Procedure in Entity Framework

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’

Summary:
        //     Creates a raw SQL query that will return elements of the given type.  The
        //     type can be any type that has properties that match the names of the columns
        //     returned from the query, or can be a simple primitive type. The type does
        //     not have to be an entity type. The results of this query are never tracked
        //     by the context even if the type of object returned is an entity type. Use
        //     the System.Data.Entity.DbSet.SqlQuery(System.String,System.Object[]) method
        //     to return entities that are tracked by the context.  As with any API that
        //     accepts SQL it is important to parameterize any user input to protect against
        //     a SQL injection attack. You can include parameter place holders 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.
        //      context.Database.SqlQuery(typeof(Post), "SELECT * FROM dbo.Posts WHERE Author
        //     = @p0", userSuppliedAuthor); Alternatively, you can also construct a DbParameter
        //     and supply it to SqlQuery. This allows you to use named parameters in the
        //     SQL query string.  context.Database.SqlQuery(typeof(Post), "SELECT * FROM
        //     dbo.Posts WHERE Author = @author", new SqlParameter("@author", userSuppliedAuthor));

Conclusion

Though basic code, but sometimes people do not know that EF can be used this way. Happy coding

November 26, 2014 В· Adi В· One Comment
Tags: , ,  В· Posted in: C#, Entity Framework, SQL

One Response

  1. Ganesh - March 12, 2015

    I want to execute Stored Procedure using EF Raw sql query, but my SP returns multiple ResultSet (multiple temporary table) and i want to bind that all result set to C# single entity.
    for e.g

    class A
    {
    int Name;
    List b;
    List c;
    }
    class B
    {
    string test1;
    }
    class C
    {
    string test2;
    }
    A a = db.Database.Sqlquery(“sp name”);

    sp going to return three temp table.
    Your answer is appreciative.

Leave a Reply

What is 7 + 11 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)