Entity SQL

• Entity SQL is a storage-independent query language that is similar to SQL.
  • It allows you to query entity data, either as objects or in a tabular form.
• You should consider using Entity SQL in the following cases:
  1. When a query must be dynamically constructed at runtime.
     • Also consider using the query builder methods of ObjectQuery<T> instead of constructing an Entity SQL query string at runtime.
  2. When you want to define a query as part of the model definition.
     • Only Entity SQL is supported in a data model.
  3. When using EntityClient to return read-only entity data as rowsets using a EntityDataReader.
• It is processed by the Entity Framework’s Object Services directly.
• It returns ObjectQuery instead of IQueryable.
• You need ObjectContext to create a query using Entity SQL.

using (var db = new DbContext("..."))
{
    ObjectContext objectContext = ((IObjectContextAdapter)db).ObjectContext;
    ObjectQuery<Student> student = objectContext.CreateQuery<Student>("Query...");
    var newStudent = student.First();                
}

using (EntityConnection connection = new EntityConnection("..."))
{
    connection.Open();
    EntityCommand command = connection.CreateCommand();
    command.CommandText = "...";
    using (EntityDataReader rdr = command.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection))
    {
        while (rdr.Read())
            Console.WriteLine("{0} - {1}", rdr.GetInt32(0), rdr.GetString(1));
    }
}

Native SQL

• 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 (Entity Types).
  2. SQL query for non-entity types which returns a primitive data type (Non-Entity Types)
  3. Raw SQL commands to the database.

Entity Types

• DBSet has SQLQuery() method to write raw SQL queries which return entity instances.
  • The returned objects will be tracked by the context, just as they would be if there were returned by a LINQ query.
• Columns returned by SQL query should match the property of an entity type of DBSet, otherwise it will throw an exception.

var students = db.Students.SqlQuery("Select * FROM Student").ToList();
var student = db.Students.SqlQuery("Select * FROM Student WHERE StudentName = 'Bill'").FirstOrDefault();

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.

var studentName = db.Database.SqlQuery<string>("Select studentname from Student where studentid=1").FirstOrDefault<string>();

Raw SQL Commands

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

// Update command
int noOfRowUpdated = db.Database.ExecuteSqlCommand("Update Student SET StudentName ='changed' WHERE StudentId = 1");
	
// Insert command
int noOfRowInserted = db.Database.ExecuteSqlCommand("INSERT into student(studentname) values('New')");
	
// Delete command
int noOfRowDeleted = db.Database.ExecuteSqlCommand("DELETE FROM Student Where Studentid = 1");