In LINQ-SQL, wrap the DataContext is an using statement - pros cons

Question

Can someone pitch in their opinion about pros/cons between wrapping the DataContext in an using statement or not in LINQ-SQL in terms of factors as performance, memory usage, ease of coding, right thing to do etc.

Update: In one particular application, I experienced that, without wrapping the DataContext in using block, the amount of memory usage kept on increasing as the live objects were not released for GC. As in, in below example, if I hold the reference to List of q object and access entities of q, I create an object graph that is not released for GC.

DataContext with using

    using (DBDataContext db = new DBDataContext())
    {
        var q = 
            from x in db.Tables
            where x.Id == someId
            select x;

        return q.toList();
    }

DataContext without using and kept alive

  DBDataContext db = new DBDataContext()
  var q = 
        from x in db.Tables
        where x.Id == someId
        select x;

    return q.toList(); 

Thanks.

Answer 1

A DataContext can be expensive to create, relative to other things. However if you're done with it and want connections closed ASAP, this will do that, releasing any cached results from the context as well. Remember you're creating it no matter what, in this case you're just letting the garbage collector know there's more free stuff to get rid of.

DataContext is made to be a short use object, use it, get the unit of work done, get out...that's precisely what you're doing with a using.

So the advantages:

• Quicker closed connections
• Free memory from the dispose (Cached objects in the content)

Downside - more code? But that shouldn't be a deterrent, you're using using properly here.

Look here at the Microsoft answer: http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/2625b105-2cff-45ad-ba29-abdd763f74fe

Short version of if you need to use using/.Dispose():

The short answer; no, you don't have to, but you should...

Answer 2

Well, It's an IDisposable, so I guess it's not a bad idea. The folks at MSFT have said that they made DataContexts as lightweight as possible so that you may create them with reckless abandon, so you're probably not gaining much though.....

Answer 3

I depends on the complexity of your Data Layer. If every call is a simple single query, then each call can be wrapped in the Using like in your question and that would be fine.

If, on the other hand, your Data Layer can expect multiple sequential calls from the Business Layer, the you'd wind up repeatedly creating/disposing the DataContext for each larger sequence of calls. not ideal.

What I've done is to create my Data Layer object as IDisposible. When it's created, the DataContext is created (or really, once the first call to a method is made), and when the Data Layer object disposes, it closes and disposes the DataContext.

here's what it looks like:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;

namespace PersonnelDL
{
    public class PersonnelData : IDisposable
    {
        #region DataContext management
        /// <summary>
        /// Create common datacontext for all data routines to the DB
        /// </summary>
        private PersonnelDBDataContext _data = null;
        private PersonnelDBDataContext Data
        {
            get
            {
                if (_data == null)
                {
                    _data = new PersonnelDBDataContext(ConfigurationManager.ConnectionStrings["PersonnelDB"].ToString());
                    _data.DeferredLoadingEnabled = false; // no lazy loading
                    //var dlo = new DataLoadOptions(); // dataload options go here
                }
                return _data;
            }
        }

        /// <summary>
        /// close out data context
        /// </summary>
        public void Dispose()
        {
            if (_data != null)
                _data.Dispose();
        }
        #endregion

        #region DL methods
        public Person GetPersonByID(string userid)
        {
            return Data.Persons.FirstOrDefault(p => p.UserID.ToUpper().Equals(userid.ToUpper()));
        }

        public List<Person> GetPersonsByIDlist(List<string> useridlist)
        {
            var ulist = useridlist.Select(u => u.ToUpper().Trim()).ToList();
            return Data.Persons.Where(p => ulist.Contains(p.UserID.ToUpper())).ToList();
        }

        // more methods...
        #endregion
    }
}

Answer 4

In one particular application, I experienced that, without wrapping the DataContext in using block, the amount of memory usage kept on increasing as the live objects were not released for GC. As in, in below example, if I hold the reference to List of q object and access entities of q, I create an object graph that is not released for GC.

  DBDataContext db = new DBDataContext()
  var qs = 
        from x in db.Tables
        where x.Id == someId
        select x;

  return qs.toList();

  foreach(q in qs)
  {
        process(q);
        //cannot dispose datacontext here as the 2nd iteration will throw datacontext already disposed exception while accessing the entity of q in process() function
        //db.Dispose();
  }

  process(Table q)
  {
        //access entity of q which uses deferred execution
        //if datacontext is already disposed, then datacontext already disposed exception is thrown
  }

Given this example, I cannot dispose the datacontext because all the Table instances in list variable qs **share the same datacontext. After Dispose(), accessing the entity in process(Table q) throws a datacontext already disposed exception.

The ugly kluge, for me, was to remove all the entity references for q objects after the foreach loop. The better way is to of course use the using statement.

As far as my experience goes, I would say use the using statement.

Answer 5

1. First time DataContext will get object from DB.
2. Next time you fire a query to get the same object (same parameters).: You’ll see query in a profiler but your object in DataContext will not be replaced with new one from DB !!

Not to mention that behind every DataContext is identity map of all objects you are asking from DB (you don’t want to keep this around).

Entire idea of DataContext is Unit Of Work with Optimistic Concurrency. Use it for short transaction (one submit only) and dispose.

Best way to not forget dispose is using ().