I have 3 tables Subcontract, Company, and a link table CompanyToSubcontract. The link table contains the Subcontract_id and the Company_id. The foreign keys were set-up in SQL and when I drug them into my dbml the one-to-many relationship arrows showed up and everything looked fine. However, when coding, it's as if the relationship isn't there.
When I write a Select statement I have to use the join for it to work. When I refer to CompanyToSubcontract in code, I don't have the correct members available. I have CompanyToSubcontract.company_id and CompanyToSubcontract.subcontract_id, but not CompanyToSubcontract.company or CompanyToSubcontract.subcontract.
I have another table Group which has a one-to-many relationship with Subcontracts. I set up the foreign key the same way and that's working great. I can access Subcontract.group_id as well as Subcontract.group.
Everything seems to be set up properly for the link table, but I cannot get it to work. Is there some kind of trick for multiple foreign keys? What am I missing?
EDIT: My CompanyToSubcontract doesn't have a primary key. Could that be causing the problem? Trying it right now.