Entity Framework - CreateQuery VS ExecuteFunction VS ExecuteStoreQuery VS ExecuteStoreCommand

Question

What are the differences between following-

CreateQuery()  ExecuteFunction(), ExecuteStoreQuery() and ExecuteStoreCommand() 

As per my knowledge CreateQuery is used for Entity SQL & rest of the methods are used for sql function or stored procedure defined in DB.

As per ObjectContext class meta data they are as follow:

CreateQuery():Creates an System.Data.Objects.ObjectQuery<T> in the current object context        by using the specified query string. 
Returned -> System.Data.Objects.ObjectQuery<T>


ExecuteFunction(): Executes a stored procedure or function that is defined in the data source and expressed in the conceptual model; discards any results returned from
the function; and returns the number of rows affected by the execution.
Returned -> The number of rows affected.
This has an overloaded version which return -> The entity type of the System.Data.Objects.ObjectResult<T> 


ExecuteStoreCommand(): Executes an arbitrary command directly against the data source using the existing connection.
Return -> The number of rows affected.


ExecuteStoreQuery(): Executes a query directly against the data source that returns a sequence of typed results.
Return -> An enumeration of objects of type TResult.

As per above information -

Use ExecuteFunction() if you have added db Function/Stored Procedure in your EDMX & can be used for both insert/update & getting result set.

Use ExecuteStoredCommand() if you have not added db Function/Stored Procedure in your EDMX & can be used to insert/update only.

ExecuteStoreQuery() can do what Executefuction() can do except that you no need to add your db Function/Stored Procedure in EDMX & IEnumerable can be used as return type.

Please correct me if I am wrong. Any further information will be highly appreciated.

Answer 1

Very similar to CreateQuery would be following:

var id = 42;
using(var ctx = new Entities()){
  var query = ctx.Companies.Where(o=>o.Id==id);
  var result = query.First();
}

Before I call First() query is just a query. Nothing has been sent to the database. Only when data is asked will the query get executed and data retrieved.

Writing lambda is easy, but lets say you can sacrifice that for other benefits. If EDMX is not aware of your data mapping you basically can only use ExecuteStoreQuery. Ex. you have manually created mapping.

var queryString = "SELECT ... FROM " + tableName;
var table = context.ExecuteStoreQuery<ResultTableTemplate>(queryString );

Writing code for your application is fine, but sometimes database outlive several User Interfaces. In order to minimize amount of work needed to be done you can store some if not all functionality in the database. They can be stored there mainly as functions or procedures.

ExecuteFunction is only for function imports. Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e. no INSERT or UPDATE statements allowed). To call a custom function Select Today() in C# :

var date = ctx.ExecuteFunction<DateTime>("Today").First();

Entity Framework supports three ways to load related data - eager loading, lazy loading and explicit loading. By default it is lazy loading, that is why you see me using .First()/.FirstOrDefault/.ToList/... for more information on how to load data as you need you can take a look at Loading Related Entities.

Procedures are like database batch scripts. If you use database first design then most likely most of your business logic will be stored in procedures. You would call them in c# like :

var cmdText = "[DoStuff] @Name = @name_param, @Age = @age_param";
var @params = new[]{
   new SqlParameter("name_param", "Josh"),
   new SqlParameter("age_param", 45)
};

ObjectContext.ExecuteStoreQuery<MyObject>(cmdText, @params);

Bonus:

One common thing you would want to do is to call a stored procedure that accepts a table value parameter.