ASP.Net MVC: Best Practices for dbml files...

Question

This may just be a yes or no type of question but here goes anyway...

From all (well most) of the examples that I've seen for using mvc, it appears that method for creating the dbml file is just drop the entire schema of the database into it and let it autogenerate all of the linq to sql goodness. It appears that you just need one of these (as you can't have duplicate tables in separate dbml files in the same project) but for some reason it would seem like there's a better way to do this...especially when dealing with a large project that has a fair number of tables.

So is this the proper way to go about creating a dbml file to use in a mvc project, just drop the entire table structure into and go to town? If not, how do you do it?

Answer 1

If the schema was large, I think i would be relying fully on a SQLMetal script to generate my *.dbml and backing classes. This way you can just regenerate your entire data model whenever your database gets updated. Otherwise, if a table, view, etc, gets updated in the database you don't have to delete and then re drag-and-drop that table into your visual *.dbml file.

Actually, I am not expert with SQLMetal, but I think you can even use it it to generate everything you need for Linq-toSql and not even require/generate a *.dbml file.

Answer 2

I'm not sure yet - its a problem I'm still working on but I think that the answer is that should it be desirable to have multiple dbml files - effectively views of your data - then you want to host the dbml files in their own projects so that you can have the similar things in multiple namespaces and not have them conflict.

This being the case the next logical step is to put your dbml files/models into their own projects by default and to learn to work with them when set up that way. This will also aid reuse of a model of database where you have more than one application interacting with that database.

There are certainly issues with separating the thing out and also with having multiple dbml files in a a single project (in terms of ensuring that extensions to the classes are implemented conistently in all instances for example) but I've got a case where its not inappropriate.

Good question, answer probably tends towards being "just one" but not in every case...

Answer 3

Personally I prefer to create the classes/association in the .dbml and then generate the database from that.

Just add the following class to your project

partial class MyDataContext {
    partial void OnCreated() {
        if (!DatabaseExists())
            CreateDatabase();
        }
}

Answer 4

Glad i found this question as the code I have inherited has a large amount of tables and SPs in the .dbml , so much so that it frequently crashed VS when I tried to open it :(