Entity Framework LINQ projection into custom type results in missing data

Question

I have a many to many relationship between Contractors and SafetyCouncils. They are joined by a bridge table ContractorsSafetyCouncils which consists of ContractorId and SafetyCouncilId. These 2 columns form a composite key. This relationship is mapped correctly in EF4. The Contractor entity has the property:

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

And the SafetyCouncil entity has the property:

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

When accessing these properties via lazy loading from a single Contractor or SafetyCouncil entity, they work exactly as expected. But when accessing this relationship in a query:

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

For every record the SafetyCouncilIds collection has 0 members, when based on the data in the database every record should have at least 1 SafetyCouncilId associated with it.

If I run the same query, but project into an anonymous type instead of the MasterListItem type, it works correctly. Why can't I project this query into my custom type?

Update: My MasterListItem POCO contained the following properties:

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

The SafetyCouncilIds property was the cause of the problem. I changed this to an automatic property and built the string elsewhere and projecting onto the POCO worked like a charm.

Answer 1

I have two suggestions:

1. Try to isolate the the problem by removing any extra parts of the query.
2. Compare the two sql queries generated and find the differences.

Unfortunately, without access to your code or schema, I can't provide a better answer.

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

}