SQL to LINQ query, return objects AND count nicely bundled together..

Question

• An employee can have multiple vehicles..
• And a company can have multiple employees..

My mission is to get the Companies that therefore have the most vehicles..

I have the LINQ query working perfectly (phew)! It returns (via select new {})..

• CompanyID
• EmployeeVehicleCount

Fantastic! BUT.. I want to be able to pull out "Company" objects rather than just the int CompanyID, so that I could essentially access them like this while iterating through the results:

ResultRow.Company.CompanyName

And

ResultRow.EmployeeVehicleCount

Otherwise, I am going to have to first set up a loop and then instantiate each and every Company as I go through the loop which doesn't seem very efficient.

What's the cleanest way to achieve what I'm after? (Thx in advance).

Answer 1

if you have the companyid and vehiclecount, you can transform that via a new Select ala

var list = <your existing linq query>.Select(o => new { Company = <SomeDataMethod>(o.CompanyID), EmployeeVehicleCount = o.EmployeeVehicleCount });

now you can say

foreach (var result in list) 
{
   var s = result.Company.CompanyName +" has "+ result.EmployeeVehicleCount +" cars";
}