How to write this Link2Sql select query
- by RememberME
I have 3 tables: subcontracts, companies and contacts. Each table has a active_status flags which shows that the item is still active (hasn't been deleted).
Each contact has a company_id field which stores the id of the contact's company.
Each subcontract has a company_id field which stores the subcontract's company.
Each company has a company_id field which holds its guid and a primary_company field, b/c the company could be a subsidiary. If it's a subsidiary, the primary_company field holds the id of the primary company.
I have a subcontract form. On the form, I have a drop-down of contacts which are stored in a contact table. Currently the drop-down lists all contacts. I would like to have the drop-down only list contacts which belong to the subcontract's company or any of that company's subsidiaries.
I have the following query which I use elsewhere in the program to pass a company_id and get its subsidiaries.
public IQueryable<company> GetSubsidiaryCompanies(Guid id)
{
return
from c in db.companies
where c.primary_company == id && c.active_status == true
select c;
}
This is my current contact selection
public IQueryable<contact> GetContacts()
{
return
from c in db.contacts
where c.active_status == true
orderby c.contact_name
select c;
}
What I need to do is pass it the subcontract.company_id and then only return the contacts where contact.company_id == subcontract.company_id or contact.company_id == one of the subsidiary ids.
