A Linq to Sql - Multiple .DBML files or one .DBML File

Question

I am working on a web application using ASP.NET 3.5. The application has hundreds of tables. I was told in a seminar that I should use one .DBML file for the entire application instead of using multiple .DBML files (there was also a post in stackoverflow that said the same thing). Given that I have so many tables does using one .DBML file make sense or am I better off creating multiple .DBML files that are logically grouped?

For instance, I was thinking of creating the following .DBML files:

• Customer
• Vendor
• Employee
• Sales Order

One of the concerns that I have about using multiple .DBML files is how I would handle updates across .DBML files. For instance, if I had to update a field on the customer table when a new sales order was entered. How would I handle that? I certainly don't want to include the customer table in both the Customer and the Sales Order .DBML files. Could I wrap the operations in a TransactionScope?

I don't know if the following has any impact on the answer, but my plan is to use the repository pattern and POCO classes so that references to the table definitions in the .DBML file will be local to my data access layer.

Thanks

Answer 1

I would have to advise you to use ONE dbml file for all your tables.

If you are trying to join two tables from different data contexts, it makes your code more convoluted. It can be done by simulating cross context joins, but why put yourself in that situation. Keep it simple stupid.

Also, if you do decide to go with 2 or more dbml files and you mistakenly add the same table to multiple data contexts, then you will get a "This member is defined more than once” error.

Answer 2

I've worked on a project were the team decided to separate the domain in four different DBML files. The main reason for this spit-up had to do with the LINQ to SQL designer. The designer wasn’t build to be used with big domains.

These four 'sub domains' in this project were reasonably separate, but there was some overlap and this bit us all the time. With this experience I'd advise you to use a single DBML file per domain. Usually you’ll have one domain per database, so this means one DBML file per database.

Personally, I'm against using TransactionScope in production code (but I use it for integration tests all the time), but that is another discussion. However, when you do decide to go with multiple DBML files, and have a use case where you need to create multiple DataContext classes, you can run them in the same transaction, as follows:

using (var con = new SqlConnection("constr"))
{
    con.Open();
    using (var tran = con.BeginTransaction())
    {
        using (var context = new CustomerDataContext(con))
        {
            // do some work with it
            context.SubmitChanges();
        }

        using (var context = new VendorDataContext(con))
        {
            // do some work with it
            context.SubmitChanges();
        }
    }
}

This is the model I use most of the times, even with a single DataContext. However, the creation of the connection and transaction are abstracted away, so there’s only one place in code where the transaction is made.