My table structure is as follows:

Person 1-M PesonAddress
Person 1-M PesonPhone
Person 1-M PesonEmail
Person 1-M Contract 
Contract M-M Program
Contract M-1 Organization

At the end of this query I need a populated object graph where each person has their:

• PesonAddress's
• PesonPhone's
• PesonEmail's
• PesonPhone's
• Contract's - and this has its respective
  • Program's

Now I had the following query and I thought that it was working great, but it has a couple of problems:

from people in ctx.People.Include("PersonAddress")
                        .Include("PersonLandline")
                        .Include("PersonMobile")
                        .Include("PersonEmail")
                        .Include("Contract")
                        .Include("Contract.Program")
where people.Contract.Any(
    contract => (param.OrganizationId == contract.OrganizationId)
        && contract.Program.Any(
            contractProgram => (param.ProgramId == contractProgram.ProgramId)))
select people;

The problem is that it filters the person to the criteria but not the Contracts or the Contract's Programs. It brings back all Contracts that each person has not just the ones that have an OrganizationId of x and the same goes for each of those Contract's Programs respectively.

What I want is only the people that have at least one contract with an OrgId of x with and where that contract has a Program with the Id of y... and for the object graph that is returned to have only the contracts that match and programs within that contract that match.

I kinda understand why its not working, but I don't know how to change it so it is working...

This is my attempt thus far:

from people in ctx.People.Include("PersonAddress")
                        .Include("PersonLandline")
                        .Include("PersonMobile")
                        .Include("PersonEmail")
                        .Include("Contract")
                        .Include("Contract.Program")
let currentContracts = from contract in people.Contract
                where (param.OrganizationId == contract.OrganizationId)
                select contract 
let currentContractPrograms = from contractProgram in currentContracts 
                    let temp = from x in contractProgram.Program
                        where (param.ProgramId == contractProgram.ProgramId)
                        select x
                    where temp.Any()
                    select temp
where currentContracts.Any() && currentContractPrograms.Any()
select new Person { PersonId = people.PersonId, FirstName = people.FirstName, ..., ...., 
                    MiddleName = people.MiddleName, Surname = people.Surname, ..., ...., 
                    Gender = people.Gender, DateOfBirth = people.DateOfBirth, ..., ...., 
                    Contract = currentContracts, ... };  //This doesn't work

But this has several problems (where the Person type is an EF object):

• I am left to do the mapping by myself, which in this case there is quite a lot to map
• When ever I try to map a list to a property (i.e. Scholarship = currentScholarships) it says I can't because IEnumerable is trying to be cast to EntityCollection
• Include doesn't work

Hence how do I get this to work. Keeping in mind that I am trying to do this as a compiled query so I think that means anonymous types are out.