none
How to call store procedure which return multiple result set with EF code first RRS feed

  • Question

  • just show me with sample code that how can i call store procedure which return multiple result set with EF code first.

    thanks

    Thursday, September 15, 2016 3:13 PM

Answers

  • Here you go.  And I included how to fix-up navigation properties between the separate results, as this is often what multiple resultsets is used for.

    using System;
    using System.Collections.Generic;
    using System.Data.Entity;
    using System.Data.Entity.Core.Objects;
    using System.Data.Entity.Infrastructure;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ConsoleApplication25
    {
        public class Customer
        {
            public int Id { get; set; }
            public string Name { get; set; }
    
            public virtual ICollection<Order> Orders { get; } = new HashSet<Order>();
        }
        public class Order
        {
            public int Id { get; set; }
    
            public int CustomerID { get; set; }
            virtual public Customer Customer { get; set; }
            public string Product { get; set; }
            public int Quantity { get; set; }
        }
        public class MyDb : DbContext
        {
            public DbSet<Customer> Customers { get; set; }
            public DbSet<Order> Orders { get; set; }
    
            public  List<Customer> GetCustomerAndOrders( int customerId)
            {
    
                if (this.Database.Connection.State != System.Data.ConnectionState.Open)
                    this.Database.Connection.Open();
    
                var cmd = this.Database.Connection.CreateCommand();
                cmd.CommandText = "GetCustomerAndOrders";
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                var pCustomerId = cmd.CreateParameter();
                pCustomerId.ParameterName = "@customerID";
                pCustomerId.DbType = System.Data.DbType.Int32;
                pCustomerId.Value = customerId;
                cmd.Parameters.Add(pCustomerId);
    
                using (var rdr = cmd.ExecuteReader())
                {
                    var oc = ((IObjectContextAdapter)this).ObjectContext;
    
                    //read the customers and add them to the cache to enable fixup of navigation properties
                    var customers = oc.Translate<Customer>(rdr, "Customers", MergeOption.OverwriteChanges).ToDictionary(c => c.Id);
                    rdr.NextResult();
                    //read the customers and add them to the cache to enable fixup of navigation properties
                    var orders = oc.Translate<Order>(rdr, "Orders", MergeOption.OverwriteChanges).ToList();
    
                    return customers.Values.ToList();
    
                }
            }
    
    
        }
    
        class Program
        {
            public static object IDbContextAdapter { get; private set; }
    
            static void Main(string[] args)
            {
                Database.SetInitializer(new DropCreateDatabaseAlways<MyDb>());
                using (var db = new MyDb())
                {
                    //disable lazy loading to prevent re-querying the database on navigation to the Orders collection
                    db.Configuration.LazyLoadingEnabled = false;
    
                    db.Database.ExecuteSqlCommand(@"
    create procedure GetCustomerAndOrders @customerID int
    as
    begin
      select * from Customers where id = @customerID;
      select * from Orders where CustomerID = @customerID;
    end
    ");
    
                    var cust = db.Customers.Create();
                    cust.Name = "Customer1";
    
                    for (int i = 0; i < 10; i++)
                    {
                        var order = db.Orders.Create();
                        order.Customer = cust;
                        order.Product = "SomeProduct";
                        order.Quantity = i;
                        db.Orders.Add(order);
                    }
    
                    db.Customers.Add(cust);
                    db.SaveChanges();
    
                    int customerId = cust.Id;
    
                    var customerFromProc = db.GetCustomerAndOrders(customerId);
    
                    var orders = customerFromProc.First().Orders.ToList();
    
                }
            }
    
           
        }
    }
    
    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Sudip_inn Monday, September 26, 2016 11:19 AM
    Thursday, September 15, 2016 8:59 PM

All replies

  • just show me with sample code that how can i call store procedure which return multiple result set with EF code first.

    thanks

    You would most likely have to use the EF backdoor, using straight-up ADO.NET, SQL Command Objects that called the sproc and a Datareader with the ADO.NET Datareader being able to navigate and read multiple result sets, like in the past and do it that way.

    https://blogs.msdn.microsoft.com/alexj/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database/

    Thursday, September 15, 2016 3:30 PM
  • Yep. Here's a little helper method for your DbContext:
            IEnumerable<T> ReadFromDataReader<T>( DbDataReader rdr )
            {
                var oc = ((IObjectContextAdapter)this).ObjectContext;
                var result = oc.Translate<T>(rdr);
                return result;
            }
    David

    David http://blogs.msdn.com/b/dbrowne/

    Thursday, September 15, 2016 3:52 PM
  • @David would u please post a complete sample code instead of partial. just show me how to call store proc which takes multiple params and return multiple result which i like to capture by EF code first.
    Thursday, September 15, 2016 8:11 PM
  • Here you go.  And I included how to fix-up navigation properties between the separate results, as this is often what multiple resultsets is used for.

    using System;
    using System.Collections.Generic;
    using System.Data.Entity;
    using System.Data.Entity.Core.Objects;
    using System.Data.Entity.Infrastructure;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ConsoleApplication25
    {
        public class Customer
        {
            public int Id { get; set; }
            public string Name { get; set; }
    
            public virtual ICollection<Order> Orders { get; } = new HashSet<Order>();
        }
        public class Order
        {
            public int Id { get; set; }
    
            public int CustomerID { get; set; }
            virtual public Customer Customer { get; set; }
            public string Product { get; set; }
            public int Quantity { get; set; }
        }
        public class MyDb : DbContext
        {
            public DbSet<Customer> Customers { get; set; }
            public DbSet<Order> Orders { get; set; }
    
            public  List<Customer> GetCustomerAndOrders( int customerId)
            {
    
                if (this.Database.Connection.State != System.Data.ConnectionState.Open)
                    this.Database.Connection.Open();
    
                var cmd = this.Database.Connection.CreateCommand();
                cmd.CommandText = "GetCustomerAndOrders";
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                var pCustomerId = cmd.CreateParameter();
                pCustomerId.ParameterName = "@customerID";
                pCustomerId.DbType = System.Data.DbType.Int32;
                pCustomerId.Value = customerId;
                cmd.Parameters.Add(pCustomerId);
    
                using (var rdr = cmd.ExecuteReader())
                {
                    var oc = ((IObjectContextAdapter)this).ObjectContext;
    
                    //read the customers and add them to the cache to enable fixup of navigation properties
                    var customers = oc.Translate<Customer>(rdr, "Customers", MergeOption.OverwriteChanges).ToDictionary(c => c.Id);
                    rdr.NextResult();
                    //read the customers and add them to the cache to enable fixup of navigation properties
                    var orders = oc.Translate<Order>(rdr, "Orders", MergeOption.OverwriteChanges).ToList();
    
                    return customers.Values.ToList();
    
                }
            }
    
    
        }
    
        class Program
        {
            public static object IDbContextAdapter { get; private set; }
    
            static void Main(string[] args)
            {
                Database.SetInitializer(new DropCreateDatabaseAlways<MyDb>());
                using (var db = new MyDb())
                {
                    //disable lazy loading to prevent re-querying the database on navigation to the Orders collection
                    db.Configuration.LazyLoadingEnabled = false;
    
                    db.Database.ExecuteSqlCommand(@"
    create procedure GetCustomerAndOrders @customerID int
    as
    begin
      select * from Customers where id = @customerID;
      select * from Orders where CustomerID = @customerID;
    end
    ");
    
                    var cust = db.Customers.Create();
                    cust.Name = "Customer1";
    
                    for (int i = 0; i < 10; i++)
                    {
                        var order = db.Orders.Create();
                        order.Customer = cust;
                        order.Product = "SomeProduct";
                        order.Quantity = i;
                        db.Orders.Add(order);
                    }
    
                    db.Customers.Add(cust);
                    db.SaveChanges();
    
                    int customerId = cust.Id;
    
                    var customerFromProc = db.GetCustomerAndOrders(customerId);
    
                    var orders = customerFromProc.First().Orders.ToList();
    
                }
            }
    
           
        }
    }
    
    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Sudip_inn Monday, September 26, 2016 11:19 AM
    Thursday, September 15, 2016 8:59 PM
  • Hi Mou_inn,

    You could also use the other two methods to achieve your requirement. like below:

    #Store Procedure.

    CREATE PROCEDURE [dbo].[Sp_AllCustomers]
    	
    AS
    	select * from Customers
    RETURN 0

    #Models

    namespace EFStoreProcedureDemo
    {
        using System.ComponentModel.DataAnnotations;
        using System.ComponentModel.DataAnnotations.Schema;
    
        public partial class Customers
        {
            [DatabaseGenerated(DatabaseGeneratedOption.None)]
            public int Id { get; set; }
    
            [StringLength(50)]
            public string FirstName { get; set; }
    
            [StringLength(50)]
            public string LastName { get; set; }
        }
    }
    

    #Usage1:

    using (var db = new EFDemoContext())
                {
    
                  
                    var query = db.Database.SqlQuery<Customers>("Sp_AllCustomers").ToList();
                  }

    #Usage2:

    using (var db = new EFDemoContext())
                {
    
                 var query = db.Customers.SqlQuery("Sp_AllCustomers").ToList();
    
                             }

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, September 16, 2016 2:03 AM
    Moderator
  • @David what is IObjectContextAdapter ?

    how IObjectContextAdapter is different from object context ?

    var oc = ((IObjectContextAdapter)this).ObjectContext;

    what is this keyword refer here ?

    Friday, September 16, 2016 1:17 PM
  • Hi,

    >>how IObjectContextAdapter is different from object context ?

    Interface implemented by objects that can provide an ObjectContext instance. The DbContext class implements this interface to provide access to the underlying ObjectContext.

    >>what is this keyword refer here ?

    this keyword is related DbContext. as the following code, "this" is "SchoolDBEntities "

    public partial class SchoolDBEntities : DbContext

    For more information, please refer to:

    http://www.entityframeworktutorial.net/EntityFramework4.3/dbcontext-vs-objectcontext.aspx

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, September 26, 2016 9:18 AM
    Moderator
  • i test your code but below one does not work.

    using (var db = new EFDemoContext())
    {
          var query = db.Customers.SqlQuery("Sp_AllCustomers").ToList();
    }

    Monday, September 26, 2016 11:38 AM