<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Entity Framework and Store Procedures that return multiple resultsets

Published on
8,000 Points
4,300 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
1 Comment

Expert Comment

by:Budheshwar Tiwari
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
0

Featured Post

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month