To call and use stored procedures from a database in Entity Framework is pretty easy. Stored procedures can return Scalars, Entities and also Complex Types (properties aggregated) as we will see here.

In this example we add an existing stored procedure called sp_GetArtistNameAndCountryById. We do this by refreshing the model from the database and select the stored procedure to be related to the store model.

After that we open the Model Browser and right click the store procedure name in the store and select ‘Add Function Import’…

In the next dialog we add the stored procedure as a function to the conceptual model. Name the method and specify it to return a complex type. Press the ‘Get Column Information’ button to retrieve the returned properties. Also click the ‘Create New Complex Type’ to return as our new special Artist name-country combination.

If we look at the Model Browser now, we see the Function Import for the GetArtistNameAndCountryById function in the conceptual model.

We write a test to see if it works. We retrieve an Artist with Id = 6 which happens to be the most wonderful Irish singer Luka Bloom. Note that the resultArtist is our complex type (GetArtistNameAndCountryById_Result).

using System.Data.Objects;
using System.Diagnostics;
using System.Linq;
using Entities;
using Microsoft.VisualStudio.TestTools.UnitTesting;
 
namespace MusicEntitiesTests
{
    [TestClass]
    public class ArtistTest
    {
        [TestMethod]
        public void CallingGetArtistNameAndCountryByIdReturnsArtists()
        {
            using (var context = new MusicModelContainer())
            {
                ObjectResult<GetArtistNameAndCountryById_Result> result =
                    context.GetArtistNameAndCountryById(6);
                GetArtistNameAndCountryById_Result resultArtist = result.First();
 
                Debug.WriteLine("{0} from {1}",
                                resultArtist.Name,
                                resultArtist.Country);
 
                // Id 6 is Luka Bloom
                Assert.IsTrue(resultArtist.Name.Equals("Luka Bloom"));
            }
        }
    }
}

And the test has passed ones again :-)