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.

Answer 1

You can execute the following types of queries:

1. 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>(); } 

2. 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>(); } 

3. 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

Answer 2

This has worked!!

using (var ctx = new HR()) { ctx.Database.ExecuteSqlCommand("insert into Employees values (9, 'Beverage')"); ctx.SaveChanges(); }