05.17
Like the previous, this is with 3.5SP1. Also, I “get” that this is probably By Design as lacking the PK you’re unable to identify specific rows in the mapping table for later update, but for the purpose of this blog post I’ll consider it a bug anyway :)
This one didn’t take as long to figure out since I luckily had working and broken test cases already in front of me to compare.
One mapping table (basically just 2 columns holding the PK’s of 2 “real” tables to create a n:m mapping) didn’t have a PK set, and even with all 3 of them in the dbml, the FK associations (the FK’s were set fine) weren’t working – it would show the PK columns in the table’s class fine, but not the properties (of the type of the parent tables) that should be there “following” the FK links.
Another mapping table, very similar, though, had a PK set (encompassing the 2 FK columns instead of a pure-identity 3rd column) and its associations Just Worked.
Sure enough, I took the 2 FK columns, made those into a PK, then removed/readded that table in the dbml, and now the FK associations work fine!
Here’s an example repro: 2 real tables with simple PK int’s and a mapping table that just FK’s to each of them.
Notice here at the start that there’s no PK set for the mapping table, just the FK’s to the 2 real tables.
The FK relationships work fine as-is, at least in terms of SQL Server and even the dbml diagram showing them. Notice that both real tables have PK’s set (the key-looking icons to the left of the id columns), but there’s no PK set for the mapping table.
However, when we actually try to use the mapping table, you’ll notice we only get the FK int’s and not the actual “real” table classes that we’d expect from LINQ-to-SQL:
Now, to confirm that adding the PK actually fixes it. So, we pick the 2 columns and make that a PK in the mapping table.
Now, this is one of the painful things about LINQ-to-SQL as opposed to LINQ-to-Entities – no “refresh from schema” option. It’s not likely to show up, since the ADO.NET team’s made it clear that LINQ-to-Entities are the future.
Ok, back to manually refreshing (remove then add) the table in the dbml.
This is another behavior that appears to be a bug in VS – after setting the PK in SSMS fine, it doesn’t matter what you refresh in the connection, dragging over the mapping table won’t show the PK change. While I could probably delete/re-add the data connection in server explorer, I just restart VS to do the trick. Afterwards, adding the table shows the PK fine:
Save that dbml change, and now we can “see” the associated tables, not just the FK’s:
![[DIR]](http://etrustdownloads.ca.com/icons/dir.gif){kind=icon}
![[FILE]](http://etrustdownloads.ca.com/icons/generic.gif){kind=icon}
