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.

Answer 3

Hey,

I have a fairly large project with 200 tables or so and it works fine in one data context; since all of the data is pretty interconnected (designed between 2nd and 3rd normal form), it would be a pain to use multiple data contexts.

Multiple context issues would not be fun in the areas where the app needs to do a query against tables that are split; you'd have to ensure certain tables are in separate data contexts regardless, because of the way LINQ works and the drill-down hierarchy. At least from a convenience standpoint, not that it couldn't be done.

HTH.

Answer 4

I would use one DBML file per context. By context, I mean the operation required to be done by your user as she/he is in one particular window of your application. For instance :

1. You have a GUI allowing you to manage your Customer objects ; I would then consider having a CustomerManagementDataContext within which I would add the tables for customer management tasks related, and all the depencies.

This way, you may have a context per window, if you see what I mean. But it all depends on your relational model, this might be easier fto include all the tables into your DBML file, but then, it does create a context a bit more complicated, and doesn't point out the tables related to your customer management or else, depending on the context you built.

Indeed, for ease of use, it is neither bad to use only one, but this is no good practice, if I may mention it.