MSLinqToSQLGenerator generates different output than SQLMetal

Question

I have a rather large DBML file and recently discovered that Visual Studio's MSLinqToSQLGenerator is generating different output than:

SqlMetal.exe All.dbml /code:All.designer.vb /namespace:LINQ2FSE /pluralize /provider:SQL2005

It seems to have dropped an arbitrary (and I think relatively small) set of associations from the generated VB code. But SQLMetal works fine. Shouldn't the output be the same?

After further research, I find that the difference seems to be associations on entities that involve properties that are also used on other associations on the same entity with a different number of columns. For example: Entity A has columns id and name Entity B has columns id, name and fkA (foreign key to A) Entity C has columns id, name, fkA and fkB (nullable fkB)

Entity C has association C_A, which links fkA to A.id it also has association C_B, which links fkA and fkB to B.fkA and B.id

The code for properties supporting C_B will not be generated by Visual Studio, but will be generated by SqlMetal.exe.

Is this kind of association allowed? Is there a reason the code is being generated differently?

Answer 1

It turns out (with the help of Microsoft) that SQLMetal generates different output than the IDE's MSLinqToSQLGenerator because the DBML file (generated by a tool I created) had some relationships defined where the parent could access the children, but the children did not define the parent association. Apparently you are required to define the association from the child to the parent (the foreign key relationship). If you only have an association from the parent to the children defined, and don't have the reverse association defined (or the reverse association has a different name) then the .NET source code for that association will not be generated in either direction. This is handled correctly for MSLinqToSQLGenerator, but SQLMetal does not perform as much validation, apparently, and will generate the association code anyway. Microsoft has reported this issue to development.