如何在实体框架中执行原始SQL查询？

Question

我正在使用带有实体框架5的asp.net
mvc3。我有.edmx文件，并能够使用linq或SP与数据库交互，但是我想运行一些原始sql语句。我正在尝试这样的事情：

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

是否有可能以这种方式执行sql查询？谢谢。

Answer 1

您可以执行以下类型的查询：

1. 对实体类型的SQL查询，该查询返回特定类型的实体。

   using (var ctx = new SchoolDBEntities())
   

   {

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

   }

2. 非实体类型的SQL查询，该查询返回原始数据类型。

   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 (“Select studentname
   from Student where studentid=1”).FirstOrDefault ();
   }

3. 将原始SQL命令发送到数据库。

         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");
   
      }
   

你也可以参考这个