c# - Entity Framework LINQ 投影到自定义类型导致数据丢失

Question

我在承包商和安全委员会之间建立了多对多关系。它们由由 ContractorId 和 SafetyCouncilId 组成的桥接表 ContractorsSafetyCouncils 连接。这两列构成一个组合键。此关系在 EF4 中正确映射。 Contractor 实体具有以下属性:

public virtual ICollection<SafetyCouncil> SafetyCouncils
{
    get;
    set;
}

并且 SafetyCouncil 实体具有以下属性:

public virtual ICollection<Contractor> Contractors
{
    get;
    set;
}

当从单个承包商或 SafetyCouncil 实体通过延迟加载访问这些属性时，它们完全按预期工作。但是在查询中访问此关系时:

from c in ContractorRepository.All()
where c.PQFs.Count() > 0
let psmAudits = c.PQFs.SelectMany(pqf => pqf.Audits)
let psmAudit = psmAudits.FirstOrDefault(audit => audit.CompletedDate == psmAudits.Max(a => a.CompletedDate))
let scsAudits = c.PQFs.SelectMany(pqf => pqf.SCSAudits)
let scsAudit = scsAudits.FirstOrDefault(audit => audit.CompletedDate == scsAudits.Max(a => a.CompletedDate))
select new MasterListItem()
{
    AdministratorNotes = c.AdminFlags.Where(f => f.IsActive && f.ForPQF).Select(f => f.Text),
    CanViewInfo = false,
    ContractorName = c.ContractorName,
    ContractorId = c.Id,
    ContractorTaxId = c.TaxId,
    SafetyCouncilIds = c.SafetyCouncils.Select(sc => sc.Id),
    PQFSubmitted = c.PQFs.Max(p => p.PQFInfo.SubmittedDate.Value),
    PSMAuditId = psmAudit.Id,
    PSMAuditComplete = psmAudit.CompletedDate,
    PSMAuditStatus = psmAudit.Status.Description,
    SCSAuditId = scsAudit.Id,
    SCSAuditComplete = scsAudit.CompletedDate
};

问题出现在:

SafetyCouncilIds = c.SafetyCouncils.Select(sc => sc.Id),

对于 SafetyCouncilIds 集合的每条记录都有 0 个成员，当基于数据库中的数据时，每条记录应该至少有 1 个 SafetyCouncilId 与之关联。

如果我运行相同的查询，但投影到匿名类型而不是 MasterListItem 类型，它会正常工作。为什么我不能将此查询投影到我的自定义类型中？

更新:我的 MasterListItem POCO 包含以下属性:

public string SafetyCouncilIdsString
{
    get;
    set;
}

public IEnumerable<int> SafetyCouncilIds
{
    set
    {
        StringBuilder sb = new StringBuilder(",");

        foreach (var id in value)
        {
            sb.Append(id);
            sb.Append(",");
        }

        this.SafetyCouncilIdsString = sb.ToString();
    }
}

SafetyCouncilIds 属性是问题的原因。我将其更改为一个自动属性，并在别处构建字符串并投影到 POCO 上，效果非常好。

Answer 1

public IEnumerable<int> SafetyCouncilIds
{
    set
    {
        StringBuilder sb = new StringBuilder(",");

        foreach (var id in value)
        {
            sb = sb.Append(id).Append(","); // <-- try this
            // *or sb = sb.AppendFormat("{0},", id);*
        }

        this.SafetyCouncilIdsString = sb.ToString();
    }