Tuesday, November 25, 2008

Making custom changes in LINQ-to-SQL designer

This post describes a scenario I encountered recently that would require making custom changes in the LINQ-to-SQL designer.

Even given that the Microsoft rumor-mill has tagged LINQ-to-SQL as terminal given the rise of LINQ-to-Entity, I still find LINQ-to-SQL useful for those non-enterprise solutions. As many developers know, the LINQ-to-SQL class designer doesn’t provide much automated support for keeping your database schema up-to-date with the class designer objects (tables, relationships, etc.)

I haven’t found this to be a huge issue since I work around this limitation by deleting and re-adding tables (which automatically updates the dbml code). I’ve steered clear of making any custom changes through the designer so I can perform this simple work around.

Recently I ran into an issue that makes me want to make manual changes in the designer. It has to do to the way the relationship property names are generated. Let’s look at an example.

Consider you have a table named Grades that represents school grades like 1, 2, 3, and so forth. Now assume you have another table named Worksheets that represents individual worksheets for each school grade. This is a typical one-to-many relationship. Two of the Worksheets columns are StoreGrade (where a PDF worksheet is stored on disk) and DisplayGrade (which grade to display this worksheet for). This allows a worksheet to be reused for 4th grade even though it is stored in the 3rd grade folder. The StoreGrade and DisplayGrade are foreign keys of the Grades table.

When using the LINQ-to-SQL designer, the above scenario will create a Grade object will two properties called Worksheets and Worksheets1. When using intellisense, it really isn’t possible to know which of these properties is for StoreGrade and which is for DisplayGrade. It is possible to change the property names created by using the designer properties for each relationship, but that would mean I lose the ability to delete/re-add a table with ease.

I’m continuing to hold off making custom designer changes, but this is one scenario I’ve run across that gives a reason to make custom changes via the LINQ-to-SQL designer.

No comments:

Can't RDP? How to enable / disable virtual machine firewall for Azure VM

Oh no!  I accidentally blocked the RDP port on an Azure virtual machine which resulted in not being able to log into the VM anymore.  I did ...