LINQ to SQL multiple DataContext-s

Question

I'm trying to figure the best strategy about how to organize DataContexts. The typical DB we work has between 50 and 100 tables usually in third-normal form and with many relations between them. I think we have two options:

1. Put all tables in a single context. This will ensure that anything we do will be committed in the correct order in database. The problem is that the LINQ designer will be a mess with 50+ tables and I'm worrying performance may be affected.
2. Create several data contexts based on the logical grouping of tables. The problem is that there will be places where one side of a relation will be in one context and the other in another one. We'll have to manually take care of committing both context-s in the correct order.

Is there any recommended practice to handle this?

More details:

I want to create my own entities and unit of work on top of LINQ to SQL. Entities will be defined in a xml model file where the mapping to LINQ entities will be specified also. A custom tool will generate my entities (POCO) based on the model. The client code will interact only with my entities and my unit of work; never directly with the DataContext or LINQ entities. However I do not want to duplicate what LINQ to SQL provide out of box so I want to use the underlying LINQ DataContext. This means that I cannot have two orders in different data contexts, because it wouldn't be possible to map my POCO Order with both of them.

Answer 1

You should create contexts that allow you to perform units of work. This may involve overlapping table mappings.

Context1 : Customer has many Invoices

Context2 : Customer has many Orders

Context3 : Invoice has many Orders

Answer 2

LINQ-to-SQL mappings are like typed DataSets, in that when you use one, you're dealing with a session containing data. You can have the same tables in several different DataContexts. They're only classes, after all; they don't mean anything until you start interacting with the database, by filling them with existing data or using them to create new data.

So perhaps you have Customer, Address, Phone, etc. tables that you deal with when you're sending out a new catalog. Then you have Invoice, Line Item, Product, etc. tables that you use when you're creating an order. But in that latter set you may want to have Customer as well. That's fine. You should just take care to only have one session active at a time so that you're not using inconsistent data. You shouldn't have problems from overlapping entities in your various DataContexts as long as you're not using them in an overlapping way.

As far as the clutter, you can put your DataContext in a specific namespace, and you can also put your various entities in a specific namespace (albeit only one namespace per set of entities in a DataContext). You can do this in the Properties window. This will let you keep the Intellisense less jumbled.

Answer 3

I use one datacontext per database.

Average tables can be up to 100, however from experience I don't experience any performance issues.

The datacontext is in a separate project, which is compiled. The resultant dll referenced from the BLL

Answer 4

This is a common question that has been thoroughly analyzed here: http://craftycode.wordpress.com/2010/07/19/linq-to-sql-single-data-context-or-multiple/

In essence, you should create at most one data context per strongly connected group of tables, or one data context per database.