Select items with the same foreign key

I have a question regarding Linq in C#.

Let’s say that I have 3 tables “Company”, “Employee” and “Job”.

Company
--------
Id - int (PK)
Name - string

Employee
--------
Id - int (PK)
CompanyId - int (FK on Company.Id)
Name - string

Job
--------
Id - int (PK)
CompanyId - int (FK on Company.Id)
EmployeeId - int (FK on Employee.Id)
Name - string

Something like that: enter image description here

The important thing is that every work must be connected to a company but not necessarily to an employee. However, each employee must be connected to a company. For example we can have racord like that:

Company
--------
Id     Name
1      'A'
2      'B'
3      'C'

Employee
--------
Id     CompanyId     Name
1          1         'A'
2          1         'B'
3          2         'C'

Job
--------
Id     CompanyId     EmployeeId     Name
1          1             1         'clean'
2          1             2         'wash'
3          2             2         'buy'
4          3            NULL       'sell'

And now with linq I would like to get all jobs that are assigned to this employee and other employees from the same company. So in this case it will should are jobs with id 1 and 2 becouse employee 1 is assigned to company 1 and job with id 2 also has assigned to this company. How could I achieve this using linq but something like this: _context.Job.Where (x => x)

It is important to make only one query to the database.

Tkanks.

Answer

You can simplify the problem by thinking of it in two steps: find the company for a given employee, find all the jobs for that company:

var companyToFind = from e in Employee
                     where e.Id == empToFind
                     select e.CompanyId;

var jobsToFind = from j in Job
                 where companyToFind.Any(cid => cid == j.CompanyId)
                 select j;

In LINQ to SQL, EF Core 2.2 and EF Core 3.x the Any is translated to an EXISTS query in SQL.

Note since you know there should be only one answer to companyToFind, sending two queries to the database may be more efficient.

For LINQ to SQL or EF Core 3.x, you could also nest the first query and reduce to a single result:

var jobsToFind = from j in Job
                 where (from e in Employee
                     where e.Id == empToFind
                     select e.CompanyId).First() == j.CompanyId
                 select j;