Entity Framework - Stored Procedure Mapping

I recently was asked a question about the Entity Framework’s (EF) “Stored Procedure Mapping” context menu from the designer. There were actually two issues wrapped into one question. The first part of the question was around the ability to replace stored procedures (SP). The second part was updating a specific table from different SP’s. While it is possible to change the SP for the insert, update and delete methods via the context menu, there was some difficulty finding the select method to use a SP. Rightfully so as unfortunately there is no option to map a “Select” stored procedure to an entity nor is there an option to change an “Update” SP dynamically based on a user’s input. That said, the following is some sample code I put together that shows exactly how to import a “Select” stored procedure to use in a solution/project. While I won’t touch on the differences between lazy and eager loading it is something to be aware of.

I created two different stored procedures (UpdateCustomer[Full|Email]) for updating a customer’s information (either all their information or just their email address) as well as a "GetCustomers" SP to use for retrieving data (the "Select"). All were linked as imported functions. Based upon a checkbox in my example (or some condition that is set) will determine which "Update" SP will be used to update the “Customers” table. Note: Any transactional type of SP (i.e. insert, update, delete) that does not return a value cannot be paired with an entity.

 

//-----------------------------------------------------------------------------

//

// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF

// ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO

// THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

// PARTICULAR PURPOSE.

//

// Copyright (c) Microsoft Corporation. All rights reserved.

//

//

//-----------------------------------------------------------------------------

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

namespace SampleWindowsFormsApplication

{

    public partial class frmMain : Form

    {

        public frmMain()

      {

            InitializeComponent();

        }

        private void frmMain_Load(object sender, EventArgs e)

        {

            using (TailspinToysEntities TailSpin = new TailspinToysEntities())

            {

                var customers = from cs in TailSpin.GetCustomers() select cs;

                foreach (Customer cst in customers)

                {

                    rtbCustomersID.AppendText(cst.UserName);

                }

            }

        }

        private void btnSaveCustomer_Click(object sender, EventArgs e)

        {

            using (TailspinToysEntities TailSpin = new TailspinToysEntities())

            {

                if (chkEmail.Checked)

                    TailSpin.UpdateCustomerEmail(txtUserName.Text, txtEmail.Text);

    else

                    TailSpin.UpdateCustomerFull(txtUserName.Text, txtEmail.Text, txtFirst.Text, txtLast.Text);

            }

        }

    }

}

Summary

While this is a simple example it can be expanded further which can provide a mechanism to dynamically switch between SP’s based upon some input.