Introduction

When using LINQ to SQL in one of our projects, we came across a situation where we had to make a lot of changes to the DBML file SqlMetal generates, such as:

Change all lookup values to enums so that our code looks more elegant

Modify the generated association member names to our own names for better readability

Remove unwanted tables/Stored Procedures

Export the DBML as an image

etc.

While trying to make all the above changes, it became cumbersome to repeat the same changes again and again whenever we regenerated the DBML. So, I started with writing a small script which finally got transformed to a VS add-in.

Installing the Add-in

I have included a Wix project along with the add-in source which generates the MSI. So, just install and you are ready to go.

How It Works

The add-in looks for an XML file in the same folder as the DBML file with a name as DBML file name + "custom.xml". So, if your DBML file name is Northwind.dbml, then your custom mapping file should be named as "northwind.dbml.custom.xml".

The add-in adds two context menu options to all the DBML files in the solution.

Create Mapping XML

This command just creates the initial mapping file for you to customize. It just creates a copy of DBML file and changes the file name.

Apply Customization

This command applies the custom changes to the existing DBML file.

Create/Refresh

This command either creates the initial DBML (or) refreshes the complete DBML with the latest changes from the selected database and then applies the custom changes.

Both commands will regenerate the designer.cs and layout files automatically so that you can view the modified DBML in the designer once the task is done.

How To Create a Custom Mapping XML

Just create a copy of the DBML file and rename as per the naming conventions by appending with "custom.xml". One advantage with this approach is Visual Studio provides auto-completion for all possible attributes as well as node names. You can add a custom attribute called "CustomizationType" which takes "Add", "Update", "Delete", which specifies the customization you are looking for that specific node.

For example, if you want to make a specific column from the database to be invisible in your code for whatever reason, you can update the column node in the mapping file as below:

<ColumnCustomizationType="Delete"Name="Description"Type="System.String"DbType="VarChar(50) NOT NULL"CanBeNull="false"/>

For changing the lookup columns type to enum, you can modify the node in the custom mapping file as below:

Initial Version

<ColumnName="ProductStatusCd"Type="System.Int16"DbType="SmallInt NOT NULL"CanBeNull="false"/>

In the Custom XML

<ColumnName="ProductStatusCd"Member="ProductStatus"Type="global::SampleApplication.ProductStatus"DbType="SmallInt NOT NULL"CanBeNull="false"/>

If you observe the above custom change, I have changed the Member attribute also to "ProductStatus", so in my code, I can refer to this column as "ProductStatus" instead of "ProductStatusCd".

Similarly, you can make any custom change which is allowed in the DBML by the VS designer and save the custom XML file, and the add-in takes care of applying these changes to the final DBML file.

Screenshots to Show the Usage

Add a new DBML file to the project using Visual Studio Add New Dialog. This will add a blank DBML file to the project:

Choose "Create/Refresh" command of the addin to generate the DBML from the given database connection:

Provide Connection details as well as sqlMetal specific properties like serialization type etc.:

The screen below shows the initial DBML generated. Now we have to create the custom mapping file for this:

Choose "Create Mapping XML" command to create the base mapping XML based on the initial DBML file. We use this file to specify our customizations. You can remove those tables/columns/functions from this mapping file if you don't need any customization for them. But keeping them will make addin think that you need some customization for these entities as well. But as long as we don't specify any customizations specified, addin will just ignore them.

Here is a sample custom mapping file which I have used in the sample (now included in the source code). I have tried to explain as much as possible but if anything is not clear, let me know.

<?xmlversion="1.0"encoding="utf-8"?><!--At the root level, you can change attributes like Serialization,
Class,EntityNamespace,ContextNamespace.
But you can always customize the code if you have any need to change
AccessModifier,BaseType etc.
You can include any extra namespaces you want to add, using "CustomNapespaces" node.
--><DatabaseName="TestDB"Serialization="Unidirectional"xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007"><CustomNamespaces><NamespaceName="System.IO"Alias="MyAlias"/><NamespaceName="System.Text"/></CustomNamespaces><!--
Possible changes implemented are "Member" and you can add custom attributes
(xml serialization,propertygrid related etc) to the generated class.
--><TableName="dbo.ProductCategories"Member="ProductCategories"><!--
Possible changes implemented are "Name" and "Id".
for example, if your table name in db is "product_category",
you can change to "ProductCategory" if you follow specific naming
conventions or to satisfy FxCop:)
--><CustomAttributes><AttributeName="YourOwnCustomAttribute">"Parameters if any"</Attribute></CustomAttributes><TypeName="ProductCategory"><ColumnName="ProductCategoryID"Type="System.Int32"DbType="Int NOT NULL IDENTITY"IsPrimaryKey="true"IsDbGenerated="true"CanBeNull="false"/><ColumnName="Description"Type="System.String"DbType="VarChar(50) NOT NULL"CanBeNull="false"/><ColumnName="ParentCategoryID"Type="System.Int32"DbType="Int"CanBeNull="true"><CustomAttributes><AttributeName="global::System.ComponentModel.Description">
"Category Name"</Attribute><AttributeName="DisplayName">"Category"</Attribute><AttributeName="Category">"Main Properties"</Attribute></CustomAttributes><Column><AssociationName="FK_ProductCategories_ProductCategories"Member="ParentCategory"ThisKey="ParentCategoryID"OtherKey="ProductCategoryID"Type="ProductCategory"IsForeignKey="true"/><AssociationName="FK_ProductCategories_ProductCategories"Member="ChildCategories"ThisKey="ProductCategoryID"OtherKey="ParentCategoryID"Type="ProductCategory"DeleteRule="NO ACTION"/><AssociationName="FK_Products_ProductCategories"Member="Products"ThisKey="ProductCategoryID"OtherKey="ProductCategoryID"Type="Product"DeleteRule="NO ACTION"/></Type></Table><TableName="dbo.Products"Member="Products"><TypeName="Product"><ColumnName="ProductID"Type="System.Int32"DbType="Int NOT NULL IDENTITY"IsPrimaryKey="true"IsDbGenerated="true"CanBeNull="false"/><ColumnName="ProductName"Type="System.String"DbType="VarChar(50) NOT NULL"CanBeNull="false"/><!--Example to add a custom property to the Product--><ColumnName="NewCustomProperty"Type="System.String"CustomizationType="Add"/><ColumnName="ProductCategoryID"Type="System.Int32"DbType="Int NOT NULL"CanBeNull="false"/><!--Example to change the datatype to enum and change the
Member attribute value such that we can refer in our code as
"ProductStatus" which is more meaningful than "ProductStatusCd".
Also here i have n't specified any CustomizationType.By default
its assumed to be "Update".This is just to save some typing
as most of the changes we do ,are updates. :)
--><ColumnName="ProductStatusCd"Member="ProductStatus"Type="global::SampleApplication.ProductStatus"DbType="SmallInt NOT NULL"CanBeNull="false"/><!--For Associations, you can mention the Cardinality="One"
if you know the relation is always one to one.
Other customizations possible are
1.Change the Member attribute to your custom name
--><AssociationName="FK_Products_ProductCategories"Member="ProductCategory"ThisKey="ProductCategoryID"OtherKey="ProductCategoryID"Type="ProductCategory"IsForeignKey="true"/></Type></Table><!--For Functions, you can change the Method name to a more meaningful
name than the default generated one. Sp_getproducts->GetProducts
--><FunctionName="dbo.sp_getproducts"Method="GetProducts"><!--For Parameter, you can change the "Parameter" to a more meaningful
name than the default generated one. category_id->CategoryID
--><ParameterName="category_id"Parameter="CategoryID"Type="System.Int32"DbType="Int"/><!--For ElementType, you can change the Name to a more meaningful
name than the default generated one. Sp_getproductsResult->ProductDetails
--><ElementTypeName="Sp_getproductsResult"><!--For ElementType Columns, you can change the Member and Type--><ColumnName="ProductID"Type="System.Int32"DbType="Int"CanBeNull="true"/><ColumnName="ProductName"Type="System.String"DbType="VarChar(50)"CanBeNull="true"/></ElementType></Function><FunctionCustomizationType="Delete"Name="dbo.sp_alterdiagram"Method="Sp_alterdiagram"><ParameterName="diagramname"Type="System.String"DbType="NVarChar(128)"/><ParameterName="owner_id"Type="System.Int32"DbType="Int"/><ParameterName="version"Type="System.Int32"DbType="Int"/><ParameterName="definition"Type="System.Data.Linq.Binary"DbType="VarBinary(MAX)"/><ReturnType="System.Int32"DbType="Int"/></Function><FunctionCustomizationType="Delete"Name="dbo.sp_creatediagram"Method="Sp_creatediagram"><ParameterName="diagramname"Type="System.String"DbType="NVarChar(128)"/><ParameterName="owner_id"Type="System.Int32"DbType="Int"/><ParameterName="version"Type="System.Int32"DbType="Int"/><ParameterName="definition"Type="System.Data.Linq.Binary"DbType="VarBinary(MAX)"/><ReturnType="System.Int32"DbType="Int"/></Function><FunctionCustomizationType="Delete"Name="dbo.sp_dropdiagram"Method="Sp_dropdiagram"><ParameterName="diagramname"Type="System.String"DbType="NVarChar(128)"/><ParameterName="owner_id"Type="System.Int32"DbType="Int"/><ReturnType="System.Int32"DbType="Int"/></Function><FunctionCustomizationType="Delete"Name="dbo.sp_helpdiagramdefinition"Method="Sp_helpdiagramdefinition"><ParameterName="diagramname"Type="System.String"DbType="NVarChar(128)"/><ParameterName="owner_id"Type="System.Int32"DbType="Int"/><ElementTypeName="Sp_helpdiagramdefinitionResult"><ColumnName="version"Member="Version"Type="System.Int32"DbType="Int"CanBeNull="true"/><ColumnName="definition"Member="Definition"Type="System.Data.Linq.Binary"DbType="VarBinary(MAX)"CanBeNull="true"/></ElementType></Function><FunctionCustomizationType="Delete"Name="dbo.sp_helpdiagrams"Method="Sp_helpdiagrams"><ParameterName="diagramname"Type="System.String"DbType="NVarChar(128)"/><ParameterName="owner_id"Type="System.Int32"DbType="Int"/><ElementTypeName="Sp_helpdiagramsResult"><ColumnName="Database"Type="System.String"DbType="NVarChar(128)"CanBeNull="true"/><ColumnName="Name"Type="System.String"DbType="NVarChar(128)"CanBeNull="true"/><ColumnName="ID"Type="System.Int32"DbType="Int"CanBeNull="true"/><ColumnName="Owner"Type="System.String"DbType="NVarChar(128)"CanBeNull="true"/><ColumnName="OwnerID"Type="System.Int32"DbType="Int"CanBeNull="true"/></ElementType></Function><FunctionCustomizationType="Delete"Name="dbo.sp_renamediagram"Method="Sp_renamediagram"><ParameterName="diagramname"Type="System.String"DbType="NVarChar(128)"/><ParameterName="owner_id"Type="System.Int32"DbType="Int"/><ParameterName="new_diagramname"Type="System.String"DbType="NVarChar(128)"/><ReturnType="System.Int32"DbType="Int"/></Function></Database>

Once you are ready with one version of your DBML, you can just keep making changes to your mapping file and use "Create/Refresh" command to update the DBML from the database and automatically apply your custom changes.

Points of Interest

Do not use the VS Designer to create the DBML if you want to use this add-in, because for whatever reason, the association key names generated by VS Designer are different from the ones generated by the SqlMetal tool. SqlMetal names the association keys the same as the foreign key names in the database but the VS designer changes them to the sourcetable_targettable format. As this add-in relies on sqlmetal.exe to generate the initial DBML, do not make any changes to the DBML using the VS Designer. Of course, you can open the final DBML and view the same in the designer to get a clear picture of what is changed and whether all the changes are correctly applied.

Conclusion

I have included the source code for the Wix project as well which will give you a basic idea on how to create a simple setup project using Wix (Windows Installer XML). Apart from solving a common problem while using LINQ to SQL, I hope it will help somebody to write her/his own VS Addin.

It works for me on normal application/library type projects, but if I try and use it on a Website Project, the context menu items aren't there. Do you know if it's possible to make it work with that kind of project?

Glad you liked it.I dont think its possible to support the website project.I tried that when i first created this addin.I couldnt do it because website project doesnt have a project file.
may be you can try the new web application project.that should work.

I have installed your add in for VS2013 and clicked Create/Refresh on my dbml file and after filling in the connection details and clicking OK I get an exception saying it cannot find sqlmetal.exe. I have set my path to include C:\Program Files (x86)\SqlMetalPlus v1.0 and I tried browsing to the directory but still get the same error. On examining the folder there is no sqlmetal.exe present in this directory and i cannot find on on my machine?

Hi,
SqlMetal.exe is a commandline tool provided by microsoft.it is not included in this addin but it should be already available on your machine if you have .NET installed.if you have latest version of .NET then you should be able to find it in
C:\Program Files (x86)\Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools\

if you have older version of .NET then it must be there in one of the folder under C:\Program Files (x86)\Microsoft SDKs\Windows\
Just set this in your PATH and you should be good to go.
Regards
Azeet

This is the dumbest and the biggest headache causing tool ever created, you should get a job at microsoft and continue to make peoples lives more difficult..

Also your latest version does not work with visual studio 2010 out of the box. I dont care what troubleshooting you want me to do .,.. it needs to work out of the box. there were no menus showing up, your documentation is terrible. thanks for your efforts

I am interested only in how to keep my data model in sync with any/all changes to the actual database. It was unclear to me from the article whether this utility's refresh function does that specifically. I.E., reference the stack overflow article stackoverflow.com/questions/1110171/how-to-update-linq-to-sql-dbml-file and its accepted (green checkmarked) answer. This is how I have been doing a COMPLETE refresh of the entire database (not an UPDATE the existing model type of thing). But rather than doing the procedures in the stackoverflow article (manually in the VS2012 designer: delete everything in the VS designer tab, refresh the server explorer, drag-and-drop all the tables/views/sprocs again), does the refresh function of this utility accomplish that exact same thing? Thanks!

the refresh option does exactly what you wanted to do.When you select the Referesh command, the addin will update the DBML with latest changes from your database and reapply the customizations.
But if you schema changes are incompatible with your previous verison then you may have to create the mapping file again and make your customizations again manually.you can use any merge tools like winmerge for this purpose.
hope it helps.
Azeet

Hi Azeet, thanks for the quick response. However, I don't think you got exactly what I was going for. Let me see if I can make the scenario more clear...

First, let's say I never want to have any "customizations" of the dbml whatsoever. In other words, here is the sequence of how I utilize LINQ2SQL:

1. I have an existing database in SQL Server (say it's a recently-created database with 10 tables of data in it).
2. In VS2012, I open up the designer and via the server explorer on the left side, I drag and drop all of the tables onto the design surface, then click save all. I now have my dbml file in my solution and all of the generated code.
3. Two weeks later, I change the schema of one of the tables in the database via SSMS.
4. All I want to do is COMPLETELY RE-generate ALL of the LINQ2SQL plumbing. Not update anything. RECREATE FROM SCRATCH the ENTIRE LINQ2SQL plumbing.
5. As described in the stackoverflow article, using plain vanilla LINQ2SQL with Visual Studio, IF one wants to RECREATE FROM SCRATCH the ENTIRE LINQ2SQL plumbing, one would have to: A) go into the dbml designer, delete everything on the designer surface, then drag and drop ALL of the tables in the database to the design surface and save all. NOW all of the LINQ2SQL plumbing has been RE-generated FROM SCRATCH.
6. I am trying to find a way to not have to do the manual from-scratch regeneration described in #5 previous. So my question is, does the SqlMetalPlus "refresh" functionality do that EXACT thing? Or no.

Otay! I'll try it out. If SqlMetalPlus does that as described, then I'll be wanting to donate some $$ to you if you have a paypal account or some other way to accept donations!!! Why Microsoft hasn't put some button in visual studio that just does the recreate-everything-from-scratch functionality is a freakin' mystery to me, I'm sooooo tired of doing that sequence of events in the designer over and over and over and over for the thousandth freakin' time... (and I've looked for a similar utility all over the web for the past year and couldn't seem to find anything that does this simple task in a scripted/automated way, anyone else feel free to correct me if there is anything out there (other than SqlMetalPlus ostensibly)).

You can update the custom mapping xml anytime manually and select the command "Apply Customization".
If you want to merge your changes to the DB with an existing custom xml(and i think this is what you are looking for) then i dont think the addin can do that because its difficult to merge automatically.
i would suggest to use a tool like winmerge to merge the changes while keeping your existing customizations.

Hi,
Sorry about that.i have all 3 versions VS2010,2012 and 2013 and i am able to install without any issues.
Are you getting the exception during installation or while using?
can you give me some more information.i will try to resolve.
Thanks
Azeet

Excelent piece of code. Does it should work with VB.NET. If it does, then i can't make it work.
I've VS.2010 and every time I apply the Customization, it brings me all the schema without my modifications.