Entity Framework Tutorial Basics（39）：Raw SQL Query

However, columns returned by SQL query should match the property of an entity type of DBSet otherwise, it will throw an exception. For example:

using (var ctx = new  SchoolDBEntities())
{                
    var studentName = ctx.Students.SqlQuery("Select studentid, studentname 
        from Student where studentname=‘New Student1‘").ToList();

}

If you change the column name in query, then it will throw an exception because it must match column names:

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:

A SQL query returning instances of any type, including primitive types, can be created using the SqlQuery method on the Database class. For example:

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:

ExecuteSqlCommnad method is useful in sending non-query commands to the database, such as the Insert, Update or Delete command. For example:

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

}

Entity Framework Tutorial Basics（39）：Raw SQL Query

标签：