How to write this linq query to avoid too many query?

Question

I have two table Company and Employee. And there relation is Company(one) - Employee(many).
And I want to concatenate all the Employees' name to a string and output. I know I can write such a query :

  String names = "";
    foreach(var emp in Company.Employee)
    {
         names += emp.name;
    }

But If I use this mean, I would load all the employee records into memory, and then make comparison, which is a waste of time and memory, and would low the performance. So in linq, is it possible to craft such a query that can return all concatenated names within a single SQL ?

Thanks in advance ! Any recommendations will be greatly appreciated !

Answer 1

var employeeNames = context
                       .Company
                       .Where(c => c.Id = 0xF00)
                       .SelectMany(c => c.Employee)
                       .Select(e => e.Name)
                       .ToArray();

var result = String.Join(" ", employeeNames);

You can vary the part of the query selecting the company a bit depending on the exact semantics and Entity Framework version. In .NET 4.0 the Entity Framework supports Single(). If you don't care about minor semantic differences you can use First() instead of SelectMany() prior to .NET 4.0.

Answer 2

This is a simplified variation of Daniel's but I think it should work (making assumptions about the schema)

var employeeNames = (from e in context.Employee
                       where e.CompanyId = 0xF00
                       select e.Name)
                       .ToArray(); 

var result = String.Join(" ", employeeNames);