.net - Linq 大师 - 过滤相关实体

Question

我的表结构如下:

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

在此查询结束时，我需要一个填充的对象图，其中每个人都有:

PesonAddress的

PesonPhone的

PesonEmail的

PesonPhone的

契约(Contract)的 - 这有其各自的

程序的

现在我有以下查询，我认为它工作得很好，但它有几个问题:

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;

问题在于它根据标准过滤人员，而不是契约(Contract)或契约(Contract)的程序。它带回了每个人拥有的所有契约(Contract)，而不仅仅是那些组织 ID 为 x 的契约(Contract)，并且分别适用于每个契约(Contract)的程序。

我想要的只是那些至少有一个 OrgId 为 x 的契约(Contract)的人，并且该契约(Contract)有一个 ID 为 y 的程序......并且返回的对象图只有匹配和该契约(Contract)中匹配的程序。

我有点明白为什么它不起作用，但我不知道如何改变它所以它起作用了......

这是我迄今为止的尝试:

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

但这有几个问题(其中 Person 类型是一个 EF 对象):

剩下我自己做映射，在这种情况下，有很多映射

当我尝试将列表映射到属性时(即奖学金 = currentScholarships)，它说我不能，因为 IEnumerable正试图被转换到 EntityCollection

包含不起作用

因此，我如何让它发挥作用。请记住，我正在尝试将其作为编译查询来执行，因此我认为这意味着匿名类型已被淘汰。

Answer 1

只是不要使用包含，手动过滤。您可以先过滤与所需的 ProgramId 和 OrganizationId 关联的契约(Contract)。之后，您可以选择与选定契约(Contract)关联的人员。附上示例代码。您需要修改它以正确利用 M-M 关系。但无论如何逻辑应该是正确的。

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;