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.
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
This has worked!!
using (var ctx = new HR())
{
ctx.Database.ExecuteSqlCommand("insert into Employees values (9, 'Beverage')");
ctx.SaveChanges();
}