LINQ to Entities Does Not Recognize ToArray

Question

I am trying to write a query that will be designed in DTO, where two of the properties are int arrays. I am getting an error due to a call to Toray () 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 two int [] properties, how can I get these values? For now, I just undo the list of commands and then iterate over them to create a DTO.

I saw other similar questions, but the solutions do not seem to work for me. I suspect that I need an extra step because I get around the relationship.

Answer 1

What you need to do is split this request into two different steps; the first will get 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(); 

Answer 2

I believe the problem is that you are trying to call ToArray in a block that the SQL provider will convert to an SQL query. Of course, SQL Server does not know what an array is for this method to not work. What happens if you delete the ToArray call? I believe that these results will return as IEnumberables outside this block, you can convert them as needed. I'm not sure if this will work, but if you change the definition of TeamDto as follows, this may solve the problem.

  // you have now something like string[] employees; // instead do IEnumberable<string> employees; // If you want an array add string[] _employees; 

Then, outside the request for _employees = employees.ToArray();