Entity Framework LINQ projection to user type results in missing data

Question

I have many different relationships between Contractors and SafetyCouncils. They are joined by the bridge table ContractorsSafetyCouncils, which consists of ContractorId and SafetyCouncilId. These 2 columns form a composite key. This ratio is correctly displayed in EF4. The executing entity has the property:

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

And the Safety Council object has the property:

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

When accessing these properties through lazy loading from a single Contractor or SafetyCouncil object, they work exactly as expected. But when accessing this relationship in the request:

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
};

The problem occurs with:

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

SafetyCouncilIds 0 , 1 SafetyCouncilId, .

, MasterListItem, . ?

Update: My 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

SafetyCouncilIds . , POCO .

Answer 2

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();
    }

}

Answer 3

:

• , .
• Compare the two requested sql queries and find the differences.

Unfortunately, without access to your code or schema, I cannot provide a better answer.