---
title: "The underlying provider failed on an open while using stored procedure in code first"  
description: "The underlying provider failed on an open while using stored procedure in code first"  
author: "Anonymous User"  
published: 2013-01-05  
updated: 2013-01-05  
canonical: https://www.mindstick.com/forum/530/the-underlying-provider-failed-on-an-open-while-using-stored-procedure-in-code-first  
category: "asp.net mvc"  
tags: ["asp.net mvc"]  
reading_time: 2 minutes  

---

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

The underlying [provider](https://answers.mindstick.com/qa/92701/who-is-the-best-telecom-solution-provider) [failed](https://www.mindstick.com/forum/160484/error-failed-to-launch-debug-adapter-additional-information-may-be-available-in-the-output-window) on an open while using [stored procedure](https://www.mindstick.com/articles/803/using-stored-procedure-in-asp-dot-net) in code first

Hi,

I [am trying](https://answers.mindstick.com/qa/36834/which-two-programming-languages-should-i-master-in-if-i-am-trying-to-get-into-google-or-facebook) to [retrieve data](https://www.mindstick.com/forum/160347/how-do-you-retrieve-data-from-a-nosql-database-like-mongodb) from stored procedure in [entity framework](https://www.mindstick.com/articles/1566/crud-operations-using-entity-framework-code-first-approach) 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     ASBEGIN      -- 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](https://www.mindstick.com/articles/23258/choose-your-business-structure-wisely) 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.


---

Original Source: https://www.mindstick.com/forum/530/the-underlying-provider-failed-on-an-open-while-using-stored-procedure-in-code-first

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.