Code First Stored Procedures with Multiple Results

Posted on August 15, 2012. Filed under: Entity Framework | Tags: , , , |

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.
About these ads

Make a Comment

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

18 Responses to “Code First Stored Procedures with Multiple Results”

RSS Feed for RoMiller.com Comments RSS Feed

Very nice, thank you

[...] some new EF5 features in a 10min session for the Visual Studio launch. Rowan also blogged about calling stored procedures with multiple result sets in Code First and customizing ‘Reverse Engineer Code First’ in the EF Power [...]

[...] some new EF5 features in a 10min session for the Visual Studio launch. Rowan also blogged about calling stored procedures with multiple result sets in Code First and customizing ‘Reverse Engineer Code First’ in the EF Power [...]

Can you give a rough estimate as to when there will be a release to natively support code first stored procedures. I’m in the beginning stages of building a large enterprise product and hoping that I don’t have to hand code stored proc wrappers for long. Thanks for all your hard work on this project!

It will be available in EF6, which will be RTM’d sometime next year. We haven’t started implementing the feature yet but it’s close to the top of the list now. I’d expect us to start working on it in the next couple of months. Once we have it implemented you could try it out using our nightly builds.

Just realized that what asked could be confusing, so to be a bit more specific, I’m looking forward to natively support for stored procedures, but I’m specifically hoping that “Reverse Engineer Code First” will generate the stored proc wrappers for me.

Hi Rowan,
Nice and helpful article. I think it is important to note that each entity (each blog/post) doesn’t get Translated or added to the DbSet.Local collection until the call is made to access one of it’s properties, such as in the foreach loops you have above.
Meaning that if you were to remove those foreach() {Console.WriteLine(…)} at the end of it, you have no entities in the Local collection of the DbSets.
Chris.-

The database surcuttre is similar to Northwind’s Employee and Territories, but I didn’t see how you configure the many-to-many mapping, how does it works , and could you explain more how to declare the many-to-many mapping? thanks.

Hello Rowan, I wonder if the Alpha has already a way to try stored procedures. Thank you for this example.

i tried yout example, it worked, but when i use a procedure with a parameter it dosent. Im doing that:

……

var cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = “[dbseg].[test]“;
cmd.Parameters.Add(new SqlParameter(“@test”, “this is a test”));
try
{
// Run the sproc
db.Database.Connection.Open();

var reader = cmd.ExecuteReader();
……

I am able to successfully do this with parameters on a stored procedure, two things to try:
1. Set the command type to stored procedure by doing
cmd.CommandType = System.Data.CommandType.StoredProcedure;
2. Remove the @ from the parameter name
Try them separately, particularly curious to see if (1.) solves it, because I seem to recall similar behavior when I first tried it.

It worked! But with the @test instead of just test, so it was the number 1 solution that solved it. i’ve tried the 2 solution but no good results alone and with cmd.CommandType = System.Data.CommandType.StoredProcedure, but it didnt worked. Only works if it have the @. thanks for the help.

I need to read many-to-many entities from procedure. Can you help me?

Hi Steve,How can I configure which datbaase to use? And where is the data saved by default?I have installed SQL Server CE 4.0. I added a datbaase called SimpleTest.sdf to the App_Data folder and then added a connectionstring to my Web.config with the same name as the DbContext that was scaffolded:When I run the application, I everything works, but my datbaase isn’t used. That means it’s storing the data somewhere else and I don’t know where or how to change this.I guess I’m just overlooking something very simple here

I have Store SQL :
alter proc [dbo].[BKGetInvoiceNew]
(
@Control int,
@checkno nvarchar(20),
@RVC nvarchar(10),
@FormDay nvarchar(15),
@ToDay nvarchar(15)
)
AS
BEGIN
declare @sql nvarchar(2000)

set @sql =’SELECT CheckNo,TableID,TotalBase,SubTotal,TaxTotal,Discount,STaxTotal,CloseBy,NGuest,CloseTime,PONumber,PaymentMode,PaymentInfo,NChild,TableNo
from SMILE_POS.dbo.Invoice where 1=1′

if @checkno ”
set @sql = @sql + ‘and Checkno =’+@checkno+”

if @RVC ”
set @sql =@sql + ‘ and RVC =’+@RVC+”

if @FormDay ” and @ToDay”
set @sql = @sql+’ Convert(DateTime,Cast(IMonth as varchar) + ”/” + Cast(IDay as varchar) + ”/” + Cast(IYear as varchar),101) between ”’ + cast(@FormDay as varchar) +”’ And ”’ + cast(@ToDay as varchar)+ ”
–PRINT @sql

EXECUTE (@sql)

end

I can not call this store SQL in winform, can you help me call this store? Thanks

Hi,
This solution does not work for me , Its same as http://msdn.microsoft.com/en-us/data/jj691402.aspx, I have tried following things

1) I have downloaded EF 4.1 and installed successfully after that i have added EntityFramework dll inside my proejct
2) After that When I tried to run my code getting following errror .in reading from dataReader;
‘GetDepartmentDivision’ could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly. Near simple identifier, line 1, column 1.

My Code is like this …
using (var objPractice = new PracticeEntities())
{
System.Data.Common.DbDataReader sqlReader;
var cmd = objPractice.Connection.CreateCommand();
cmd.CommandText = “GetDepartmentDivision”;
if (cmd.Connection.State == ConnectionState.Closed)
cmd.Connection.Open();

sqlReader = cmd.ExecuteReader();

var depObj = ((IObjectContextAdapter)objPractice).ObjectContext.Translate(reader, “Department”, MergeOption.AppendOnly);
reader.NextResult();
var divObj = ((IObjectContextAdapter)objPractice).ObjectContext.Translate(reader, “Division”, MergeOption.AppendOnly);

I do not know what’s i am missing ? because some peoples are saying that your code is working but i have done practical example it’s not working …

I have a entity model as below:

public Entity
{
//Properties
}

public Activity : Entity
{
//Properties
public Action Action { get; set; }
public ICollection Commands { get; set; }
}

public Action : Entity
{
//Properties
}

public Command : Entity
{
//Properties
}
I have a store procedure that returns List of Activities along with Related Actions and Commands of each Activity. How I can map the result sets of the before-mentioned store procedure in EF5 or EF 6 Codefirst ? I already used ObjectContext.Translate method of which is explained in MSDN article Stored Procedures with Multiple Result Sets.

But the problem is all my entities are derived from Entity class and that’s why when I used ObjectContext.Translate for Activity .. I can’t use it for Action and Command since It maps the EntitySetName of the Entity class for Activityso if I use it for either Action or Command it’ll raise error. How I can manage that?

Hi
This method is Great, but do you use in return multiple result
just lke select blogs left join tabl1 .. left join table2…
I use this method to all my Stored Procedures , but in the first time wo call this method is aways used Over time 1000ms。 do you have some Solve Method, Thanks


Where's The Comment Form?

    About

    Rowan works as a Program Manager for the ADO.NET Entity Framework team at Microsoft. He speaks at technical conferences and blogs at romiller.com. Rowan lives in Seattle, Washington with his wife Athalie. Prior to moving to the US he resided in the small state of Tasmania in Australia. Outside of technology Rowan's passions include snowboarding, mountain biking, horse riding, rock climbing and pretty much anything else that involves being active. The primary focus of his life, however, is to follow Jesus.

    RSS

    Subscribe Via RSS

    • Subscribe with Bloglines
    • Add your feed to Newsburst from CNET News.com
    • Subscribe in Google Reader
    • Add to My Yahoo!
    • Subscribe in NewsGator Online
    • The latest comments to all posts in RSS

    Meta

Liked it here?
Why not try sites on the blogroll...

Follow

Get every new post delivered to your Inbox.

Join 162 other followers

%d bloggers like this: