ASP.NET MVC/LINQ - retreiving tables and number of connections

Question

Still extremely new to the whole MVC/LINQ thing. I'm in the processes off building a blog, and I need to build a table for the posts, and within each post build a table for the comments of that post.

To build the tables, I'm doing something like:

postsTable = (new DataContext(connectionString)).GetTable<Post>();

Unfortunately for each comments table, it does the same thing. I see DataContext(connectionString) and assume it's reconnecting every single time. I feel like I should be able to connect once at the start of the fetch and then close the connection when I'm done. Am I doing this wrong? Could somebody point me in the right direction?

Answer 1

Go to http://www.asp.net/MVC and check out the tutorials and starter kits there are a whole bunch of good articles that will show you what you are looking for

Answer 2

Since you're doing LINQ to SQL you can...

1. Define the blogs and comments tables on your database with the appropriate foreign key relationship.
2. Drag and drop both onto your dbml designer surface. Click the save button; that's when the code is generated.
3. When you populate your viewmodel (or however you're getting your data back to the result of a controller action) only query the information you need at the time.

For a single view of a blog entry with associated comments ithe LINQ query might look like so...

    YourDataContext dataContext = new YourDataContext();
    var blogData = (from b in dataContext.Blogs
       where b.BlogId == 1
       select b).SingleOrDefault();

// you should now have a single blog instance with a property named Comments.  Set the 
// fetch mode to eager if you plan to always show the comments; leave it lazy to only do 
// the lookup if necessary.  Execute all of your queries/accesses before you pass 
// data to the view

Answer 3

What your looking for is a pattern called "Session/Context per Request". The most popular and cross ORM, cross WebForms/MVC way of doing this is at the start of a request new up a context, throw in session, and finally at the end pull it down and dispose of it.

From: http://blogs.microsoft.co.il/blogs/gilf/archive/2010/05/18/how-to-manage-objectcontext-per-request-in-asp-net.aspx

public static class ContextHelper<T> where T : ObjectContext, new()
{
  #region Consts

  private const string ObjectContextKey = "ObjectContext";

  #endregion

  #region Methods

  public static T GetCurrentContext()
  {
    HttpContext httpContext = HttpContext.Current;
    if (httpContext != null)
    {
      string contextTypeKey = ObjectContextKey + typeof(T).Name;
      if (httpContext.Items[contextTypeKey] == null)
      {
        httpContext.Items.Add(contextTypeKey, new T());
      }
      return httpContext.Items[contextTypeKey] as T;
    }
    throw new ApplicationException("There is no Http Context available");
  }

  #endregion
}

You can also mess around with new()ing up the DataContext in your controller constructor as seen here: http://www.stephenwalther.com/blog/archive/2008/08/20/asp-net-mvc-tip-34-dispose-of-your-datacontext-or-don-t.aspx

Now this article says you don't have to worry about disposing of your context but I disagree. With modern ORMs you really want to take advantage of the "session" like ways they track and persist changes. Without manually disposing of your context all sorts of bad code or horrible unit of work patterns won't throw exceptions like they should. IMHO the session aspect of an ORM is the most important part. Ignore at your peril.

If your using SQL Server the connection pooling feature negates a lot of the performance impacts of opening and closing a connection. Unless you start doing 100,000 requests a second I wouldn't worry about it.