Execute T-SQL Statements in Entity Framework 4
Execute T-SQL Statements in Entity Framework 4
In this post I’m 
going to show a
new Entity Framework 4
feature that enable us
to execute T-SQL
from the ObjectContext
directly to the database.
Executing T-SQL Statements from ObjectContext
One of the new improvements in EF4 is the ability to execute
T-SQL store queries and commands. This ability enable the EF
developer to execute T-SQL directly against the database. This ability
should only be used in cases that Entity Framework doesn’t support
something that you need which can be a very complicated query for
example. In order to use this feature the ObjectContext supply two new
methods: ExecuteStoreQuery<T> and ExecuteStoreCommand.
Using ExecuteStoreQuery<T> Method
The ExecuteStoreQuery<T> method should be used to query
data and return it in the shape of the given generic parameter.
The method will only work if T has default constructor and also
there is a one to one match between the returned column names
and the class property names. This imply that you can use any class
that you want which confirm to the restrictions as the T generic
parameter and not only EF generated entities. If the classes are
entities you can provide the EntitySet name and a MergeOption
in order to connect the entity to the ObjectContext.
You use the method like in the following example:
class Program
{
static void Main(string[] args)
{
using (var context = new EntityFrameworkExampleEntities())
{
var query = context.ExecuteStoreQuery<Company>("SELECT * FROM Companies");
foreach (var company in query)
{
Console.WriteLine(company.CompanyName);
}
}
Console.ReadLine();
}
}
This is a very simple query that only show the concept of how to use
the method. In such queries I won’t bother to use ExecuteStoreQuery<T>
since I can use LINQ to Entities instead.
Using ExecuteStoreCommand Method
The
ExecuteStoreCommand method should be used to send a command
to the database such as update, delete or insert. The return value is
the number of rows that were affected by the command you sent.
The following is a very simple delete command:
class Program
{
static void Main(string[] args)
{
using (var context = new EntityFrameworkExampleEntities())
{
var count =
context.ExecuteStoreCommand(@"DELETE FROM Companies
WHERE [CompanyID]=4");
Console.WriteLine(count);
}
Console.ReadLine();
}
}
As in the previous example this is only a simple example.
Summary
Lets sum up, there are times that you hit a wall when you use EF.
It can occur in very complicated commands or queries that you need
in your application but LINQ to Entities can’t help you with them. In
such situations ExecuteStoreQuery<T> and ExecuteStoreCommand
can come to your help.