Directly Executing Store Commands
With the Entity Framework, you can directly execute arbitrary data source commands.
The store command is executed in the context of the current transaction, if such a transaction exists. If the connection through the ObjectContext is not open before the call to one of the following methods, the Entity Framework will open the connection before executing the query and then close the connection after the query execution completes. For information about query execution, see Object Queries. The following methods belong to the ObjectContext type, for examples on how to use the methods, see How to: Directly Execute Commands Against the Data Source. The :
The ExecuteStoreCommand method executes an arbitrary command directly against the data source by using the existing connection.
The System.Data.Objects.ObjectContext.ExecuteStoreQuery.String,System.Object[]) method executes a query directly against the data source and returns a sequence of typed results.
The System.Data.Objects.ObjectContext.ExecuteStoreQuery.String,System.String,System.Data.Objects.MergeOption,System.Object[]) method executes a query directly against the data source and returns a sequence of typed results in a specific entity set together with the specified merge option so that query results can be tracked as entities. If you do not provide an entity set, the entity results are not tracked.
The System.Data.Objects.ObjectContext.Translate.Data.Common.DbDataReader) method translates a DbDataReader that contains rows of entity data to objects of the requested entity type.
The System.Data.Objects.ObjectContext.Translate.Data.Common.DbDataReader,System.String,System.Data.Objects.MergeOption) method translates a DbDataReader that contains rows of entity data to objects of the requested entity type, in a specified entity set, and with the specified merge option to use when translated objects are added to the object context. The default merge option is AppendOnly.
Materializing the Result Type
For the previous methods that take a generic result type parameter, the TResult can be a primitive type, an entity type, or any custom type. The type does not have to be defined in the Entity Framework conceptual model. If the specified type is of a type that is not defined in the conceptual model or is not a primitive type, the following mapping conventions apply.
The type:
Must not be abstract.
Must have a default constructor.
Each property of the type:
Must have a setter.
Must correspond to a primitive type in CSDL.
Must correspond to a column name in the resulting DbDataReader (the provider implementation determines whether a column has the same name as the property). If the name of the type's property does not match a field of the DbDataReader, the Entity Framework materializes the default value of the property if it is defined in the conceptutal model.
Working with Parameterized Commands
Parameterized commands help guard against SQL injection, which are attacks in which a command is "injected" into a SQL statement and compromises security on the server. Parameterized commands guard against a SQL injection attack by guaranteeing that values received from an external source are passed as values only, and not as part of the SQL statement. As a result, SQLcommands inserted into a value are not executed at the data source. Rather, they are evaluated only as a parameter value. In addition to the security benefits, parameterized commands provide a convenient method for organizing values that are passed with a SQLstatement or to a stored procedure.
When you call the ExecuteStoreCommand method or one of the ExecuteQuery overloads (ExecuteStoreQuery or ExecuteStoreQuery)and pass the commandText and parameters arguments, the Entity Framework generates a new DbCommand instance by using the current object context connection and sets the CommandText* *property to commandText and the Parameters property to parameters. The parameters value can be an array of DbParameter objects or an array of parameter values. If only values are supplied, an array of DbParameter objects is created based on the order of the values in the array. The underlying provider determines the appropriate DbType for the parameter object, based on the common language runtime (CLR) type. The ParameterName for each parameter object is built by using the following pattern: "pn" where n is the zero-based ordinal of the parameter argument.
For example, the following two method calls are equivalent:
context.ExecuteStoreQuery<Product>("select * from Products where pid = {0}", 1);
context.ExecuteStoreQuery<Product>("select * from Products where pid = @p0", new SqlParameter { ParameterName = "p0", Value = 1 });