MVC DataContext Ok to Share One Connection or Not

Question

When is the "unit of work" no longer a "unit"? Which scenario is better on resources? THe first creates one connection wheras the second creates 4.

using (DataContext dc=new DataContext) 
{
  var orders= from o in dc.orders 
              select ( new Product {  property a= from a in ..join... select x, 
                                      property b= from b in ..join... select y,
                                      property c= from c in ..join... select z.}
                      )
}

OR

using (DataContext dc=new DataContext) 
{
  var orders= from o in dc.orders 
              select ( new Product {  property a = GetPropertyA(), 
                                      property b = GetPropertyB(),
                                      property c = GetPropertyC()}
                      )
}

Answer 1

Generally speaking the less round trips to your database the better. However, in high scale apps we often make our databases more and more 'dumb' so we end up not doing any joins so we don't tie up CPU and memory on the SQL database server. It really depends on which resources you find more scarce. If your SQL server has plenty of resources do the join there, otherwise pulling it back separately would be better. Have you looked at the results of this in the SQL profiler? Are you sure it's 4 connections, or just 4 separate calls?

Answer 2

When using LINQ if you add one entity to another and they use a different data context you are going to get an error. I would suggest keeping the same data context for each unit of work you are using. You can easily identify the unit of work you are processing by using the repository pattern to write your data access classes. If you're not clear on the repository pattern, check out this entry