<

Entity Framework and Store Procedures that return multiple resultsets

Published on
5,248 Points
1,548 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
0 Comments

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Join & Write a Comment

This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month