Best practices in using BD connections via Entity Framework

Question

When defining the context class in the Entity Framework (example):

public class Context : DbContext
{
    public DbSet<Usuario> Usuarios { get; set; }
    public DbSet<Categoria> Categorias { get; set; }
}

And used in DAO classes or controllers:

private Context db = new Context();

How best to use the reference to Context?
It would be more performative if db were static?
There is relevant literature regarding this issue, related to an environment with several competing users?

Answer 1

DbContext nay is a class thread-safe, then should not be used as static (what I imagine to be like a Singleton). The objects attached in this context are also not thread-safe, what can create conflicts.

This makes the idea of using a context by Controller is a good idea, since destroying the DbContext of each Controller, is called the method Dispose for each DbSet, and consequently for each object connected to each DbSet. This ensures that any pending modifications are properly resolved upon expiry of the object.

Still, if it’s interesting to use a pattern Singleton to the DbContext in its application, it might be necessary to write an additional logic in the classes to verify whether the object is attached to the context or not, lock certain parts of the context (like each DbSet, for example) as critical regions, and so on, but I don’t think that’s a good idea.

Answer 2

Usually the DataContext is instantiated in each object (a CRUD of Users for example, therefore Class Users).
It is not good practice to use it as Singleton or static [ref 1], (sigleton != Static). Moreover singletons should be minimized in an application [ref 2].

public class Usuarios
{
    private Context db = new Context();

    private Usuario getUser(int id){
        return (from x in db.Usuarios
                where x.UsuarioID == id && !x.Excluido
                select x);
    }

    private IEnumerable<Usuario> getUserList(){[..]}

    private bool SaveUser(Usuario info){[..]}
    [..]
}

Some people establish each method:

    private Usuario getUser(int id){
        var db = new Context();
        return (from x in db.Usuarios
                where x.UsuarioID == id && !x.Excluido
                select x);
    }

and there are those who install in each method by using:

    private Usuario getUser(int id){
        Usuario result = null;

        using (var db = new Context()){
            result = (from x in db.Usuarios
                where x.UsuarioID == id && !x.Excluido
                select x);
        }

        return result;
    }

However, as already discussed in the SO-English, in practice it is unnecessary to dislocate the Context variable after using it since Garbage Collector already takes care of it soon after realizing the end of the use of the Context variable. By the way, not only unnecessary as an anti-standard (anti-pattern) overuse.

References:

1
http://www.britishdeveloper.co.uk/2011/03/dont-use-singleton-datacontexts-entity.html
https://stackoverflow.com/questions/4735498
https://stackoverflow.com/questions/4735498

2
https://stackoverflow.com/questions/137975
https://stackoverflow.com/questions/1020312
https://stackoverflow.com/questions/86582/singleton-how-should-it-be-used?rq=1

Answer 3

A long time ago I studied and read a lot about the use of Entityframework and arrived at a programming methodology, where I instate the context in each method, passed, if necessary, the context by reference to other methods I call. I always try to use the instruction using, thus ensuring that after the execution of the instructions that need context it is released from memory and releasing the connection. I don’t know how efficient this way of programming is, but in the applications I already have, I’ve never had problems with performance.

private Usuario getUser(int id)
{
    Usuario usuario = null;
    using (var db = new Context()) 
    {    
       usuario  = (from x in db.Usuarios
            where x.UsuarioID == id && !x.Excluido
            select x).FirstOrDefault();
    }
    //outras instruções que não necessitam de conexão com o banco

    return usuario;
}

Answer 4

If you create a context for each object, as in the first example, you will have problem if the objects are associated, example: Pessoa and Endereco, where Endereco points to Pessoa. So, when you have an instance of Pessoa in a context and an instance of Endereco in another context and need to turn on this Endereco à Pessoa, will give error saying that can not because they are part of contexts different.

As in the second example, it is only possible if you can solve everything in one method. Horrible! Example: I can have a method that searches person by Cpf.

Pessoa.pesquisaCpf(string cpf)

In this method I return a person and need to do a search using a context. Then I need to create a Endereco, using my same example, and I use the Address constructor, which will create another context. It will already give error.

To solve all this I’m still using only one context created in the system login. I am not happy with this, because the changes made in the system by one user are not seen by another user on another machine, until the same rewrites the login. This I’m trying to fix.