Execute T-SQL Statements and stored using Entity framework 4

EF4 provides us with the ability to execute T-SQL stored and T-SQL statements, this should use only if you have a complex query and EF doesn’t support you, so EF4 provide tow new method in ObjectContext Feature ( ExecuteStoreQuery and ExecuteStoreCommand ) .

We will create an example to show how to use these methods

<!--[if !supportLists]-->1- <!--[endif]-->Create a small db contain one table like this.

<!--[if !supportLists]-->2- <!--[endif]-->Open your vs 2010, create a console application for test

<!--[if !supportLists]-->3- <!--[endif]-->Add Entity data model. (Add >> Add new item >> chose your data connection ) <!--[if !vml]-->

<!--[endif]-->

<!--[if !supportLists]-->4- <!--[endif]-->Chose your database objects (in our example chose employee table )

<!--[if !vml]-->

<!--[if !supportLists]-->A) <!--[endif]-->Using ExecuteStoreQuery method

static void Main(string[] args)

{

var context = new EF4_TestEntities();

ObjectResult<Employee> result = context.ExecuteStoreQuery<Employee>("Select * from Employees");

foreach (var emp in result)

{

Console.WriteLine(emp.Name);

}

Console.ReadLine();

}

<!--[if !supportLists]-->· <!--[endif]-->This method should be used to query data and return it in the shape of the given generic parameter.

<!--[if !supportLists]-->· <!--[endif]-->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.

<!--[if !supportLists]-->· <!--[endif]--> This implies 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.

<!--[if !supportLists]-->B) <!--[endif]-->Using ExecuteStoreCommand Method.

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.

static void Main(string[] args)

{

var context = new EF4_TestEntities();

Console .WriteLine( ""+ context.ExecuteStoreCommand("delete Employees")) ;

Console.ReadLine();

}

There are queries that you need in your application but EF not applying you I think with EF4 you find it easy to embedded it now J thx EF4