LINQ to SQL not recognizing new associations?

Question

I have two projects using legacy databases with no associations between the tables. In one, if I create associations in the DBML file, I can reference the associations in LINQ like this:

From c In context.Cities Where c.city_name = "Portland" _
Select c.State.state_name

(assuming I added the link from City.state_abbr to State.state_abbr in the DBML file.)

In a different project that uses a different database, adding the association manually doesn't seem to give me that functionality, and I'm forced to write the LINQ query like this:

From c In context.Cities Where c.city_name = "Portland" _
Join s In context.States On c.state_abbr = s.state_abbr _
Select s.state_name

Any idea what I could be missing in the second project?

Note: These are completely contrived examples - the real source tables are nothing like each other, and are very cryptic.

Answer 1

Check your Error List page. You might have something like the following in there:

DBML1062: The Type attribute '[ParentTable]' of the Association element 'ParentTable_ChildTable' of the Type element 'ChildTable' does not have a primary key. No code will be generated for the association.

In which case all you should need to do is make sure that both tables have a primary key set and re-save the dbml file. This will invoke the custom tool, which will in turn update the designer.cs file and create code for the association.

Answer 2

It looks like my problem was my tables didn't have primary keys in the second project. Like I stated, these are legacy tables, so I had to do the linking and primary key stuff in the Database Context instead of the database itself, and I just forgot to specify the primary keys the second time around. Frustrating when you don't spot it, but it makes sense now.