LINQ to SQL architecture. What is best?

Question

This question is sort of a pool. We are trying to identify the best archtecture while using a ORM like LINQ to SQL. The archture we are defining is for sort of framework that other applications will access either trough directly referencing the DLL or through a webservice. We have .NET apps and PHP apps.

The possibilities are:

Multiple data contexts: Separting the database into units of work and create separate contexts for each one.

Pros:

• Easy of use
• Classes will be broken into different namespaces
• Smaller domain to maintain

Cons:

• Objects have to be duplicated if related, creating maintenance hell
• Objects cannot be passed between context, creating the need for another hit on the data base

Single data context: All tables, views, procedures, reside in the same huge context.

Pros:

• No duplication
• Relationships are easy to manage, basicaly the LINQ takes care of it.
• Better performance, less hits on the DB.

Cons:

• All tables are in the same namespace, code completion becomes nuts
• Not the best for the designer (at least on VS2008)
• Cannot be selective in what to save and what not. Save all, or delete all mode.

Well this are things that came up to my mind, so if have any other pros or cons, please tell me and I will include them in the post. Also pick your best one.

Thanks all

Answer 1

I understand your doubts. I had the same when I started to use LinqToSql. To help me find the better way I started to create a personal project where I could test all approaches without the worry and without preconception.

During this exercise I found that the only one context approach is the most useful. This solution appears to be easier to maintain and if you need to recreate the domain you will manage only one file in only one project.

Other aspect I realized during the exercise is that use the LinqToSql directly is not efficient in the terms of organization. If you have a project where a team will execute the development instead of only one person you should “shield” the LinqToSql from them. There should be a “sheriff” who will handle with the domain and you also should use some abstraction mechanism to protect the model from abusing (I implemented a Repository pattern and it worked well but you could find different approaches).

I also faced the problem of creating some logical groups inside the domain. Well in the true what I did was to use some DDD (Domain Driven Design) techniques to create what is called aggregates. Aggregates are a logical arrangement of entities inside a domain where you have a root entity (that works as an aggregator) and several other satellites entities related between them. You can do this creating some new entities in the LinqToSql domain. These new entities will be disconnected from the database and will work as aggregators. This approach will enable you to create “sub-domains” inside your domain and help you to have a better design.

In the end I realized that the best way to use LinqToSql is to take the context like a simple DAL. Reuse its domain, with some extensions (where we can use T4 to help us to create the code), where the entities are transformed into DTOs (Data Transfer Objects) to expose the data to the other layers.

I am publishing (it’s not finished yet) the steps I took during the exercise in my blog: http://developmentnirvana.blogspot.com/