How to execute raw sql query with in entity framework?
34,001
Solution 1
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
Solution 2
This has worked!!
using (var ctx = new HR())
{
ctx.Database.ExecuteSqlCommand("insert into Employees values (9, 'Beverage')");
ctx.SaveChanges();
}
Author by
Badhon Jain
Updated on July 05, 2022Comments
-
Badhon Jain almost 2 years
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.