Linq guru - filtering related entities...

Question

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.

Answer 1

Include in the Entity Framework will always bring back everything in the relationship, there is no way to do a partial include or an equivalent of AssociateWith that Linq to SQL has.

Instead if you only want to bring back some of the Contracts you need to split it into two queries and take advantage of the auto hookup performed by the entity framework.

Once both queries have been executed your Person objects will contain only the Contracts brought back by the Contracts query in their Contracts collection.

Answer 2

Like Mant101 says, you cannot filter the .Include part in Linq to entities. Look at a Person object as a representation of all information stored in the database about this person, including all contracts. All fitering has do be done seperately.

These questions seem to pop up regulary here. At least I think I've seen some, but cannot find many. Here's another question dealing with this topic: http://stackoverflow.com/questions/1085462.

There is also a workable anser there: Just return your (whole) person object, and any additional (filtered) information on it in a new anonymous type.

Answer 3

Just don't use Include, filter manually. You can first filter Contracts that are associated with required ProgramId and OrganizationId. After that you can select persons associated with selected contracts. A've attached a sample code. You'll need to modify it to utilize M-M relationship correctly. But anyway logic should be correct.

public class PersonDetails
{
    public Person person;
    public List<Contract> contracts;
}

var selected_program = (from pr in ctx.Programs where pr.Id == param.ProgramId select pr).Single();

//select contracts by OrganizationId and ProgramId
var selected_contracts = from c in ctx.Contracts
                where c.OrganizationId == param.OrganizationId
                from p in ctx.Programs
                where p.Id == param.ProgramId
                where p.ContractId == c.Id
                select c;

//select persons and contracts
var people =
    from p in ctx.People
    select new PersonDetails()
    {
        person = p,
        contracts = (from c in selected_contracts
                     where c.PersonId == p.Id
                     select c).ToList()
    };

//select people associated with selected contracts
var selected_people = from p in people where p.contracts.Count > 0 select p;