CODEWITHSUNDEEP
linqlinq-to-sqllinq-to-entitiesaggregate
MemoryLeak
MemoryLeak

Reputation: 7318

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

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 !

Upvotes: 2

Views: 174

Answers (2)

James Curran
James Curran

Reputation: 103505

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

Upvotes: 0

Daniel Brückner
Daniel Brückner

Reputation: 59645

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.

Upvotes: 2

Related Questions