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 have an add-in for VS (2008, 2010, 2012, and 2013) 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 from http://www.huagati.com/dbmltools/ if you want to take it for a test spin.