Entity Framework and Stored Procedures

Entity Framework also allows you to work with Stored Procedures.

I have created a stored procedure for extracting the Contact information based on GroupId.

create procedure spGetContactsByGroup (@pGroupId int)

as

select * from Contacts where GroupId = @pGroupId
go

spGetContactsByGroup 2

While running the Entity Data Model Wizard I am selecting the Stored Procedure spGetContactsByName.






















But when you look at ContactManagementModel in the Model Browser you won't see any indication of stored procedure.  For that you have to explicitly create a complex type.



















Navigate to the ContactManagementModel.Store and then to Stored Procedures section there you will find spGetContactByGroup stored procedure. Right click the stored procedure and select Add Function Import.

























In the Add Function Import dialog select Select Complex type from Return a Collection of section. Click on Get Column Information Button and then Create Complex Type Button.



Now you can see that the stored procedure is appearing as a Complex Type within the ContactManagementModel.


Now you can use the stored procedure in code.

using ContactManagementModel;public partial class _Default : System.Web.UI.Page
{ ContactManagementEntities context = new ContactManagementEntities();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ddlGroups.DataSource = context.ContactGroups.ToList();
ddlGroups.DataTextField = "GroupName";
    ddlGroups.DataValueField = "GroupId";
DataBind();
}
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{ int intGroupId = Int32.Parse(ddlGroups.SelectedValue.ToString());

var Contact = from p in context.spGetContactsByGroup(intGroupId)
select new { p.ContactName, p.Email };
GridView1.DataSource = Contact.ToList();
DataBind();
}



Top