LINQ to SQL multiple DataContext-s

sql linq linq-to-sql datacontext
Albert picture Albert · Oct 24, 2008 · Viewed 7.6k times · Source

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

Kevin Craft picture Kevin Craft · Jul 19, 2010

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.

Related questions

How to store a list in a column of a database table

So, per Mehrdad's answer to a related question, I get it that a "proper" database table column doesn't store a list. Rather, you should create another table that effectively holds the elements of said list and then link to it …

Read More
LINQ to SQL - Left Outer Join with multiple join conditions

I have the following SQL, which I am trying to translate to LINQ: SELECT f.value FROM period as p LEFT OUTER JOIN facts AS f ON p.id = f.periodid AND f.otherid = 17 WHERE p.companyid = 100 I have seen …

Read More
How can you handle an IN sub-query with LINQ to SQL?

I'm a bit stuck on this. Basically I want to do something like the following SQL query in LINQ to SQL: SELECT f.* FROM Foo f WHERE f.FooId IN ( SELECT fb.FooId FROM FooBar fb WHERE fb.BarId = 1000 ) Any …

Read More