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.
