Entity Framework and Store Procedures that return multiple resultsets

Alexandre SimõesSoftware Architect
CERTIFIED EXPERT
Published:
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
7,078 Views
Alexandre SimõesSoftware Architect
CERTIFIED EXPERT

Comments (1)

HI Alexandre,

i have use the second tricks of you mention this article but the error is bind the

==>  Invalid attempt to read when no data is present.
can you please help this issue.
**********The Store Procedure result is ******

SQL query Window**********These are Model ************

  public class UserDashboard
    {
        public List<UserMenuItem> DealOfTheDay { get; set; }
        public List<UserMenuItem> UserMenuList { get; set; }
        public List<MenuItemImage> MenuImageGallery { get; set; }
        public int TotalCartCount { get; set; }
    }
    public class UserMenuItem
    {
        public long ItemId { get; set; }
        public string ItemCode { get; set; }
        public string ItemName { get; set; }
        public decimal OutletPrice { get; set; }
        public decimal Calorie { get; set; }
        public string Discription { get; set; }
        public string UnitName { get; set; }
        public string ImageTitle { get; set; }
        public bool isFavorite { get; set; }
    }
    public class MenuItemImage
    {
        public int ImageID { get; set; }
        public long ItemId { get; set; }
        public string ImageTitle { get; set; }
    }
 ************* This is the function of code first multiple select Statement*************
        public UserDashboard GetAllUserDashboardData(long UserId, byte WalletType = 1)
        {
            UserDashboard result = new UserDashboard();
            var command = _Context.Database.Connection.CreateCommand();
            command.CommandText = "[dbo].[APIUserDashBoardData]";
            command.CommandType = CommandType.StoredProcedure;
            var userid = new SqlParameter("@UserId", UserId);
            var to = new SqlParameter("@WalletType", WalletType);
            command.Parameters.Add(userid);
            command.Parameters.Add(to);
            try
            {
                _Context.Database.Connection.Open();
                // Run the sproc  
                var reader = command.ExecuteReader();
                #region First Method to bind the entity by ef
                //// Read Blogs from the first result set
                //var DealOfTheDayList = ((IObjectContextAdapter)_Context)
                //    .ObjectContext
                //    .Translate<UserMenuItem>(reader, "Result1", MergeOption.AppendOnly);


                //foreach (var item in DealOfTheDayList)
                //{
                //    result.DealOfTheDay.Add(
                //        new UserMenuItem()
                //        {
                //            ItemId = item.ItemId,
                //            ItemCode = item.ItemCode,
                //            ItemName = item.ItemName,
                //            OutletPrice = item.OutletPrice,
                //            Calorie = item.Calorie,
                //            Discription = item.Discription,
                //            UnitName = item.UnitName,
                //            ImageTitle = item.ImageTitle,
                //            isFavorite = item.isFavorite,
                //        });
                //}
                //// Move to second result set and read Posts
                //reader.NextResult();
                //var MenuImageGalleryList = ((IObjectContextAdapter)_Context)
                //    .ObjectContext
                //    .Translate<MenuItemImage>(reader, "Result2", MergeOption.AppendOnly);


                //foreach (var item in MenuImageGalleryList)
                //{
                //    result.MenuImageGallery.Add(
                //        new MenuItemImage()
                //        {
                //            ImageID = item.ImageID,
                //            ImageTitle = item.ImageTitle,
                //            ItemId = item.ItemId,

                //        });
                //}

                //// Move to third result set and read Posts
                //reader.NextResult();
                //var UserMenuList = ((IObjectContextAdapter)_Context)
                //    .ObjectContext
                //    .Translate<UserMenuItem>(reader, "Result3", MergeOption.AppendOnly);


                //foreach (var item in UserMenuList)
                //{
                //    result.UserMenuList.Add(
                //        new UserMenuItem()
                //        {
                //            ItemId = item.ItemId,
                //            ItemCode = item.ItemCode,
                //            ItemName = item.ItemName,
                //            OutletPrice = item.OutletPrice,
                //            Calorie = item.Calorie,
                //            Discription = item.Discription,
                //            UnitName = item.UnitName,
                //            ImageTitle = item.ImageTitle,
                //            isFavorite = item.isFavorite,
                //        });
                //}

                #endregion
                #region Second Method to bind the entity by ef
                // Read Blogs from the first result set
                while (reader.Read())
                    {
                        result.DealOfTheDay.Add(
                            new UserMenuItem()
                            {
                                ItemId = reader.GetInt64(0),
                                ItemCode = reader.GetString(1),
                                ItemName = reader.GetString(2),
                                OutletPrice = reader.GetDecimal(3),
                                Calorie = reader.GetDecimal(4),
                                Discription = reader.GetString(5),
                                UnitName = reader.GetString(6),
                                ImageTitle = reader.GetString(7),
                                isFavorite = reader.GetBoolean(8),
                            });
                    }
                    //// Move to Second result set and read Posts
                    reader.NextResult();
                    while (reader.Read())
                    {
                        result.MenuImageGallery.Add(
                            new MenuItemImage()
                            {
                                ImageID = reader.GetInt32(0),
                                ItemId = reader.GetInt64(1),
                                ImageTitle = reader.GetString(2)
                            });
                    }
                    //// Move to third result set and read Posts
                    reader.NextResult();
                    while (reader.Read())
                    {
                        result.UserMenuList.Add(
                            new UserMenuItem()
                            {
                                ItemId = reader.GetInt64(0),
                                ItemCode = reader.GetString(1),
                                ItemName = reader.GetString(2),
                                OutletPrice = reader.GetDecimal(3),
                                Calorie = reader.GetDecimal(4),
                                Discription = reader.GetString(5),
                                UnitName = reader.GetString(6),
                                ImageTitle = reader.GetString(7),
                                isFavorite = reader.GetBoolean(8),
                            });
                    }
                    reader.NextResult();
                    while (reader.Read())
                    {
                        result.TotalCartCount = reader.GetInt32(0);
                    }
                    #endregion
               
            }
            catch (Exception ex)
            {
                ex.ToString();
            }
            finally
            {
                _Context.Database.Connection.Close();
            }
            return result;
        }

*********For more detail see the file attachments********************
Thanks
VS2017.png

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.