DBML customization vs regeneration

Question

I'm having a database with a lot of foreign key relationships defined. When I simply drag any table involved in these FK connections into the DBML editor, so as to machine-generate the DBML file, these will all be represented as associations.

From here I'm able to make any changes to these associations: I might want the parent end of the association to be internal rather than public so that a JSON serializer (say) will not get caught in cyclic references; or in a connection between the tables Form and FormAnswer, I might want the child property to be called Answers rather than the machine generated FormAnswers.

Now, if the database design is changed, and I want to update the DBML to reflect this change, it seems these customizations would require me to track down every single change and update it manually (Add property, set it's source, source data type, C# data type...)

This can be a rather tedious process; what I'm asking is whether there is any way to automate this.

1. Can I have these changes reflected on the SQL server?

The ideal solution, it seems, would be if there was any way to make these specifications directly in the SQL Server database diagrams, so that a full re-generation of the DBML file (deleting everything and dragging it onto the DBML editor anew) would come up with the exact same result.

Suspecting that I would already know about the above, if it was achievable, I'd be happy to settle with:

2. Can I extract these changes to a class of its own?

Since all Linq to SQL entities are being generated as partial classes, I thought for a while that I might be able to create a new file, that I maintain manually, to which I could copy all changes as the ones mentioned.

So whenever I've changed an association, I would dig into the designer.cs code, cut the modified association, and paste it into my own file. Upon re-generation, I would expect compiler errors for any duplicates, and easily step through and remove these associations from the DBML. The problem here is that the associations seem to be only properties with attributes. If Form has a property called Answers, and the DBML generator will try to create a property called FormAnswers, the resulting Form object would simply have both properties, which is not at all what I want.

Has anybody had any luck with either of these solutions? Or if you know of any other way to deal with the problem, I'm open for suggestions.

Answer 1

I haven't found any way to automatically update my DBML based on changes to the database without clearing the entire canvas and re-dragging the items in again. And as you say, the FKs are all renamed to defaults which is annoying.

DBMLs are in XML format and I've often found that once the major database changes settle down, it is faster and easier to just edit the XML (Open With ... ) and make the modification there as you do in parallel to the creation script that I keep as part of my project.

Then you rebuild, or perhaps just Run Custom Tool to regenerate the changes to the cs files.

This was in VS 2008, not sure if it's still a challenge in 2010.

HTH

Answer 2

Any update command is completely missing in Linq-To-Sql designer. So I think designer is not usable in real incremental project. There are three ways to solve this issue:

• Don't use the designer and code your mappings
• Don't use the designer and write your mappings in XML. SqlMetal is helpful for initial generation.
• Create custom tool which will add your modifications. Each time regenerated complete model and run this tool.

Answer 3

I have an add-in for VS (2008 and 2010) that may help you out here. It adds DB <=> DBML sync, and has a number of settings giving you control over what type of changes to sync, as well as 'exclusion lists' allowing you to mark individual tables/views/members/FKs as "don't touch" items.

As for the FK => association issue you mention: the sync options allows you to exclude all or individual child navigation properties to avoid circular references that can cause trouble when you're serializing entity objects.

You can download the add-in and get a trial license from http://www.huagati.com/dbmltools/ if you want to take it for a test spin.