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.
Gil Fink is a web development expert and ASP.Net/IIS Microsoft MVP. He is the founder and owner of sparXys. He is currently consulting for various enterprises and companies, where he helps to develop Web and RIA-based solutions. He conducts lectures and workshops for individuals and enterprises who want to specialize in infrastructure and web development. He is also co-author of several Microsoft Official Courses (MOCs) and training kits, co-author of "Pro Single Page Application Development" book (Apress) and the founder of Front-End.IL Meetup. You can read his publications at his website: http://www.gilfink.net