<

Entity Framework and Store Procedures that return multiple resultsets

Published on
6,859 Points
3,159 Views
2 Endorsements
Last Modified:
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!

The problem

Entity Framework (EF) allows us to import Stored Procedures (SPs) since its first version, but there are still some missing features.

The specific problem here is when you have a SP that returns more than one resultset; for instance, returning two (or more) SELECT statements.

Take the following SP as an example:
CREATE PROCEDURE GetAllData
AS
BEGIN
    SET NOCOUNT ON;

    SELECT ID, Name, Age FROM dbo.Clients;
    SELECT ID, Name, Quantity FROM dbo.Products;
END

Open in new window

The result of this query is two tables:

ID          Name               Age
----------- ------------------ ----------
1           Client 1           20
2           Client 2           22
3           Client 3           34
4           Client 4           12
5           Client 5           55



ID          Name               Quantity
----------- ------------------ -----------
1           Product 1          12.33
2           Product 2          1.00
3           Product 3          45.70
4           Product 4          100.56
5           Product 5          87.00

Open in new window

By default, if you try to import this SP into Entity Framework 6 (EF6), only the first query will be considered. There are two ways of solving this problem; let's have a look.


Pre-Conditions

As said above, EF will fail to properly handle your SP so you can leave it out of the Entity Model. Both solutions will rely on the following Plain Old CLR Objects (POCOs):

public class AllData
{
    public List<Client> AllClients { get; set; }
    public List<Product> AllProducts { get; set; }
}

public class Client
{
    public int ID { get; set; }
    public String Name { get; set; }
    public int Age { get; set; }
}

public class Product
{
    public int ID { get; set; }
    public String Name { get; set; }
    public decimal Quantity { get; set; }
}

Open in new window

 

Solution 1: The EF6 "official" way

As in this example on MSDN, we can do something like:

public AllData GetAllData()
{
    AllData result = new AllData()
    {
        AllClients = new List<Model.Client>(),
        AllProducts = new List<Model.Product>()
    };

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

        try
        {
            db.Database.Connection.Open();

            // Run the sproc  
            var reader = cmd.ExecuteReader();
            // Read Blogs from the first result set 
            var customers = ((IObjectContextAdapter)db)
                .ObjectContext
                .Translate<Client>(reader, "Clients", MergeOption.AppendOnly);


            foreach (var item in customers)
            {
                result.AllClients.Add(
                    new Model.Client()
                    {
                        ID = item.ID,
                        Name = item.Name,
                        Age = item.Age
                    });
            }

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


            foreach (var item in products)
            {
                result.AllProducts.Add(
                    new Model.Product()
                    {
                        ID = item.ID,
                        Name = item.Name,
                        Quantity = item.Quantity
                    });
            }
        }
        finally
        {
            db.Database.Connection.Close();
        }
    }

    return result;
}

Open in new window

 

Solution 2: Do it yourself

Personally, I prefer to go back to the ADO.net basics and do the hydration (the act of filling and object with data) of my own POCO. The main reason is often linked to the fact that, most of the time, these POCOs are meant to be sent by a WCF SOAP service; doing it with the EF entities will give you some serialization problems, to say the least (more about this on a future article).
Other good reasons can be:
  • Your application model is different from your database model
  • The result of the SP is not representable by any EF entity
  • You simply want to avoid useless intermediate objects hydration 
Here's my modified way, handling the DataReader and the objects hydration manually:
public AllData GetAllData()
{
    AllData result = new AllData()
    {
        AllClients = new List<Model.Client>(),
        AllProducts = new List<Model.Product>()
    };

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

        try
        {
            db.Database.Connection.Open();
                    
            // Run the sproc  
            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                result.AllClients.Add(
                    new Model.Client() {
                        ID = reader.GetInt32(0),
                        Name = reader.GetString(1),
                        Age = reader.GetInt32(2)
                    });
            }

            reader.NextResult();
            while (reader.Read())
            {
                result.AllProducts.Add(
                    new Model.Product()
                    {
                        ID = reader.GetInt32(0),
                        Name = reader.GetString(1),
                        Quantity = reader.GetDecimal(2)
                    });
            }

        }
        finally
        {
            db.Database.Connection.Close();
        }
    }

    return result;
}

Open in new window



Think as if there was no box

This is one of my rules; don't limit yourself by thinking outside of the box, think as if there was no box at all.

Although you might be using EF as the interface with your database, that doesn't mean that you must be stuck with what it supports. Most of the time, picking a more "old school" approach will prove to be much more efficient.
2
Comment
0 Comments

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Join & Write a Comment

Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month