MVC EF database repository pattern

Question

I have read all sorts of posts on this subject but couldn't find the answer to my question.

The general consensus is that I should be creating a context for 1 unit of work (say 1 web page). I have enclosed every method in my Database.cs (see below) with 'using' hence to me - that implies that each time a method from this class is called - a context is created. So if I was to call 2 Methods from Database.cs from the same Action in the HomeController.cs - would that mean that 2 contexts are created?

Would it not be better to declare a private field inside Database.cs like so:

private Entities db = new Entities()

And have each method within the Database.cs class access it? Which approach is the best?

My current implementation (I'm only going to include the method Verify but there are many methods in the Database class):

HomeController.cs

    [AllowAnonymous]
    public class HomeController : Controller
    {
        private IDatabase Database;

        public HomeController()
        {
            this.Database = new Database();
        }

        [HttpGet]
        public ActionResult Verify(string id)
        {
            if (Database.VerifyUser(id))
            {
                return View();
            }
            else
            {
                ViewBag.Error = "There was an error with the verification process";
                return View();
            }
        }
    }

Database.cs

public class Database : IDatabase
{
... some other methods ...
    public bool VerifyUser(string verificationHash)
    {
        using (Entities db = new Entities())
        {
            var userToVerify = db.VerifyUser(verificationHash);
            int count = userToVerify.Count();
            if (count == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}

db.VerifyUser(..) - this is a call to a stored procedure

Answer 1

Yes in your design DbContext created and disposed in every method calls.

Actually, it is not a good solution to put all database operations to a class and create DbContext over and over again. You probably have a problem in future with that class. It might have hundred methods in time and so it is hard to maintain and all entities are not related with each other semantically so it may cause confusion. I think it is a better solution to seperate entity types into classes. For example, you have an users, projects, departments. If we apply my solution to these entities then the uml class diagram will be like this.

All repositories takes a reference to DbContext. It is called Dependency Injection. It means that dbcontext is instantiated once and passes its reference through necessary repositories so there are no context re-creation. Also there is a generic repository which you can put standard procedures.

So you can use repositories like this.

[HttpGet]
public ActionResult Verify(string id){

   using(var context = new DbContext())
   {
      var userRepo = new UserRepository(context); 
      //Department repository can be used over the same context.
      var departmentRepo = new DepartmentRepository(context);
      if(userRepo.verifyUser(id)){
         return View();
      }
   }

}

Answer 2

Yes that means there are two instances of DbContext. The better is to have one instance of DbContext in your Database class and use this instance in all your methods.

public class Database : IDatabase, IDisposeable
{
    private Entities db;
    public Database()
    {
        db = new Entities()
    }

... some other methods ...
    public bool VerifyUser(string verificationHash)
    {
        var userToVerify = db.VerifyUser(verificationHash);
        int count = userToVerify.Count();
        if (count == 1)
        {
            return true;
        }
        else
        {
            return false;
        }        
    }

    public void Dispose()
    {
        db.Dispose()
    }
}

Then when you finish from Database instance you dispose it and it will dispose the DbContext

public class HomeController : Controller
{
    private IDatabase Database;

    public HomeController()
    {
        this.Database = new Database();
    }

    [HttpGet]
    public ActionResult Verify(string id)
    {
        using(this.Database)
        {
            if (Database.VerifyUser(id))
            {
                return View();
            }
            else
            {
                ViewBag.Error = "There was an error with the verification process";
                return View();
            }
        }
    }   
}

BTW: you may prefer to dispose your resources at the controller level. In that case, you don't need to add using statement in your actions e.g. add this to your controller:

protected override void Dispose(bool disposing)
{
    this.Database.Dispose();
    base.Dispose(disposing);
}