How to Refresh the .dbml when the Database changes
I make many changes to my database while developing – adding or renaming columns, new tables, new views etc. The problem is that LINQ-to-SQL’s designer tool doesn’t have a Refresh button. The recommendation is that you delete the changed entity from the designer, and re-add it.
This is obviously a kludge by Microsoft, since this loses any changes you’ve made to the old entity, e.g. if you customise it by modifying the access, renaming mapped columns, or add associations e.g. between tables and views.
I also had another issue – my database has above 250 tables and views in it. If you drag all these onto the design surface, then it somewhat resembles meatballs in spaghetti – it’s unusable for design work.
Then bear in mind that if you want to create an association between a view and a table (see my other FAQ about this), so that the view becomes a child entity of the table in the code, you have to
- try and find both tables in this massive diagram, and
- somehow drag a relationship between them.
The designer is therefore utterly useless in anything beyond about 20 or so tables.
I decided to write a WinForm application to make managing my .dbml easier. Reading the .dbml is very easy as it’s just an XML file with a published schema. I also needed a way to read the database structure – I already had some code for reading a SQL database structure but it could not replicate the clever work that goes on when reading StoredProcedure output formats. I found that the simplest way was to use the SqlMetal.exe command line tool that comes in the .NET Framework to generate a new .dbml with all tables, views and stored procedures.
I then managed to write some code that compared the existing .dbml with the generated one and updated it accordingly. I may one day re-write this to make it available but it’s not completed.
Managing the DBML
To manage the .dbml my WinForm application just lists the tables and procedures as A-Z and shows the properties I wanted and allow me to create or modify the unsupported properties.