One of the most common request we get for Code First is to natively support stored procedures (sprocs). That item is right at the top of our backlog now – but it didn’t make it into EF5.

While using sprocs for insert, update and delete really isn’t possible with Code First yet, you can use DbSet.SqlQuery to fetch data using sprocs with your Code First model.

But what if you sproc has multiple result sets? I hadn’t tried this before but it turns out it’s actually really easy.

 

Assume we have the following sproc:

CREATE PROCEDURE [dbo].[GetAllBlogsAndPosts]
AS
    SELECT * FROM dbo.Blogs
    SELECT * FROM dbo.Posts

 

 

The ObjectContext API has a Translate method that can be used to copy results from a DbDataReader into entities from our model. Fortunately we can drop down to the more complex ObjectContext API from our DbContext. If we provide Translate with an EntitySet name then it will add the entities to our context and track changes to them – the same as if they were retrieved using a LINQ query or the Find method.

Note: For Code First models the EntitySet name is the same as the name of the DbSet property on your derived context.

I could explain further… but a code listing is worth a thousand words:

 

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Objects;

namespace Sproc.Demo
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new BloggingContext())
            {
                // Make sure Code First has built the model before we open the connection
                db.Database.Initialize(force: false);

                // Create a SQL command to execute the sproc
                var cmd = db.Database.Connection.CreateCommand();
                cmd.CommandText = "[dbo].[GetAllBlogsAndPosts]";

                try
                {
                    // Run the sproc
                    db.Database.Connection.Open();
                    var reader = cmd.ExecuteReader();

                    // Read Blogs from the first result set
                    var blogs = ((IObjectContextAdapter)db)
                        .ObjectContext
                        .Translate<Blog>(reader, "Blogs", MergeOption.AppendOnly);

                    foreach (var item in blogs)
                    {
                        Console.WriteLine(item.Name);
                    }

                    // Move to second result set and read Posts
                    reader.NextResult();
                    var posts = ((IObjectContextAdapter)db)
                        .ObjectContext
                        .Translate<Post>(reader, "Posts", MergeOption.AppendOnly);

                    foreach (var item in posts)
                    {
                        Console.WriteLine(item.Title);
                    }
                }
                finally
                {
                    db.Database.Connection.Close();
                }
            }
        }
    }

    public class Blog
    {
        public int BlogId { get; set; }
        public string Name { get; set; }

        public virtual List<Post> Posts { get; set; }
    }

    public class Post
    {
        public int PostId { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }

        public int BlogId { get; set; }
        public virtual Blog Blog { get; set; }
    }

    public class BloggingContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<Post> Posts { get; set; }
    }

}

 

Summary

I don’t have anything else to say about stored procedures… but there are two things I love that this post reminded me of.

1. Every time I have to use the ObjectContext API I am so glad we built the DbContext API. Do you know what an EntitySet is or what MergeOption does… probably not… and you shouldn’t need to just to copy results out of a DbDataReader.I am glad that we built DbContext as a façade on ObjectContext though… so those obscure APIs are there when needed.
2. Code First is awesome for blog posts (any many other things too)… you literally have everything you need to run my code sample by copying that code listing into Program.cs in your own console application. I could give you ‘a download of the completed solution’… but it just has Program.cs with that code in it – nothing else.