Entity Framework Tutorial Basics: Raw SQL Query

Execute Native SQL Query

You can execute native raw SQL query against the database using DBContext. You can execute the following types of queries:

1. SQL query for entity types which returns particular types of entities
2. SQL query for non-entity types which returns a primitive data type
3. Raw SQL commands to the database

SQL query for entity types:

As we have seen in one of the previous chapters, DBSet had SQLQuery () method to write Raw SQL queries which return entity Instances. The returned objects is tracked by the context, just as they would is if they were returned by a LINQ query. For example:

using (varnew  schooldbentities ()) {    var studentlist = ctx. Students.sqlquery ("select * from Student"). Tolist<student>();  }

However, columns returned by SQL query should match the property of an entity type of DBSet otherwise, it'll throw an ex Ception. For example:

using (varnew  schooldbentities ()) {                    var studentname = ctx. Students.sqlquery ("from        where studentname='New Student1' "). ToList (); }

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

using (varnew  schooldbentities ()) {                    //This wouldthrow an exception    var studentname = ctx. Students.sqlquery ("from            where studentname='New Student1' "). ToList ();}

SQL query for non-entity types:

A SQL Query Returning instances of any type, including primitive types, can is created using the SQLQuery method on the database class. For example:

using (varnew  schooldbentities ()) {    //Get student name of string type    string studentname = ctx. database.sqlquery<string> ("from        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 com Mand. For example:

using(varCTX =Newschooldbentities ()) {    //Update Command    intnoofrowupdated = ctx. Database.executesqlcommand ("Update Student            SetStudentname ='changed student by command' whereStudentid=1");    //Insert Command    intnoofrowinserted = ctx. Database.executesqlcommand ("INSERT into student (Studentname)Values'New Student')");    //Delete Command    intnoofrowdeleted = ctx. Database.executesqlcommand ("Delete from student            whereStudentid=1");}

Entity Framework Tutorial Basics: Raw SQL Query