EF stored Procedures (top)

Currently, EF's support for stored procedures is not perfect. The following issues exist:
> EF does not support a stored procedure to return a result set of a multi-table union query.
> EF only supports returning all fields of a table for conversion to the corresponding entity. There is no way to support returning partial fields.
> Although a stored procedure that returns a scalar value can be imported normally, it does not automatically generate the corresponding entity for us. CS code, we still can't call or use scalar stored procedures directly in code
> EF cannot directly support parameters of the output type in stored procedures.
> Some other issues.
Here are some details on how to use stored procedures and how to store some of the fields that return entities and tables.

> Importing stored procedures and returning entities

In the VS visual designer, open the Entity model (EMDX file). Then, right click on "Customers" à "add" à "function import", then select "Stored Procedure name" and enter the function import name, select return type as entity and select Customers. As shown in the following:

After that, click "OK". After that, the stored procedure is imported. In the code we can use to change the stored procedure. As shown in the following code:

 Public void GETENTITYBYSP () {    using (varnew  northwindentities ())    {    　　 var cst = db. Getcustomerbyid ("ALFKI").　　FirstOrDefault ();    Assert.isnotnull (CST);    Console.WriteLine ("customerid:{0},contactname:{1}", CST. CustomerID, CST. ContactName);}    }

> Problems with federated query result sets

In this version of EF, there are multiple table union queries that do not support stored procedures, that is, the result set of the query, the part of the field from table A, and the other part from Table B, in which case the EF cannot be processed directly. This can be accomplished by writing two or more stored procedures. For example: the first stored procedure returns all the fields of table A, and the second stored procedure returns all the fields in table B, and then we implement the federated query in the code.
Follow the previous idea, add a stored procedure getordersbycustomerid that returns all Orders table fields, and then add a stored procedure getdetailsbycustomerid that returns the entire field of the Order Details table, and import them into the entity model.
where the getordersbycustomerid stored procedure is as follows:

 create  procedure   Getordersbycustomerid   @CustomerId  var Char  (5   Span style= "color: #0000ff;" >as  begin  set  NOCOUNT on  ;  select  *   orders where  orders. Customerid=   @CustomerId  ; end   
  

The Getdetailsbycustomerid stored procedures are as follows:

CREATE PROCEDUREGetdetailsbycustomerid@CustomerId varchar(5)        asBEGIN         SETNOCOUNT on; SELECTD.*       fromOrders O,[Order Details]DWHEREO.orderid=D.orderid andO.customerid=@CustomerId;END

After that, in our code to implement the federated query. The code is as follows:

 Public voidGETORDERBYSP () {using(vardb =Newnorthwindentities ()) {    varOrders = db. Getordersbycustomerid ("vinet").    ToList (); varDetails = db. Getdetailsbycustomerid ("vinet").    ToList (); Orders. ForEach (o= = O.order_details.attach (Details. Where (d = D.orderid = =( O.orderid))); foreach(varOrderinchorders) {Console.WriteLine (order.        OrderID); foreach(varDinchorder.    Order_Details) Console.WriteLine (D.productid); }    }}

One of the things that needs to be noted is that two stores were executed, and in memory there were two opposing objects, and they were not connected before. To do this, we need to use the Attach method to connect them (red code snippet) so that we can navigate to access the object's entity, which is the foreach (Var d in order. The order in Order_Details). Order_Details.

---the problem of returning some fields
By default, when this version of EF returns an entity using a stored procedure, it must return all fields so that EF can automatically convert the returned results to the corresponding entity. Otherwise, the "Data reader is not compatible with the specified XXX type of exception, ..." is reported.
Next, we resolve this issue by establishing a stored procedure and establishing a new entity. First, we create a stored procedure named Getcustomerandorders in the database, which is defined as follows:

CREATE PROCEDUREgetcustomerandorders asBEGIN    SETNOCOUNT on; SELECTc.customerid,c.companyname,o.orderid,o.orderdate,d.quantity fromCustomers c,orders O,[Order Details]DWHEREC.customerid=O.customerid andO.orderid=D.orderid;ENDGO

Then, add an entity customeroders, and set the properties as shown:

Then, in the VS visual designer, open the Entity model (EMDX file), by adding the à function import, import the stored procedure getcustomerandorders, and named Getcustomerandorders, The return type is set to the entity CustomerOrders.
Finally, we can code entities for this entity and stored procedures. The following code:

 Public void Getordercountbycustomerid () {    using (varnew  northwindentities ())    {    var co = db. Getcustomerandorders (). Take (ten). Skip (0);     foreach (var in Co)          Console.WriteLine (C.customerid);    }}

> Returning scalar value problems

Currently, EF support for returning scalar values for stored procedures is not complete. Although, we can follow the previous steps to import the function and set the type that returns the scalar value, and EF will automatically generate this stored procedure-related mapping configuration for us in the entity model file. However, EF does not generate the corresponding. cs code for us in the Entity Model CS file, so we simply cannot call this stored procedure directly. To solve this problem, we need to manually add code to the. CS code file of the entity model.
First, create a stored stored procedure Getordercntbycustomerid in the database, with the following code:

 create  procedure   Getordercntbycustomerid   @CustomerId  v Archar  (5  )  Span style= "color: #0000ff;" >as  begin  set  NOCOUNT on  ;  select  count  (* ) from  Orders where  Orders.customerid=   @CustomerId  ; end  

Next, follow the normal steps to import this stored procedure and set the return type to a scalar type of Int32.
Then we need to add a generic method and a method to execute the stored procedure, with the following code:

 Public Partial classnorthwindentities{//generic methods are used to perform scalar stored procedures        PrivateT executefunction<t> (stringfunctionname, system.data.entityclient.entityparameter[] parameters)whereT:struct{System.Data.EntityClient.EntityCommand cmd= ((System.Data.EntityClient.EntityConnection) This. Connection).            CreateCommand (); Cmd.commandtype=System.Data.CommandType.StoredProcedure; Cmd.            Parameters.addrange (Parameters); Cmd.commandtext= This. DefaultContainerName +"."+functionname; Try            {                if(CMD. Connection.state! =System.Data.ConnectionState.Open) cmd.                Connection.Open (); varobj =cmd.                ExecuteScalar (); return(T) obj; }            Catch(System.Exception) {Throw; }            finally{cmd.            Connection.close (); }        }        //methods for executing getordercntbycustomerid stored procedures in a database         Public intGetordercountbycustomerid (stringCustomerId) {            varparam =NewSystem.Data.EntityClient.EntityParameter ("CustomerId", System.Data.DbType.String); Param. Value=CustomerId; returnexecutefunction<int> ("Getordercountbycustomerid",New[] {param}); }}

Finally, with the above modifications, we can directly use the stored procedure that returns a scalar value, the code is as follows:

 Public void Getordercountbycustomerid () {    using (varnew  northwindentities ())    {    var result = db. Getordercountbycustomerid ("vinet");     0 );    Console.WriteLine (Result. ToString ());}    }

So far, we've solved the problem of returning scalars to the EF stored procedure.

EF stored Procedures (top)