Linq to SQL: Is it better to have a small DataContext for each page or a global one? [closed]

Question

I am trying out Linq to SQL in an ASP.NET application that uses a large database with lots of foreign keys (100+ tables). I am impressed with how Linq permits you to create a datacontext with all of your relationships intact and to then create Linq statements that automatically join tables. However, this leads to a question: if I am submitting a Linq statement that just works with one or two tables, is it better to have a datacontext that just has the necessary table/tables? It seems to me that if I build a datacontext with all of the tables in the database, it would be quite massive and loading it for every use of Linq would have a negative impact on performance. Am I right?

Comment: I know to create the datacontext only as needed (but thank you nonetheless for mentioning it). The question is really about whether I should have lots of little datacontexts or whether it would be Ok to build one big one.

Answer 1

You should have one DataContext per one group of connected tables. In most applications, this means one DataContext for everything. If you happen to have several sets of tables that you do not need to modify together, you might consider several DataContexts. If you even might need to query across DataContexts, do not separate them.

A DataContext is not just a set of tables - it is meant to be an implementation of the Data Gateway pattern - you can fill it with methods that return the data you need, so you don't have to hardcode queries into every corner of your application. Now, if you had multiple DataContexts, one per page, you would very likely end up having to stick your common functionality (think MyDataContext.GetActiveCustomers()) in every one of them. This would be horrible duplication.

So the answer is that it is usually not OK to build many small DataContexts. This is only feasible if your data is completely separate (different logical or physical databases) or if you are using DataContext as simply a Connection object, which it is not meant to be.

Do note however, that DataContexts should be short-lived - they are an implementation of the Unit of Work pattern and thus their lifetime should be equal to one logical operation (e.g. loading a set of products or inserting a new order). DataContexts are cheap to create and destroy, so do not waste time caching them just because.

Answer 2

This page http://www.albahari.com/nutshell/10linqmyths.aspx (see Myth #10) says it's better to use short-lived DataContext instances.