To add to my previous question:
I manage to get the 1:n relationships going, it is just the m:n one that would not allow me to find columns joined to the main table by means of a Linq query.
Let me explain what I am trying to do, maybe you can suggest a more elegant solution.
I am working on an Contract Management system. I have Users and Contracts and then also Groups. Users can belong to a Group(s) and Contracts can belong to the same Group(s) as well. When a User logs in, he or she should only be able to view only Contracts that belong to the same Group or Groups the logged in User belongs to.
My tables are:
Users (user table)
GroupUsers (join table)
Groups (groups table)
GroupContracts (join table)
Contracts (contracts table)
I want to accomplish the following:
A logged in User should be able to list only Contracts belonging to the same Groups as the User.
In SQL I would have written a query like (if the user's ID is e.g. 39):
Select Contract.ContractName FROM Contracts, Groups
WHERE Contracts.ContractID = GroupContracts.ContractID
AND GroupContract.GroupID IN
(Select GroupID FROM GroupUsers WHERE GroupUsers.UserID = 39 )
I am using OpenAccess (reverse mapping), how would I accomplish the same using Linq?