When is it necessary to dispose db connection

Question

I have a class that contains several common methods customized for use in my MVC app, that I use in several places. Here is an example of some:

    private MyEntities db = new MyEntities();


    public List<SelectListItem> GetLocationList()
    {
        var query =
                db.v_LocationsAlphabetical.OrderByDescending(x => x.Category).ThenBy(x => x.LocationName).ToList()
                .Select(x => new SelectListItem
                {
                    Value = x.LocationID.ToString(),
                    Text = x.LocationName
                });

        return (query).ToList();
    }

    public IEnumerable<SelectListItem> GetStates()
    {
        var query = db.States.Select(x => new SelectListItem
        {
            Value = x.Abbr,
            Text = x.Name
        });

        return(query);
    }

    public List<Person> GetPeople()
    {
        var query = db.Person.OrderBy(m => m.LastName).ThenBy(m => m.FirstName).ToList();

        return (query);

    }

Each one of these methods makes a call to the database to get data and I was wondering if I need to add a dispose to each method. If not, why? Thanks.

Completely unrelated but is there a reason why you always return your query between parethesis? — NunoCarmo, Jun 03 '13 at 20:36

score 6 · Answer 1 · answered Jun 03 '13 at 20:39

6

You shouldn't call dispose in each method, because the lifetime of db is the same as that of the enclosing class since it's not a local variable in a method.

The typical way to handle this is to make the current class IDisposable and call db.Dispose() in the Dispose() method.

answered Jun 03 '13 at 20:39

recursive

83,943
34
151
241

2

And this would be usually incorrect. Calling Dispose on a DbContext is usually not needed as there is nothing to dispose. This comes directly from the EF team who wrote the DbConext class. For a full explanation of why http://blog.jongallant.com/2012/10/do-i-have-to-call-dispose-on-dbcontext.html#.Ua0IS5yXT0U – Robert McKee Jun 03 '13 at 21:24
@RobertMcKee: It seems an interesting choice to mark a class as `IDisposable` when it should not be disposed. – recursive Jun 03 '13 at 21:25
It is only required if you are manually manipulating the connection yourself. Otherwise there will be nothing to dispose. Since most people don't manually open and close the connection themselves, the disposal is optional. – Robert McKee Jun 03 '13 at 21:26
Interesting blog post. I never knew that. – recursive Jun 03 '13 at 21:30
Thank you all for your answers. One thing I should point out, I'm pretty sure I am not using dbContext objects, rather I think they are ObjectContext (I am still pretty new to all this). I am using Db First, so I create an edmx file from the existing db then create an instance with: private MyEntities db = new MyEntities();. Does this impact y'alls answers? – BattlFrog Jun 05 '13 at 15:13

Robert McKee · Answer 2 · 2013-06-03T21:22:31.787

0

No. DbContexts don't have to be manually disposed, unless you manually manage the connection yourself. Therefore, disposing them is usually optional.

edited Jun 03 '13 at 21:22

answered Jun 03 '13 at 20:35

Robert McKee

21,305
1
43
57

3

Garbage collection happens automatically based on scope accessibility, but disposal does not. http://stackoverflow.com/questions/1691846/does-garbage-collector-call-dispose – recursive Jun 03 '13 at 20:37
Response from the EF team (The ones who wrote the DbContext class) explaining why in almost all cases, DbContexts don't need to be manually disposed: http://blog.jongallant.com/2012/10/do-i-have-to-call-dispose-on-dbcontext.html#.Ua0IS5yXT0U – Robert McKee Jun 03 '13 at 21:28
You edited your answer. Previously, it was incorrect. I've given you an upvote. – recursive Jun 03 '13 at 21:34
The answer was correct, but the why was technically wrong. I've edited it so that it is correct, and mentioned the unusual circumstances in which you have to manually dispose the DbContext. – Robert McKee Jun 03 '13 at 21:45

score 0 · Answer 3 · edited May 23 '17 at 10:25

There are multiple ways of handling db connection in .NET
One of my favorites is the one called one dbcontext per request, which basically means you initialize your dbcontext when needed, do the work without thinking about instantiating or disposing, and dispose automatically when the request is done. (kinda UnitOfWork-ish)

I've already shown this approach here. It's not only applicable to EF, but to Linq2SQL, ADO.NET, etc. as well.

When is it necessary to dispose db connection

3 Answers3