LINQ to Entities does not recognize ToArray

Question

I'm trying to write a query that will project to a DTO where two of the properties are int arrays. I'm getting an error because of the ToArray() call in the projection.

teams = context
  .Teams
  .Include("TeamDepartments")
  .Include("TeamEmployees")
  .Select(t => new TeamDto
      {
          sourceSystemId = t.TeamId,
          name = t.Name,
          manager = t.EmployeeIdTeamManager,
          teamLead = t.EmployeeIdTeamLead,
          employees = t.TeamEmployees.Select(te => te.EmployeeId).ToArray(),
          departments = t.TeamDepartments.Select(td => td.DepartmentId).ToArray()
       })
  .ToList();

For employees and departments, which are the two int[ ] properties, how can I get those values? For now, I'm just pulling back the list of teams and then looping over them to create the DTO.

I've seen other similar questions, but the solutions do not seem to be working for me. I suspect there's an additional step I need to take because I'm traversing a relationship.

Answer 1

What you need to do is separate this query into two different steps; the first will retrieve the correct results, and the second will project the data into your DTO. Like this:

teams = context
  .Teams
  .Include("TeamDepartments")
  .Include("TeamEmployees")
  .Select(t => new // notice this is an anonymous object
      {
          sourceSystemId = t.TeamId,
          name = t.Name,
          manager = t.EmployeeIdTeamManager,
          teamLead = t.EmployeeIdTeamLead,
          employees = t.TeamEmployees.Select(te => te.EmployeeId),
          departments = t.TeamDepartments.Select(td => td.DepartmentId)
       })
  .ToList() // first run the query on the server without the ToArray calls
  .Select(obj => new TeamDto
      {     // then project the in-memory results onto your DTO.
          sourceSystemId = obj.sourceSystemId,
          name = obj.name,
          manager = obj.manager,
          teamLead = obj.teamLead,
          employees = obj.employees.ToArray(),
          departments = obj.departments.ToArray()
      })
  .ToList();