Execute T-SQL Statements in Entity Framework 4

November 25, 2009

Execute T-SQL Statements in Entity Framework 4

In this post I’m Execute T-SQL Statements in Entity Framework 4
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.

DotNetKicks Image
Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

one comment

  1. Gulino BarnesNovember 25, 2009 ב 13:22

    Great Information! Thanks for sharing!

    Reply