Linq include children from collection

Question

Using the include method does not include the children of a collection.

var abuseCaseQuery =
  from @event in 
    context.AbuseEvents.Include("AbuseCase.AbuseCaseStatus.Status")
  select new
  {
    @event.SecurityGroupId,
    @event.AbuseCaseId,
    @event.AbuseCase.AbuseCaseStatus
  }
;

var abuseCases = abuseCaseQuery.ToList();

The abuseCases List contains all the AbuseCasesStatus and the StatusId but the Status object is null.

edmx:

How can I populate the Status navigation property?

Answer 1

In EF, Includes are ignored when you add a projection with Select.

So, you have to explicitly select all the data you want:

select new
{
  @event.SecurityGroupId,
  @event.AbuseCaseId,
  @event.AbuseCase.AbuseCaseStatuses
    .Select( acs => new { AbuseCaseStatus = acs, Status = acs.Status } )
}

---

This does result in a "flat" object, so you might like to fix up the object graph afterwards:

foreach( var abuseCase in abuseCases )
{
  foreach( var acs in abuseCase.AbuseCaseStatuses )
  {
    acs.AbuseCaseStatus.Status = acs.Status;
  }
}