One of the new features that EF feature CTP5 supplies is the support for raw SQL Query/Command through the DbContext
. In this post, I’m going to show you examples for how you can use this feature. Pay attention to the fact that the details I provide might change in the future since it's only a CTP and not a release.
DbContext Raw SQL Query/Command Support
EF feature CTP5 supports the execution of raw SQL queries and commands through the DbContext
. This behavior resembles the ObjectContext
s same functionality that is exposed by the ExecuteStoreQuery<T>
and ExecuteStoreCommand
methods. You can use the DbContext
s SqlQuery
and SqlCommand
methods which are exposed by the DbContext.Database
property. The results of the method executions can be materialized into entities that can be tracked by the DbContext
. Here is an example of how to use these methods:
class Program
{
static void Main(string[] args)
{
using (SchoolEntities context = new SchoolEntities())
{
IEnumerable sqlQuery = context.Database.SqlQuery(typeof(Department),
"select * from department");
foreach (Department item in sqlQuery)
{
Console.WriteLine(item.Name);
}
sqlQuery = context.Database.SqlQuery(typeof(Course), "GetCoursesOrderByTitle");
foreach (Course item in sqlQuery)
{
Console.WriteLine(item.Title);
}
var numberOfAffectedRows = context.Database.SqlCommand(
"DeleteDepartment @DepartmentID", new SqlParameter("DepartmentID", 8));
Console.WriteLine("Number of affected rows: {0}", numberOfAffectedRows);
}
}
}
In the program that is using the same model from my previous example, I’m executing two queries (one is raw SQL and the other is using a stored procedure) and one command. The result of the execution is as shown below:
Summary
Let's sum up - the new CTP enables the running of raw SQL queries/commands. This can be very useful in scenarios that aren’t enabled by EF or advanced scenarios.