[Entity Framework] Stored Procedures – Queries with a Custom Type

In my last post, I have shown you can use stored procedures in EF pretty easily. But you might be already wondered it looks something is missing. That’s right. In many cases, SPs do not return just scalar values or the exactly same set of data as the existing tables.

When you need to return the customized set of data, you can use a View or a Stored Procedure. Using View is more straight forward but knowing how to use the SP in EF is also important.  

 

1. Creating Stored Procedures

Let’s create the following Stored Procedure. (Suppose you have the “Contact” and “Address” tables we have been used in previous EF posts)

CREATE PROCEDURE dbo.GetContactsInCityByCityName
@cityName nchar(50)
AS
SELECT DISTINCT Contact.ContactID As ID, Contact.LastName + ', ' + Contact.FirstName As FullName, Address.City As City
  FROM Contact
  INNER JOIN Address ON Contact.ContactID = Address.ContactID
  WHERE Address.City = @cityName;

 

2. Adding Stored Procedures into the EDM

Use the EDM(Entity Data Model) Designer Wizard to add a new stored procedure to the EDM.

 

3. Importing Stored Procedures

In the “Add Function Import” wizard, select the stored procedure, enter the name, and select a return type as a complex type. The problem is that the EDM does not have any types for a set of return value. So you need to create one.

In VS 2010, the wizard lets you create complex types. This is a huge improvement compared with the previous version, in which you need to create complex types manually.

  • Click the “Get Column Information” button and the columns in a return set will be displayed
  • Click the “Create New Complex Type” button
  • Select the newly created type from the “Complex” dropdown list

Import Wizard 3

 

4. Using Stored Procedure – “GetContactsInCityByCityName”

using (var context = new ContactDBEntities())
{
  var contacts = context.GetContactsInCityByCityName("London");

  foreach (GetContactsInCityByCityName_Result c in contacts)
    Console.WriteLine("{0}: {1}", c.City, c.FullName);
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s