Databases
StackOverflow2013
Postsdbo
POLinq guru - filtering related entities
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLinq guru - filtering related entities
    text
    copied!<p>My table structure is as follows:</p> <pre><code>Person 1-M PesonAddress Person 1-M PesonPhone Person 1-M PesonEmail Person 1-M Contract Contract M-M Program Contract M-1 Organization </code></pre> <p>At the end of this query I need a populated object graph where each person has their: </p> <ul> <li>PesonAddress's</li> <li>PesonPhone's</li> <li>PesonEmail's</li> <li>PesonPhone's</li> <li>Contract's - and this has its respective <ul> <li>Program's</li> </ul></li> </ul> <p>Now I had the following query and I thought that it was working great, but it has a couple of problems:</p> <pre><code>from people in ctx.People.Include("PersonAddress") .Include("PersonLandline") .Include("PersonMobile") .Include("PersonEmail") .Include("Contract") .Include("Contract.Program") where people.Contract.Any( contract =&gt; (param.OrganizationId == contract.OrganizationId) &amp;&amp; contract.Program.Any( contractProgram =&gt; (param.ProgramId == contractProgram.ProgramId))) select people; </code></pre> <p>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.</p> <p>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.</p> <p>I kinda understand why its not working, but I don't know how to change it so it is working...</p> <p>This is my attempt thus far:</p> <pre><code>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() &amp;&amp; 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 </code></pre> <p>But this has several problems (where the Person type is an EF object):</p> <ul> <li>I am left to do the mapping by myself, which in this case there is quite a lot to map </li> <li>When ever I try to map a list to a property (i.e. Scholarship = currentScholarships) it says I can't because <code>IEnumerable</code> is trying to be cast to <code>EntityCollection</code></li> <li>Include doesn't work </li> </ul> <p>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.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload