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

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

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

Answer 2

I believe that data context are pretty lightweight, mostly just containers. The data isn't actually loaded into the context until you query it. I don't think it would be wrong to have a single data context, but only instantiate it as needed (as a unit of work) rather than keeping it around all the time. That way, you don't have a long-lived object that may continue to grow bigger and bigger.

If your tables can be separated into related groups of tables, you may want to consider having a separate data context for each of these groups. I'm not sure how LINQ would handle a query using data from multiple contexts, but it seems like it ought to work as long as the tables are on the same server. You'd have to check this if you did break things down into multiple contexts and had queries that needed tables from more than one.

Typically I use a single context and instantiate it as needed, but I don't have any databases that are quite as big as yours.

Answer 3

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.