How to generate an entity from a stored procedure using EF

If you want to generate an entity from a stored procedure, you can do this using Entity Framework. Here’s how.

Add a new ADO.NET Entity Data Model item and generate the entity model from the required database as usual. In the Choose Your Database Objects step of the Entity Data Model Wizard, select the required stored procedures and click Finish.

Next, double click the generated .edmx node in Solution Explorer to open the EF designer, right click the designer surface and select Model Browser. In the Model Browser tab that appears, under the XXXModel.Store node (under the top .edmx node), right click the stored procedure and select the option Add Function Import. This will bring up the following dialogue.

To generate an entity that will contain all the columns returned from the stored procedure, click Get Column Information (which retrieves the schema from the stored procedure), then click Create New Complex Type to generate a type that returns all columns, or Entities to map the results to an existing entity. You can rename the type if required.

Now we can use this. If using Web Forms, we can set a grid view’s datasource using the method generated for the entity:

protected void Page_Load(object sender, EventArgs e)
{
   if (!Page.IsPostBack)
   {
      using (var myEntity = new Entities())
      {
         GridView1.DataSource = myEntity.procSupplierDepotList();
         GridView1.DataBind();
      }
   }
}

If the stored procedure takes input parameters, the generated method will expect corresponding arguments.

GridView1.DataSource = myEntity.procSupplierDepotList(id);

This will automatically generate the grid view columns from the data fields returned by the generated method. To display a subset of the fields, you can either set AutoGenerateColumns to false and specify the columns, e.g.

<asp:GridView ID="GridView1" runat="server” AutoGenerateColumns="false">
<Columns>
<asp:TemplateField HeaderText="Name"><ItemTemplate><asp:Literal runat="server" Text='<%# Eval("SupplierName") %>' /></ItemTemplate></asp:TemplateField>
<asp:TemplateField HeaderText="Code"><ItemTemplate><asp:Literal runat="server" Text='<%# Eval("SupplierCode") %>' /></ItemTemplate></asp:TemplateField>
</Columns>
</asp:GridView>

Alternatively, you can create an entity in the entity model that contains just the fields you want to display and set the generated method to return a collection of this entity. Note that you can modify the return value by double clicking the method in the Function Imports node.