How to execute raw sql query with in entity framework?

Question

I'm using asp.net mvc 3 with entity framework 5. I have my .edmx file & able to interact with my database using linq or SP, but I want to run some raw sql statement. I'm trying something like this:

Using(var ctx=new HREntities())
{
  ctx.Database.ExecuteSqlCommand("insert into Employees values {0}, {1}",   model.EMPLOYEEID, model.EMPLOYEENAME);
  ctx.SaveChanges();
}

is it possible to execute sql query this way? Thanks.

http://stackoverflow.com/questions/915329/is-it-possible-to-run-native-sql-with-entity-framework — Satpal, Jun 03 '13 at 08:59
You might also have a look at my answer on http://stackoverflow.com/questions/16807334/execute-raw-sql-query-in-asp-net-mvc-database-first-mode/29147592#29147592 [1]: http://stackoverflow.com/questions/16807334/execute-raw-sql-query-in-asp-net-mvc-database-first-mode/29147592#29147592 — Murat Yıldız, Mar 19 '15 at 14:47

Pranav Labhe · Accepted Answer · 2015-07-03T05:48:20.400

You can execute the following types of queries:

SQL query for entity types which returns particular types of entities.

using (var ctx = new SchoolDBEntities())
 {

     var studentList = ctx.Students.SqlQuery("Select * from Student").ToList<Student>();

}

SQL query for non-entity types which returns a primitive data type.

using (var ctx = new  SchoolDBEntities())
{

var studentName = ctx.Students.SqlQuery("Select studentid, studentname 
    from Student where studentname='New Student1'").ToList();
}


 //Error
 using (var ctx = new SchoolDBEntities())
 {                
     //this will throw an exception
     var studentName = ctx.Students.SqlQuery("Select studentid as id, studentname as name 
        from Student where studentname='New Student1'").ToList();
 }

 //SQL query for non-entity types:
  using (var ctx = new SchoolDBEntities())
  {
       //Get student name of string type
      string studentName = ctx.Database.SqlQuery<string>("Select studentname 
    from Student where studentid=1").FirstOrDefault<string>();
  }

Raw SQL commands to the database.

      using (var ctx = new SchoolDBEntities())
      {

           //Update command
           int noOfRowUpdated = ctx.Database.ExecuteSqlCommand("Update student 
        set studentname ='changed student by command' where studentid=1");
          //Insert command
         int noOfRowInserted = ctx.Database.ExecuteSqlCommand("insert into student(studentname) 
        values('New Student')");
         //Delete command
         int noOfRowDeleted = ctx.Database.ExecuteSqlCommand("delete from student 
        where studentid=1");

       }

You can also refer this

The second way is not limited to primitive type, and complex types containing primitive types also can be used here. — Ahmad, Jun 14 '19 at 14:26

score 5 · Answer 2 · answered Jun 03 '13 at 09:15

5

This has worked!!

using (var ctx = new HR())
        {

            ctx.Database.ExecuteSqlCommand("insert into Employees values (9, 'Beverage')");

            ctx.SaveChanges();
        }

answered Jun 03 '13 at 09:15

Badhon Jain

938
6
20
38

How to execute raw sql query with in entity framework?

2 Answers2

Linked