Using singleton pattern with Entity Framework context - The underlying provider failed on open

Question

I'm trying to add a singleton pattern to my DbContext with Entity Framework. I've always used the singleton pattern for this, and never experienced this error before. I know that singleton is best practice (Apparently not), but if any of you have the time to spare, could you please explain why singleton is best practice?

Problem

Other than that, I get this error:

The underlying provider failed on open

Let's have a look at my code

DAO.cs

public class DAO
{
    private static HourRegistrationEntities hourRegInstance;

    public static HourRegistrationEntities HourRegInstance { get { return hourRegInstance = hourRegInstance ?? new HourRegistrationEntities(); } }
}

Service.cs (example method)

/// <summary>
/// Return a list of all denied Hour Registration for the Login with the given stringId
/// </summary>
/// <param name="stringId"></param>
/// <returns>A list of HourRegistrationDTO</returns>
public List<HourRegistrationDTO> GetAllDeniedHoursForLogin(string stringId)
{
    var id = Int32.Parse(stringId);
    using (var db = DAO.HourRegInstance)
    {
        var dbHours = db.HourRegistration.Where(x => x.LoginProject.Login.ID == id && x.Denied == true).ToList();
        var returnList = new List<HourRegistrationDTO>();
        foreach (var item in dbHours)
        {
            returnList.Add(new HourRegistrationDTO()
            {
                Id = item.ID,
                Hours = item.Hours,
                Date = item.Date.ToShortDateString(),
                Comment = item.Comment,
                CommentDeny = item.CommentDeny,
                LoginProject = new LoginProjectDTO()
                {
                    Project = new ProjectDTO()
                    {
                        Title = item.LoginProject.Project.Title
                    }
                }
            });
        }
         return returnList;
    }            
}

As mentioned, I've ALWAYS used the singleton pattern but NEVER had this error before. What's causing this, and why?

UPDATE:

I basically do like this (code below) instead, as that solves the problem. Now I'm more curious about what's causing the error.

Service.cs

using (var db = new HourRegistrationEntities())

Solution 1

The reason it doesn't work is that your using clause is disposing your singleton instance after first use. After that, it becomes useless, disposed but still not null.

The fact that you insist you always used singletons and it always worked doesn't really mean anything. Using singletons for data contexts is considered a terribly bad habit that leads to numerous issues, including memory, concurrency and transaction problems.

My guess is that before you always worked on single threaded desktop apps where a singleton is still risky but doesn't lead to immediate issues. Here, however, in a concurrent world of a web service, it just won't work.

On the other hand, creating a new instance per a wcf call is perfectly valid, different instances do not interfere and you correctly dispose them after used.

Solution 2

Make sure you have this code as your constructor:

public HourRegistrationEntities()
    : base("ConnectionStringName")
{
}

And then define a connection string in App.config/web.config named ConnectionStringName

This will most probably solve your problem. Now about the singleton: you're creating a new instance of HourRegistrationEntities on each call to GetAllDeniedHoursForLogin which is not singleton pattern, it's Unit of Work pattern which is THE best practice for EntityFramework. Keep using it and forget about singleton DbContext, unless you REALLY know what you do. As EF keeps track of entities and their relationship, a long lived singleton DbContext will get slow over time. There will be many other strange problems, like unsaved transactions, side effects and many other hard to debug problems.

Solution 3

May I suggest you to use "lock" functionality to access your singleton object :

public sealed class XModelInstance
{
    private static XDBEntities edmx = null;
    private static readonly object padlock = new object();

    private XModelInstance() { }

    public static XDBEntities Edmx
    {
        get
        {
            lock (padlock)
            {
                if (edmx == null)
                {
                    edmx = new XDBEntities();
                }
                return edmx;
            }
        }
    }
}

This will avoid concurrent access to your context. And of course do not use "using" clause any more to access your context :)

Call example:

var dbHours = XModelInstance.Edmx.HourRegistration.Where(...);

Comments

[Wiktor Zychla]

He isn't creating a new instance on each call, take a closer look again. Basically it means that he has a singleton and all these bad side effects you mention.

[Detilium]

Of course I have the constructor as you mention, I just didn't want to post the code from the model, as EF creates this automatically. Regarding the Unit of Work, could you provide a link of some sort explaining exactly what this is as I've never heard of it before? (Computer Science student learning from bad teachers apparently)

[Detilium]

Well, I've heard that you need to learn something new everyday. Thanks for the great input. :) (I'm a Computer Science student)

[Wiktor Zychla]

And I am a Compute Science assistant professor, regards ;)

[Alireza]

@WiktorZychla Yes, my mistake. Thanks.

[Alireza]

@Detilium link is a good start, just make sure you don't fall for the implementations that add another layer of UnitOfWork over EntityFramework. The legacy data access techs, like pure Ado.Net, would benefit from being wrapped in Repository and Unit of Work layers, some people still use the same implementations they did, now over EF. EF IS the repository and IS Unit of Work, so just using (var db = new MyDbContext()) is good to go.