THE UNDERLYING PROVIDER FAILED ON AN OPEN WHILE USING STORED PROCEDURE IN CODE FIRST

Total Post:68

Points:476
 3419  View(s)
Ratings:
Rate this:

The underlying provider failed on an open while using stored procedure in code first

Hi,

I am trying to retrieve data from stored procedure in entity framework code first model. But while I try it gives me an error “The underlying provider failed on an open” for the inner list of data.

   using (ProductDbContext dbUserContext = new ProductDbContext())
       {
          listProducts = dbUserContext.Database.SqlQuery<Products>("SP_GetAllProducts");
         dbUserContext.Database.Initialize(force: false);
 
         var dbProdCmd = dbUserContext.Database.Connection.CreateCommand();
                dbProdCmd.Connection = dbUserContext.Database.Connection;
 
                 //((IObjectContextAdapter)dbUserContext).ObjectContext.Connection.Open();
               
                dbProdCmd.CommandText = "SP_GetAllProducts";
                dbProdCmd.CommandType = CommandType.StoredProcedure;
                try
                {
                    // dbUserContext.Database.Connection.Open();
                    ((IObjectContextAdapter)dbUserContext).ObjectContext.Connection.Open();
 
                    dbUserContext.Database.SqlQuery<Products>("SP_GetAllProducts");
 
                    var prodReader = dbProdCmd.ExecuteReader();
 
                    listProducts = ((IObjectContextAdapter)dbUserContext)
                   .ObjectContext
                   .Translate<Products>(prodReader, "Products", MergeOption.AppendOnly).ToList();
                    prodReader.Dispose();
                }
                finally
                {
                    //dbUserContext.Database.Connection.Close();
                    //((SqlConnection)dbUserContext.Database.Connection).Close();
 
                    ((IObjectContextAdapter)dbUserContext).ObjectContext.Connection.Close();
                }
                //((IObjectContextAdapter)dbUserContext).ObjectContext.ContextOptions.LazyLoadingEnabled = false;
                return View(listProducts);
            }

Stored Procedure:

CREATE PROCEDURE [dbo].[SP_GetAllProducts]
      -- Add the parameters for the stored procedure here
     
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT OFF;
 
    -- Insert statements for procedure here
      SELECT * FROM [DBO].[Products]
      SELECT * FROM [DBO].[prd1]
      SELECT * FROM [DBO].[prd2]
      SELECT * FROM [DBO].[prd3]
      SELECT * FROM [DBO].[prd4]
      SELECT * FROM [DBO].[prd5]
      SELECT * FROM [DBO].[prd6]
END

Model: Model structure is something like this:

using System.Collections.Generic;
using System;
using System.Data.Entity;
 
namespace Project.Models
{
    public class Products
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public DateTime CreationDate { get; set; }
        public DateTime ModificationDate { get; set; }
 
        public virtual ICollection<T> prd1 { get; set; }
        public virtual ICollection<T> prd2 { get; set; }
        public virtual ICollection<T> prd3 { get; set; }
        public virtual ICollection<T> prd4 { get; set; }
        public virtual ICollection<T> prd5 { get; set; }
    }
 
    public class ProductContext : DbContext
    {
        public DbSet<T> prd { get; set; }
        public DbSet<T> prd1 { get; set; }
 
        public DbSet<T> prd2 { get; set; }
        public DbSet<T> prd3 { get; set; }
        public DbSet<T> prd4 { get; set; }
        public DbSet<T> prd5 { get; set; }
}

Please reply ASAP.

Thanks.

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!