LINQ to SQL: Multiple / Single .dbml per project?

Question

I've read Rick Strahl's article on Linq to SQL DataContext Lifetime Management hoping to find some answers on how I would manage my .dbml files since they are so closely related to DataContext. Unfortunately, Rick's article seems to be focused on DataContext lifetime at runtime though, and my question is concerned with how the .dbml's should be organized at design time.

The general question of 'Best practices with .dbml's' has been asked and answered here, and the answers have focused on external tools to manage the .dbml.

I'm asking a more focused question of when and why should you not have a single .dbml file in your LINQ to SQL based project?

Answer 1

I'd say, you always just need 1 dbml-file PER database. If you have multiple connections to other databases, consider design or use seperate dbml-files. Either way, one is enough per database.

This because the dbml mapps to your tables and why not just use one "data connector" / "data layer" for that, seems odd / weird design to use more than one.

It's probably more controllable using only 1 aswell.

Answer 2

In my opinion, you can split the .dmbl files so that each hold a subset of tables/procs from a DB according to function and relationship. I have not done this yet so this is just opinion.

I have however created multiple .dbml files to assist with unit testing. If you work in an environment which restricts you to using stored procs in your production environment then you cannot use the table part of the .dbml (you can use the proc part though). So if you "unit test" (this is really integration testing) the DB layer of your code you can call the proc wrapper and then check the results by querying the tables through the .dbml interface. In cases like this I'll split the .dmbl file into just the tables that I want to query in my "unit test."

Further info: I have 2 solutions that I build. One has unit tests and is never built on the build server. The other is built on the build server and deployed to test/production.

Answer 3

The answer is tricky because it's what the situation requires. I try to logically separate each DBML into contexts (after all, the DBML provides the DataContext functionality). So if my app has a single context, then it doesn't make sense for me to have a separate DBML for each table. Context is king when creating your DBML files is what I say.

Answer 4

Say you have a database:

Database D contains tables A, B, C, X, Y, Z where

• Table A has a foreign key relationship with tables B and C
• Table X has a foreign key relationship with tables Y and Z
• Table X also has a foreign key relationship with table A

Say you have 2 DBML files P and Q based on database D

• DBML File P contains entities A', B' and C' where A' is connected to B' and C' via associations.
• DBML File Q contains entities X', Y' and Z' where X' is connected to Y' and Z' via associations.

AFAIK, there is no way for DBML files P and Q to contain an association between entities A' and X'. This is the single biggest problem with having multiple DBML files.

To my mind, a DBML file reflects the data-model represented by the tables and constraints on those tables in a database. If some tables or constraints are missing from a set of DBML files, then the set of DBML files do not accurately reflect the underlying database.

Going back to our example, if there was no relationship between tables A and X in database D, then one would be able to create 2 DBML files.

Generically speaking, one can have multiple DBML files if each DBML file contains all entities and relationships that are connected. Note that the converse is not a problem, i.e., one can have a single DBML file containing multiple groups of entities that are not related to each other by any associations.

Answer 5

Another thing to bear in mind is that LINQ uses the DataContext to track the identities of the instances of entities it creates. Therefore, an entity representing a row in a table created by one instance of the DataContext class is not the same as one created by another, even if all the properties are the same.

When one has multiple DBML files, then by necessity, there will be multiple instances of DataContexts, one for each DBML file. Therefore, entities can't be joined or shared from one DataContext to another.

This is applicable when an entity exists in both (or all) DBML files.

Answer 6

Please note that LINQ2SQL is intended for simple and easy way to handle database relationship with objects.

Do not break table relationship and units of work concepts by creating multiple .dbml files.

If you ever need to create multiple .dbml files (which i don't recommend), then try to satisfy the following:-

1. If you create multiple databases with no relationship between those database tables.
2. If you want to use one of these .dbml just to handle stored procedures
3. If you do not care about unit of work concept.

If your database is too complex, then I would consider ORM such as NHibernate, EF 4

Answer 7

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

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

Answer 8

Yes, hm, I read the thorough analysis in craftycodeblog, but wouldn't it be nice if you could get all the benefits from both sides?

I want to have a single DataContext for my application, but still split it into multiple .dbml files (and multiple dbml.layout and designer.cs files). I assume this is not supported by Visual Studio, but it would solve all problems.

The use case is this: A large application is composed of several modules, each of which adds a set of tables (and of course code) to the application (i.e. to a single database) and has access to the code and the tables of other modules.

Let's say module ABC defines tables A, B, C, and module XYZ defines tables X, Y, Z. The XYZ tables may have foreign keys to the ABC tables. It would be best if: * When working with the code of module ABC you "see" only the ABC tables, both in the designer and in the linq editor. * When working with the code of module XYZ, you can see only XYZ tables in the dbml designer, so that you reduce clutter and see only your module's schema. The linq editor should have access to both ABC and XYZ tables.