Linq Gurus - Filtration Related Objects

Question

My table structure is as follows:

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

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

• Pesonaddress in
• PesonPhone's
• PesonEmail's
• PesonPhone's
• Contract - and it has
  • programs

Now I had the following query, and I thought it worked fine, but it has a few 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 a person according to criteria, and not according to contracts or contract programs. He returns all Contracts that each person has not only those that have Organization x, and the same applies to each of these Contract Programs, respectively.

What I want is only people who have at least one contract with OrgId from x and where this contract has a program with identifier y ... and for an object graph that returns to have only contracts this is a correspondence and programs under this contract that are consistent.

I understand why it does not work, but I do not know how to change it, so it works ...

This is my attempt:

 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):

• It remains for me to do the mapping myself, which in this case is quite a lot to display
• When ever I try to map a list to a property (i.e. scholarship = currentScholarships), it says that I cannot, because IEnumerable trying to be different from EntityCollection
• Enable not working

Therefore, how do I get this to work. Remembering that I am trying to do this as a compiled request, so I think this means that anonymous types are not available.

Answer 1

Just do not use Include, filter manually. You can first filter the Contracts associated with the required ProgramId and OrganizationId. After that, you can select the persons associated with the selected contracts. Attached example code. You will need to modify it in order to use MM relationships correctly. But in any case, the logic must 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; 

Answer 2

Include in the Entity Framework will always return everything in a relationship; there is no way to do the partial inclusion or the AssociateWith equivalent that Linq to SQL has.

Instead, if you want to return some of the Contracts, you need to split it into two requests and use the automatic connection function performed by the entity infrastructure.

Once both requests are completed, your Person objects will only contain the Contracts returned at the request of the Contracts in their Contracts.

Answer 3

Like Mant101, you cannot filter the .Include part in Linq for entities. Look at the Person object as a representation of all the information stored in the database about this person, including all contracts . All fixtures are performed separately.

These questions seem to appear here regularly. At least I think I saw some, but I can not find much. Here's another question related to this topic: conditional include in linq for entities? .

There is also a workable anser: just return the person’s (holistic) object and any additional (filtered) information about it in a new anonymous type.