locked
I get The underlying provider failed to Open when using EF RRS feed

  • Question

  • User539757411 posted

    This is the scenario:

    In the web forms application using Entity Framework 6 and I have a /admin folder.

    I have a LOGIN page that opens the Entity Framework connection on login:

    protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                bool userAuthenticated = (HttpContext.Current.User != null) && HttpContext.Current.User.Identity.IsAuthenticated;
                if (userAuthenticated)
                {
                    if (!IsPostBack)
                    {
                        // Open connection
                        if (dc.Database.Connection.State == ConnectionState.Closed)
                        {
                            dc.Database.Connection.Open();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Metodos.RegistrarLogErroSistema(ex);
    
                ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Erro", "alert('" + (string.IsNullOrWhiteSpace(ex.Message) ? "Ocorreu um erro no sistema! Favor contactar o administrador." : ex.Message) + "');", true);
            }
        }


    In the admin's Master Page I instantiate the datacontext and I have methods calling the database to check identity, group, permissions, like below:

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Web;
    using System.Web.Script.Serialization;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    
    public partial class Admin : System.Web.UI.MasterPage
    {
        dbDIOEntities dc = new dbDIOEntities();
    
        public int getUserGroup
        {
            get
            {
                return Methods.getCurrentUserGroup().id;
            }
        }


    And in the Method.cs inside the App_Code folder:


        public static grupos getCurrentUserGroup()
        {
            try
            {
                if (System.Web.HttpContext.Current.Session["ObjetoGrupoUsuario"] != null)
                {
                    return (grupos)System.Web.HttpContext.Current.Session["ObjetoGrupoUsuario"];
    
                }
                else if (!string.IsNullOrWhiteSpace(HttpContext.Current.User.Identity.Name))
                {
                    string nomeusuario = HttpContext.Current.User.Identity.Name.ToString();
                    usuarios usuario = dc.usuarios.Where(o => o.username == nomeusuario).FirstOrDefault();
                    return dc.grupos.Where(o => o.id == usuario.grupo_id).FirstOrDefault();
                }
                else if (System.Web.HttpContext.Current.Session["NomeUsuario"] != null)
                {
                    string nomeusuario = System.Web.HttpContext.Current.Session["NomeUsuario"].ToString();
                    usuarios usuario = dc.usuarios.Where(o => o.nome == nomeusuario).FirstOrDefault();
                    return dc.grupos.Where(o => o.id == usuario.grupo_id).FirstOrDefault();
                }
                else
                {
                    int IdOuvidoriaSetorial = (int)Enumeradores.GruposUsuarios.OuvidorSetorial;
                    return dc.grupos.Where(o => o.id == IdOuvidoriaSetorial).FirstOrDefault();
                }
            }
            catch (Exception ex)
            {
                Metodos.RegistrarLogErroSistema(ex);
            }
    
            return null;
        }


    And I have dozens of pages that Instantiate the datacontext and check user´s identity, group, permissions, etc.
    As an example below I have "Search.aspx.cs" , that instantiates a new datacontext and uses it across the code behind page:

    using System;
    using System.Collections.Generic;
    using System.Data.Entity.Validation;
    using System.Linq;
    using System.Text.RegularExpressions;
    using System.Web;
    using System.Web.Script.Serialization;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    
    public partial class Search : System.Web.UI.Page
    {
        dbDIOEntities dc = new dbDIOEntities();



    So users get error "The underlying provider failed to open" frequently and users can't use the web application.
    Is is the proper way to instantiate datacontexts and open Entity connections ?
    How could I change the code to avoid that error ? 



    Monday, September 11, 2017 9:28 AM

Answers

All replies

  • User753101303 posted

    Hi,

    As a developer you should use ex.ToString() rather than ex.Message to get the full story (including inner exceptions as well as the call stack etc...). My personal preference is to show a generic message as most often a user is not supposed to do something about an exception (and if possible to warn the dev team about exceptions with all the details).

    The problem could be that the connection is explictely opened and never closed. ending up in exhausting the connection pool. The preferred approach is to open/dispose a DbContext as needed or at most once per http request. you should never have to explicitly open/close a db connection. It is likely still best to check first what is the exact error you have (in case you would have some other error first for example).

    Monday, September 11, 2017 1:58 PM
  • User539757411 posted

     

    The problem could be that the connection is explictely opened and never closed. ending up in exhausting the connection pool. The preferred approach is to open/dispose a DbContext as needed or at most once per http request. you should never have to explicitly open/close a db connection.

    Yes. After reading across the web I have come to that conclusion.
    I have changed the code to:

    using (dbDIOEntities dc = new dbDIOEntities())
    {
    ...
    }

    So I started getting the following error:

    The ObjectContext instance has been disposed and can no longer be used for operations that require a connection.

    Monday, September 11, 2017 10:28 PM
  • User-1838255255 posted

    Hi xandeq,

    According to your description and error message, I think this issue is related to the dbcontext loading format, here are someone who meet the similar problem as you, please check:

    How to solve the error The ObjectContext instance has been disposed and can no longer be used for operations that require a connection:

    https://stackoverflow.com/questions/18398356/how-to-solve-the-error-the-objectcontext-instance-has-been-disposed-and-can-no-l 

    The ObjectContext instance has been disposed and can no longer be used for operations that require a connection. in Reference table:

    https://stackoverflow.com/questions/22007025/the-objectcontext-instance-has-been-disposed-and-can-no-longer-be-used-for-opera 

    Lazy Loading:

    http://www.entityframeworktutorial.net/EntityFramework4.3/lazy-loading-with-dbcontext.aspx 

    Best Regards,

    Eric Du

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 12, 2017 9:59 AM
  • User1120430333 posted

    You should learn how  to do global exception, a common area where all exceptions are caught and logged to a log file. That was you can get  the inner.excpetion.message if any to get further info about the exception that is hidden.

    https://www.devu.com/cs-asp/lesson-57-understanding-global-exception-handling/

    Thursday, September 14, 2017 1:16 AM