Entity Framework Learning Intermediate 3-stored procedures (middle)

Currently, the EF support for stored procedures is not perfect. The following issues exist:

L EF does not support stored procedures to return the result set of a multiple-table union query.

L EF only supports returning all fields that return a table for conversion to the corresponding entity. The return of a partial field cannot be supported.

L Although a stored procedure that returns a scalar value can be imported normally, the corresponding entity is not automatically generated for us. CS code, we still can't call or use scalar stored procedures directly in code

L EF cannot directly support parameters for output types in stored procedures.

L some other issues.

In this section, we'll learn how to add/modify stored procedures manually, and how to enable EF to support parameters of output type.

L Add/Modify stored Procedures

Sometimes, a SQL statement is more complex, but the database does not define the corresponding stored procedures. This time, we want to make the upper-level code simpler and more convenient way to complete this task. Then, at this point, we can manually add the stored procedures we need in the entity model (. edmx file). This is convenient for the upper call and convenient for later modification.

To manually modify the entity model EDMX file, add the stored procedure named Customerbycommandtext as an example. The specific steps are as follows:

Modify the Entity model file, locate the SSDL section, and add the following code:

<Function Name="CustomerByCommandText" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo" >
<CommandText>
select c.* from Customers c,Orders o where c.CustomerID=o.CustomerID
</CommandText>
</Function>

Then, locate the CSDL section and add the following code:

<FunctionImport Name="CustomerByCommandText" EntitySet="Customers" ReturnType="Collection(NorthwindModel　.Customers)"></FunctionImport>

Next, locate the MSL section and add the following code:

<FunctionImportMapping FunctionImportName="CustomerByCommandText" FunctionName="NorthwindModel　.Store.CustomerByCommandText"/>

Finally, in the. cs file of the entity model, add a method to execute this stored procedure, the code is as follows:

public global　::System.Data.Objects.ObjectResult<Customers> GetCustomerByCommandText()
{
return base.ExecuteFunction<Customers>("CustomerByCommandText");
}

At this point, the modification is complete.

Now we can use the stored procedure that we just defined manually in our code. As shown in the following code:

[Test]
public void GetCustomerByCmdText()
{
using (var db = new NorthwindEntities())
{
var csts = db.GetCustomerByCommandText().Take(10).Skip(0);
foreach (var c in csts)
Console.WriteLine(c.CustomerID);
}
}

In fact, the key is CommandText this part of the content, which is to execute the SQL statement. In addition, we can modify the entity model EMDX file, we can see all the Entity Class query SQL Statement command can be found in the edmx file, we can make the appropriate changes.