Linq Navigation Properties complex where ID in (select id from...)

Question

I have two entities Candidate and CandidateLocation where a Candidate can have multiple CandidateLocation entries.

A CandidateLocation contains the CandidateId, an ISO Country Code (e.g. US, GB) and a type column (1 = Permitted, 2 = Restricted).

Rules dictate that if the Candidate does not have any 'Permitted' entries in the CandidateLocation table they can work anywhere. If they have an explicit 'Permitted' location they can only work in the explicitly permitted locations. They can not work in explicilty restricted locations.

To try an demonstrate this please see the image below (Candidates can have multiple locations I have kept it to one to simplify the illustration)

In SQL one way of achieving this would be the following query

SELECT  *
FROM    Candidate
WHERE   Candidate.IsArchived = 0
    AND
        -- Do not inlude restricted locations (RestrictionStatus = 2)
        Candidate.CandidateId NOT IN (SELECT CandidateId FROM CandidateLocation WHERE IsArchived = 0 AND CountryISOCode = @Location AND RestrictionStatus = 2)
    AND
        (
        -- Include Explicit Permitted Locations
        Candidate.CandidateId IN (SELECT CandidateId FROM CandidateLocation WHERE IsArchived = 0 AND CountryISOCode = @Location AND RestrictionStatus = 1)
        OR
        -- Include Candidates with no Explicit Permitted Locations
        Candidate.CandidateId NOT IN (SELECT CandidateId FROM CandidateLocation WHERE IsArchived = 0 AND RestrictionStatus = 1)
        )

If anyone knows how to achieve this using linq & Navigation Properties I would greatly appreciate the help.

Many thanks

Answer 1

Assuming you've got one-to-many association between Candidates and CandidateLocations

Context.Candidates.Where(c => c.IsArchived == 0 &&
!c.CandidateLocations.Any(
    l => l.CountryISOCode == location && l.RestrictionStatus == 2) &&
(c.CandidateLocations.Any(
    l => l.CountryISOCode == location && l.RestrictionStatus == 1) ||
!c.CandidateLocations.Any(
    l => l.RestrictionStatus == 1))
);