Entity Framework - Native SQL

In Entity Framework you can query with your entity classes using LINQ. You can also run queries using raw SQL directly against the database using DbCOntext. The techniques can be appped equally to models created with Code First and EF Designer.

SQL Query on Existing Entity

The SqlQuery method on DbSet allows a raw SQL query to be written that will return entity instances. The returned objects will be tracked by the context just as they would be if they were returned by a LINQ query. For example −

class Program {

   static void Main(string[] args) {

      using (var context = new UniContextEntities()) {

         var students = context.Students.SqlQuery("SELECT * FROM dbo.Student").ToList();

         foreach (var student in students) {
            string name = student.FirstMidName + " " + student.LastName;
            Console.WriteLine("ID: {0}, Name: {1}, 	Enrollment Date {2} ",
               student.ID, name, student.EnrollmentDate.ToString());
         }

         Console.ReadKey();
      }
   }
}

The above code will retrieve all the students from the database.

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 −

class Program {

   static void Main(string[] args) {

      using (var context = new UniContextEntities()) {

         var studentNames = context.Database.SqlQuery
            <string>("SELECT FirstMidName FROM dbo.Student").ToList();

         foreach (var student in studentNames) {
            Console.WriteLine("Name: {0}", student);
         }

         Console.ReadKey();
      }
   }
}

SQL Commands to the Database

ExecuteSqlCommnad method is used in sending non-query commands to the database, such as the Insert, Update or Delete command. Let’s take a look at the following code in which student’s first name is updated as ID = 1

class Program {

   static void Main(string[] args) {

      using (var context = new UniContextEntities()) {

         //Update command

         int noOfRowUpdated = context.Database.ExecuteSqlCommand("Update 
            student set FirstMidName =  Ap  where ID = 1");

         context.SaveChanges();

         var student = context.Students.SqlQuery("SELECT * FROM
            dbo.Student where ID = 1").Single();

         string name = student.FirstMidName + " " + student.LastName;

         Console.WriteLine("ID: {0}, Name: {1}, 	Enrollment Date {2} ", 
            student.ID, name, student.EnrollmentDate.ToString());

         Console.ReadKey();
      }
   }
}

The above code will retrieve all the students’ first name from the database.